Thread: 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.