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@amsoftwaredesign.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.
>
