Message-ID: <3EC4A444.75A2DD8B@rodos.fzk.de>
Date: Fri, 16 May 2003 10:41:40 +0200
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: j.zauner@epcom.cc
Subject: Re: See Temp Table from Trigger
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> Hi List!
> Is there a way a Trigger can see a Temporary Table or better - check
if a
> Temp Table exists?
> - Or can i define a "Session Variable" that will allow this?

Joachim,
Find below a previuos thread on temp-table-existence.
Hope this helps.
Regards, Christoph

From: Mike Papper <m.papper@fantastic.com>
Subject: [SQL] Finding if a temp table exists in the current connection
Date: Wed, 02 Apr 2003 00:09:33 -0800

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


From: Christoph Haller <ch@rodos.fzk.de>
Subject: Re: [SQL] Finding if a temp table exists in the current
connection
Date: Fri, 04 Apr 2003 13:16:16 +0200

 >
 > 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


From: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: [SQL] Finding if a temp table exists in the current
connection
Date: Fri, 04 Apr 2003 09:58:24 -0500

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