pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
See Temp Table from Trigger 2+ messages / 2 participants [nested] [flat]
* See Temp Table from Trigger @ 2003-05-13 16:34 Joachim Zauner <j.zauner@epcom.cc> 0 siblings, 0 replies; 2+ messages in thread From: Joachim Zauner @ 2003-05-13 16:34 UTC (permalink / raw) To: pgsql-sql 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? Thanks J. Zauner ---------------------------------------------- epcom.cc Joachim Zauner Fronleichnamsweg 8 8940 Liezen Tel: +43 (0)3612 21269 Fax: +43 (0)3612 21268 web: www.epcom.cc mail:j.zauner@epcom.cc ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: See Temp Table from Trigger @ 2003-05-16 08:41 Christoph Haller <ch@rodos.fzk.de> 0 siblings, 0 replies; 2+ messages in thread From: Christoph Haller @ 2003-05-16 08:41 UTC (permalink / raw) To: pgsql-sql; +Cc: j.zauner@epcom.cc > > 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-05-16 08:41 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2003-05-13 16:34 See Temp Table from Trigger Joachim Zauner <j.zauner@epcom.cc> 2003-05-16 08:41 Re: See Temp Table from Trigger Christoph Haller <ch@rodos.fzk.de>
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