Skip site navigation (1) Skip section navigation (2)

Re: BUG #3662: Seems that more than one run of a functions causes an error

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Robins Tharakan" <tharakan(at)gmail(dot)com>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #3662: Seems that more than one run of a functions causes an error
Date: 2007-10-11 00:00:18
Message-ID: 27859.1192060818@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Robins Tharakan" <tharakan(at)gmail(dot)com> writes:
> BEGIN
>     CREATE TEMPORARY SEQUENCE s INCREMENT BY 1 START WITH 1;
>     FOR rec in
>         SELECT nextval('s') as rank, tt.scheme_code, tt.ret
>     ...
>     DROP SEQUENCE s;
> END;

Sorry, that's not going to work, for fundamentally the same reason that
references in this style to temp tables don't work --- the OID of the
sequence gets embedded into the nextval() call on first use of the
function.  Consider creating the temp sequence just once per session
and resetting it on subsequent uses; or use EXECUTE to process that
SELECT.  Or maybe you could dispense with the sequence altogether ---
a local-variable counter inside the function would be a vastly
lighter-weight solution anyway.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-10-11 00:12:17
Subject: Re: BUG #3662: Seems that more than one run of a functions causes an error
Previous:From: Tom LaneDate: 2007-10-10 21:32:17
Subject: Re: BUG #3668: type error in serial

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group