Обсуждение: How to get schema name in which TEMPORARY table is created?
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
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
> 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.
"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
>> 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.
* 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.