FAQ
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

Search Discussions

  • Jens Wilke at Mar 8, 2011 at 2:28 pm
    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
  • Gnanakumar at Mar 8, 2011 at 2:47 pm

    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.
  • Tom Lane at Mar 8, 2011 at 3:40 pm

    "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
  • Gnanakumar at Mar 9, 2011 at 4:46 am

    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.
  • Christian Ullrich at Mar 9, 2011 at 9:07 am

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-admin @
categoriespostgresql
postedMar 8, '11 at 2:06p
activeMar 9, '11 at 9:07a
posts6
users4
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase