> 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?
>
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
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
