Thread: temp tables and sequences in functions
Postgres v8.2
I'm having a problem with using temporary tables and sequences in a function. It seems that the temp tables are not being removed once the function has completed. I'm also running into OID conflicts when I run the function in another session. I've had to do things like this to be able to run a function multiple times in the same session:
if not exists ( select * from pg_class
where relName = 'dcxcount'
and relkind = 'S'
and relOwner = ( select oid from pg_roles where rolName = current_user ))
then
CREATE temp sequence dcxCount start 1 increment 1;
else
ALTER SEQUENCE dcxCount RESTART WITH 1;
end if;
But that doesn't work if I run the function in another session. The sequence does exist, but I can't alter the sequence in the other session. I get the following error:
ERROR: relation "dcxcount" does not exist
SQL state: 42P01
Context: SQL statement "ALTER SEQUENCE dcxCount RESTART WITH 1"
What is the proper why to deal with temp tables and sequences? Why aren't they being dropped after the function ends? Why do I get OID errors if I delete the temp table/sequence at the end of the function and then try to rerun the function?
ERROR: could not open relation with OID 58341
SQL state: XX000
Thanks
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.
I'm having a problem with using temporary tables and sequences in a function. It seems that the temp tables are not being removed once the function has completed. I'm also running into OID conflicts when I run the function in another session. I've had to do things like this to be able to run a function multiple times in the same session:
if not exists ( select * from pg_class
where relName = 'dcxcount'
and relkind = 'S'
and relOwner = ( select oid from pg_roles where rolName = current_user ))
then
CREATE temp sequence dcxCount start 1 increment 1;
else
ALTER SEQUENCE dcxCount RESTART WITH 1;
end if;
But that doesn't work if I run the function in another session. The sequence does exist, but I can't alter the sequence in the other session. I get the following error:
ERROR: relation "dcxcount" does not exist
SQL state: 42P01
Context: SQL statement "ALTER SEQUENCE dcxCount RESTART WITH 1"
What is the proper why to deal with temp tables and sequences? Why aren't they being dropped after the function ends? Why do I get OID errors if I delete the temp table/sequence at the end of the function and then try to rerun the function?
ERROR: could not open relation with OID 58341
SQL state: XX000
Thanks
Park yourself in front of a world of choices in alternative vehicles.
Visit the Yahoo! Auto Green Center.
am Wed, dem 05.09.2007, um 6:58:30 -0700 mailte Rob folgendes: > What is the proper why to deal with temp tables and sequences? Why aren't they > being dropped after the function ends? Why do I get OID errors if I delete the > temp table/sequence at the end of the function and then try to rerun the > function? > > ERROR: could not open relation with OID 58341 > SQL state: XX000 normal behavior. Read more about this problem here: http://merlinmoncure.blogspot.com/2007/09/as-previously-stated-postgresql-8.html Solution: use EXECUTE for DDL-commands inside functions. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net