From: | Clark Allan <clarka(at)gmail(dot)com> |
---|---|
To: | Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org, pgsql-docs(at)postgresql(dot)org |
Subject: | Re: PostgreSQL sequence within function |
Date: | 2005-07-05 20:01:14 |
Message-ID: | 4a7a7321050705130172b00f03@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I figured it out... the problem was calling nextval("seq") with double
quotes.
Normally, you would do "select nextval('seq')". From within a function,
calling nextval with single quotes around the argument, causes a syntax
error.
SOLUTION:
you need to use "backslash escape" sequences around the sequence argument...
example below....
-----------------------------------
CREATE FUNCTION sp_slide_create(int4) RETURNS int4 AS'
DECLARE
aScriptID ALIAS FOR $1;
seqID int4 := nextval(\'genseq\'); -- the magic is here
BEGIN
INSERT INTO tblslides (slideid) VALUES (seqID);
RETURN seqID;
END;'
LANGUAGE 'plpgsql' VOLATILE;
-----------------------------------
Maybe this is an obvious solution, but i really think there should be
something in the documentation about this (...pgsql-docs CC'ed)
Thanks
Clark Allan
On 7/5/05, Tony Caduto <tony_caduto(at)amsoftwaredesign(dot)com> wrote:
>
> Try this version of your function.
> I don't think you can assign a value to a variable in the declaration
> section with the return value of a function.
>
> CREATE OR REPLACE FUNCTION sp_slide_create(int4, bool, bool, bool,
> varchar, text, varchar, varchar, int4)
> RETURNS int4 AS'
> DECLARE
> aScriptID ALIAS FOR $1;
> aAllowDGP ALIAS FOR $2;
> aAllowDGO ALIAS FOR $3;
> aWaitForSlideFinish ALIAS FOR $4;
> aTitle ALIAS FOR $5;
> aText ALIAS FOR $6;
> aFlashFileDGP ALIAS FOR $7;
> aFlashFileDGO ALIAS FOR $8;
> aSlideType ALIAS FOR $9;
> seqID int4;
> BEGIN
> seqID = nextval("seqslideid");
> INSERT INTO tblslides
> (slideid, scriptID, allowdgp, allowdgo, waitforslidefinish, title,
> text, flashfiledgp, flashfiledgo, slidetype)
> VALUES
> (seqID, aScriptID, aAllowDGP, aAllowDGO, aWaitForSlideFinish, aTitle,
> aText, aFlashFileDGP, aFlashFileDGO, aSlideType);
>
> RETURN seqID;
> END;'
>
> LANGUAGE 'plpgsql' VOLATILE;
>
>
> Clark Allan wrote:
>
> > Thanks for the help Tony,
> > But im still having some trouble.
> >
>
>
>
From | Date | Subject | |
---|---|---|---|
Next Message | Frank Finner | 2005-07-05 20:11:10 | Re: PITR problems |
Previous Message | Richard Hayward | 2005-07-05 19:15:01 | current_user inside SECURITY DEFINER function? |