Thread: Finding if a temp table exists in the current connection

Is there a way (i.e., access theinternal pg_ tables) to find out if a 
particular temp table already exists (in the current connection)?

I have written some stored procedures that check if a table exists and if a 
column exists in a table. This is used so I can perform upgrades of our 
system (if table doesnt exist, call create table... sql).

I would like to do a similar thing for temp tables.

I have noticed that if another connection creates a temp table, it will 
show up in the pg_* tables so that all ocnnections can see the table.

Is there some standard SQL way to test?

-- 
Mike Papper

>> Is there a way (i.e., access theinternal pg_ tables) to find out if a> particular temp table already exists (in the
currentconnection)?>> I have written some stored procedures that check if a table exists 
 
and if a> column exists in a table. This is used so I can perform upgrades of our> system (if table doesnt exist, call
createtable... sql).>> I would like to do a similar thing for temp tables.>> I have noticed that if another connection
createsa temp table, it will> show up in the pg_* tables so that all ocnnections can see the table.>> Is there some
standardSQL way to test?>
 
I'm using 7.3.2 and it seems there is a way.
I've found out by starting a psql session using the -E option.
This shows you the SQL behind \dt which shows the current temp tables.
It looks like they are created in name spaces called "pg_temp_<N>,
where N is simply a connection counter.

SELECT n.nspname as "Schema",c.relname as "Name"
FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid 
= c.relnamespace
where n.nspname like 'pg_temp_%' AND pg_catalog.pg_table_is_visible(c.oid) ;

If I do (within 1st connection)
create temp table tgif (dummy int);
and the select above returns Schema   | Name
-----------+------pg_temp_1 | tgif
(1 row)
And within a 2nd connection
create temp table tgif (dummy int);
and the select above returns Schema   | Name
-----------+------pg_temp_2 | tgif
(1 row)

Does this help?

Regards, Christoph

Christoph Haller <ch@rodos.fzk.de> writes:
> It looks like they are created in name spaces called "pg_temp_<N>,
> where N is simply a connection counter.

Right.  If you are trying to find out which N applies to your session,
here is one way:

regression=# select current_schemas(true);  current_schemas
---------------------{pg_catalog,public}
(1 row)

regression=# create temp table z(f1 int);
CREATE TABLE
regression=# select current_schemas(true);       current_schemas
-------------------------------{pg_temp_2,pg_catalog,public}
(1 row)
        regards, tom lane