pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to get schema name in which TEMPORARY table is created?
6+ messages / 4 participants
[nested] [flat]
* How to get schema name in which TEMPORARY table is created?
@ 2011-03-08 14:02 Gnanakumar <gnanam@zoniac.com>
2011-03-08 14:18 ` Re: How to get schema name in which TEMPORARY table is created? Jens Wilke <jens.wilke@affinitas.de>
2011-03-08 14:43 ` Re: How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
2011-03-08 15:39 ` Re: How to get schema name in which TEMPORARY table is created? Tom Lane <tgl@sss.pgh.pa.us>
2011-03-09 09:07 ` Re: How to get schema name in which TEMPORARY table is created? Christian Ullrich <chris@chrullrich.net>
0 siblings, 4 replies; 6+ messages in thread
From: Gnanakumar @ 2011-03-08 14:02 UTC (permalink / raw)
To: pgsql-admin
Hi,
We're using PostgreSQL v8.2.3.
How do I get the schema name in which TEMPORARY table is created using
"CREATE TEMP TABLE mytable ...." syntax?
In our application, we're creating temporary table with the same as an
existing permanent table which is available in "public" schema. Hence, I
want to find out the schema name in which temporary tables are getting
created.
Regards,
Gnanam
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get schema name in which TEMPORARY table is created?
2011-03-08 14:02 How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
@ 2011-03-08 14:18 ` Jens Wilke <jens.wilke@affinitas.de>
3 siblings, 0 replies; 6+ messages in thread
From: Jens Wilke @ 2011-03-08 14:18 UTC (permalink / raw)
To: pgsql-admin
On Tuesday 08 March 2011 15:02:57 Gnanakumar wrote:
Hi,
> How do I get the schema name in which TEMPORARY table is created using
> "CREATE TEMP TABLE mytable ...." syntax?
select n.nspname from pg_class c join pg_namespace n on n.oid=c.relnamespace
where c.relname ='foo' and n.nspname like 'pg_temp%';
Regards Jens
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get schema name in which TEMPORARY table is created?
2011-03-08 14:02 How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
@ 2011-03-08 14:43 ` Gnanakumar <gnanam@zoniac.com>
3 siblings, 0 replies; 6+ messages in thread
From: Gnanakumar @ 2011-03-08 14:43 UTC (permalink / raw)
To: jens.wilke@affinitas.de; +Cc: pgsql-admin
> select n.nspname from pg_class c join pg_namespace n on
n.oid=c.relnamespace
> where c.relname ='foo' and n.nspname like 'pg_temp%';
This will return all the schema name that are available which were created
using TEMP TABLE syntax. Since our application is web-based, of course,
there will be more than one schema name listed out of this query. My
question is, how do I get the schema name of the temporary table immediately
after calling "CREATE TEMP TABLE mytable ...." syntax? I need this schema
name for one of my analysis purpose.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get schema name in which TEMPORARY table is created?
2011-03-08 14:02 How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
@ 2011-03-08 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-03-09 04:43 ` Re: How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
3 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2011-03-08 15:39 UTC (permalink / raw)
To: gnanam@zoniac.com; +Cc: pgsql-admin
"Gnanakumar" <gnanam@zoniac.com> writes:
> We're using PostgreSQL v8.2.3.
> How do I get the schema name in which TEMPORARY table is created using
> "CREATE TEMP TABLE mytable ...." syntax?
Do you need the real schema name, or will the "pg_temp" alias be
sufficient?
regression=# create temp table foo(f1 int);
CREATE TABLE
regression=# select * from pg_temp.foo;
f1
----
(0 rows)
Note: I think that this might not work in 8.2.3 --- I seem to recall
that it was added as part of the CVE-2007-2138 fix, which appeared in
the 8.2.x series in 8.2.4. However, there are many good reasons for
you to update to something later than 8.2.3 anyway.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get schema name in which TEMPORARY table is created?
2011-03-08 14:02 How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
2011-03-08 15:39 ` Re: How to get schema name in which TEMPORARY table is created? Tom Lane <tgl@sss.pgh.pa.us>
@ 2011-03-09 04:43 ` Gnanakumar <gnanam@zoniac.com>
0 siblings, 0 replies; 6+ messages in thread
From: Gnanakumar @ 2011-03-09 04:43 UTC (permalink / raw)
To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
>> How do I get the schema name in which TEMPORARY table is created using
>> "CREATE TEMP TABLE mytable ...." syntax?
> Do you need the real schema name, or will the "pg_temp" alias be
> sufficient?
I need the real schema name (for example, pg_temp_xxx) in which it is
created and not just the alias.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to get schema name in which TEMPORARY table is created?
2011-03-08 14:02 How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
@ 2011-03-09 09:07 ` Christian Ullrich <chris@chrullrich.net>
3 siblings, 0 replies; 6+ messages in thread
From: Christian Ullrich @ 2011-03-09 09:07 UTC (permalink / raw)
To: pgsql-admin
* Gnanakumar wrote:
> We're using PostgreSQL v8.2.3.
>
> How do I get the schema name in which TEMPORARY table is created using
> "CREATE TEMP TABLE mytable ...." syntax?
SELECT nspname FROM pg_namespace WHERE oid = pg_my_temp_schema();
I don't have an 8.2.3 lying around, but git says that function is in there.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-03-09 09:07 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-03-08 14:02 How to get schema name in which TEMPORARY table is created? Gnanakumar <gnanam@zoniac.com>
2011-03-08 14:18 ` Jens Wilke <jens.wilke@affinitas.de>
2011-03-08 14:43 ` Gnanakumar <gnanam@zoniac.com>
2011-03-08 15:39 ` Tom Lane <tgl@sss.pgh.pa.us>
2011-03-09 04:43 ` Gnanakumar <gnanam@zoniac.com>
2011-03-09 09:07 ` Christian Ullrich <chris@chrullrich.net>
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