PostgreSQL sequence within function

All you where really mising was a semi colon afer nextval('myseq') and
the begin end.

CREATE or REPLACE FUNCTION getSeq()
RETURNS int AS
$$
begin
RETURN nextval('myseq');
end;
$$
LANGUAGE 'plpgsql';

Clark Allan wrote:

Tony Caduto wrote:

All you where really mising was a semi colon afer nextval('myseq') and
the begin end.

CREATE or REPLACE FUNCTION getSeq()
RETURNS int AS
$$
begin
RETURN nextval('myseq');
end;
$$
LANGUAGE 'plpgsql';

Clark Allan wrote:

This just made me think. If I was writing this function, I would have
written it as an SQL function like this:

CREATE or REPLACE FUNCTION getSeq() RETURNS int AS $$
SELECT nextval('myseq');
$$ LANGUAGE SQL;

Does anybody know which version is actually better/faster/more optimal?
I tend to always write functions as SQL where it's possible, as I
imagine that an SQL database engine will be better at running an SQL
functionion than an interpreted procedural function. Am I right to think
that?

--

Russ.

Russ Brown <pickscrape@gmail.com> writes:

This just made me think. If I was writing this function, I would have
written it as an SQL function like this:

CREATE or REPLACE FUNCTION getSeq() RETURNS int AS $$
SELECT nextval('myseq');
$$ LANGUAGE SQL;

Does anybody know which version is actually better/faster/more optimal?

In recent releases the SQL version would be better, since it would
actually get "inlined" into the calling query and thus the function
overhead would be zero. However this only happens for a fairly narrow
set of cases (function returning scalar, not set, and there are
constraints as to strictness and volatility properties). A non-inlined
SQL function is probably slower than plpgsql, because the SQL-function
executor code isn't amazingly efficient (doesn't cache query plans from
one use to the next, for instance).

So the short answer is "it depends".

regards, tom lane

Thanks for the help Tony,
But im still having some trouble.
Here is the exact 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 := nextval("seqslideid");

BEGIN
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;
-------------------------------------------
I can get the code above to fire no problem. However, when i run the
following i get an error.
------------------------------------------
select sp_slide_create(88, true, true, true, 'varcharOne', 'textOne',
'varcharTwo', 'varcharThree', 2);
ERROR: column "seqslideid" does not exist
CONTEXT: PL/pgSQL function "sp_slide_create" line 14 at block variables
initialization
------------------------------------------
Thanks for the help

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:

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

Or upgrade your server to 8.x and use dollar quoting.
with dollar quoting all that is a thing of the past.

CREATE FUNCTION sp_slide_create(int4) RETURNS int4 AS
$$
DECLARE
aScriptID ALIAS FOR $1;
seqID int4 := nextval('genseq'); -- no magic needed with dollar qouting :-)
BEGIN

INSERT INTO tblslides (slideid) VALUES (seqID);

RETURN seqID;

END;
$$
LANGUAGE 'plpgsql' VOLATILE

ahhh... very nice. Thank you.