PostgreSQL sequence within function

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

PostgreSQL sequence within function

Clark Allan
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

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



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to [hidden email] so that your
       message can get through to the mailing list cleanly
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

Tom Lane-2
Russ Brown <[hidden email]> 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

Clark Allan
In reply to this post by Clark Allan
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
 
On 6/30/05, Tony Caduto <[hidden email]> 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:

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


Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

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



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

Clark Allan
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 <[hidden email]> 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.
>



Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

snorkel
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


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match
Reply | Threaded
Open this post in threaded view
|

Re: PostgreSQL sequence within function

Clark Allan
ahhh... very nice. Thank you.

On 7/5/05, Tony Caduto <[hidden email]> wrote:
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