pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFinding if a temp table exists in the current connection
3+ messages / 3 participants
[nested] [flat]
* Finding if a temp table exists in the current connection
@ 2003-04-02 08:09 Mike Papper <m.papper@fantastic.com>
0 siblings, 0 replies; 3+ messages in thread
From: Mike Papper @ 2003-04-02 08:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding if a temp table exists in the current connection
@ 2003-04-04 11:16 Christoph Haller <ch@rodos.fzk.de>
0 siblings, 1 reply; 3+ messages in thread
From: Christoph Haller @ 2003-04-04 11:16 UTC (permalink / raw)
To: pgsql-sql; +Cc: m.papper@fantastic.com
>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Finding if a temp table exists in the current connection
@ 2003-04-04 14:58 Tom Lane <tgl@sss.pgh.pa.us>
parent: Christoph Haller <ch@rodos.fzk.de>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2003-04-04 14:58 UTC (permalink / raw)
To: Christoph Haller <ch@rodos.fzk.de>; +Cc: pgsql-sql; m.papper@fantastic.com
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2003-04-04 14:58 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-02 08:09 Finding if a temp table exists in the current connection Mike Papper <m.papper@fantastic.com>
2003-04-04 11:16 Re: Finding if a temp table exists in the current connection Christoph Haller <ch@rodos.fzk.de>
2003-04-04 14:58 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox