Thread: PostgreSQL sequence within function

I am new to Postgre, and am still learning some of the basics...
please bare with me.

I need to know how to access a sequence from within a function. Let me
know whats wrong with the following...

(this is not the exact function, just for examples sake...)
----------------------------------------------
CREATE FUNCTION getSeq()
RETURNS int AS'
RETURN nextval('myseq')
'LANGUAGE 'plpgsql';
----------------------------------------------

Thanks for the help
Clark

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:

>----------------------------------------------
>CREATE FUNCTION getSeq()
>RETURNS int AS'
>RETURN nextval('myseq')
>'LANGUAGE 'plpgsql';
>----------------------------------------------
>
>Thanks for the help
>Clark
>
>

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

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

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

 RETURN seqID;
END;'