"Robins Tharakan" <tharakan@gmail.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
