[GENERAL] temp tables and sequences in functions

[Rob]

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.