Thread: PLpgSQL
3 questions: 1. Can I use CREATE SEQUENCE inside a function? 2. I can create this function but I can't get it to run: CREATE FUNCTION new_proj_pts_seq(int4) RETURNS text AS 'DECLARE proj_ID alias for $1; seq_name TEXT; BEGIN seq_name := ''proj_pts_'' || proj_ID; createsequence seq_name; END; RETURNS seq_name;' LANGUAGE 'plpgsql'; When I do SELECT new_proj_pts_seq(9000); I get: ERROR: parser: parse error at or near "$1" The same happens if I assign $1 to proj_ID (instead of aliasing), or just use $1 in the string concatenation. I always get the same message. Considering that all the above is possible/fixable... 3. On the statement 'create sequence seq_name;', will 'seq_name' be evaluated properly? Thanks. -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 553-8000 x??? dado@wildbrain.com San Francisco, CA.
You will need to use "EXECUTE" to create the sequence. The docs on pl/pgsql cover it: basically, "EXECUTE string" will cause that string to be executed as a SQL statement. > -----Original Message----- > From: Dado Feigenblatt [SMTP:dado@wildbrain.com] > Sent: Friday, July 20, 2001 2:26 PM > To: Pgsql-Sql > Subject: PLpgSQL > > 3 questions: > > 1. Can I use CREATE SEQUENCE inside a function? > 2. I can create this function but I can't get it to run: > > CREATE FUNCTION new_proj_pts_seq(int4) > RETURNS text > AS 'DECLARE > proj_ID alias for $1; > seq_name TEXT; > BEGIN > seq_name := ''proj_pts_'' || proj_ID; > create sequence seq_name; > END; > RETURNS seq_name;' > LANGUAGE 'plpgsql'; > > When I do > SELECT new_proj_pts_seq(9000); > I get: > ERROR: parser: parse error at or near "$1" > > The same happens if I assign $1 to proj_ID (instead of aliasing), or > just use $1 in the string concatenation. > I always get the same message. > > Considering that all the above is possible/fixable... > > 3. On the statement 'create sequence seq_name;', will 'seq_name' be > evaluated properly? > > Thanks. > > > -- > Dado Feigenblatt Wild Brain, Inc. > Technical Director (415) 553-8000 x??? > dado@wildbrain.com San Francisco, CA. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster
Jeff Eckermann wrote: >You will need to use "EXECUTE" to create the sequence. The docs on pl/pgsql >cover it: basically, "EXECUTE string" will cause that string to be executed >as a SQL statement. > Now that I found the documentation, I started to suspect that. Although nowhere it is mentioned that you can only use INSERT, SELECT, UPDATE, DELETE. But still, the $1 is still plaguing me. Thanks. >>-----Original Message----- >>From: Dado Feigenblatt [SMTP:dado@wildbrain.com] >>Sent: Friday, July 20, 2001 2:26 PM >>To: Pgsql-Sql >>Subject: PLpgSQL >> >>3 questions: >> >>1. Can I use CREATE SEQUENCE inside a function? >>2. I can create this function but I can't get it to run: >> >>CREATE FUNCTION new_proj_pts_seq(int4) >>RETURNS text >>AS 'DECLARE >> proj_ID alias for $1; >> seq_name TEXT; >> BEGIN >> seq_name := ''proj_pts_'' || proj_ID; >> create sequence seq_name; >> END; >> RETURNS seq_name;' >>LANGUAGE 'plpgsql'; >> >>When I do >> SELECT new_proj_pts_seq(9000); >>I get: >> ERROR: parser: parse error at or near "$1" >> >>The same happens if I assign $1 to proj_ID (instead of aliasing), or >>just use $1 in the string concatenation. >>I always get the same message. >> >>Considering that all the above is possible/fixable... >> >>3. On the statement 'create sequence seq_name;', will 'seq_name' be >>evaluated properly? >> >>Thanks. >> >> >>-- >>Dado Feigenblatt Wild Brain, Inc. >>Technical Director (415) 553-8000 x??? >>dado@wildbrain.com San Francisco, CA. >> >> >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 4: Don't 'kill -9' the postmaster >> > > -- Dado Feigenblatt Wild Brain, Inc. Technical Director (415) 553-8000 x??? dado@wildbrain.com San Francisco, CA.
Dado,
> But still, the $1 is still plaguing me.
>
Here's your problem:
> >>CREATE FUNCTION new_proj_pts_seq(int4)
> >>RETURNS text
> >>AS 'DECLARE
> >> proj_ID alias for $1;
> >> seq_name TEXT;
> >> BEGIN
> >> seq_name := ''proj_pts_'' || proj_ID;
> >> create sequence seq_name;
> >> END;
> >> RETURNS seq_name;'
This should read: RETURN seq_name
No "S".
> >>LANGUAGE 'plpgsql';
-Josh
______AGLIO DATABASE SOLUTIONS___________________________
Josh Berkus
Complete information technology josh@agliodbs.com
and data management solutions (415) 565-7293
for law firms, small businesses fax 621-2533
and non-profit organizations. San Francisco
Attachment
Josh Berkus wrote: > Dado, > > > But still, the $1 is still plaguing me. > > > > Here's your problem: > > > >>CREATE FUNCTION new_proj_pts_seq(int4) > > >>RETURNS text > > >>AS 'DECLARE > > >> proj_ID alias for $1; > > >> seq_name TEXT; > > >> BEGIN > > >> seq_name := ''proj_pts_'' || proj_ID; > > >> create sequence seq_name; > > >> END; > > >> RETURNS seq_name;' > > This should read: RETURN seq_name > > No "S". And should be placed before the END; > > > >>LANGUAGE 'plpgsql'; > > -Josh Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com