Return value of CREATE TABLE

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Return value of CREATE TABLE

Mike Martin
Is this possible?
Basically I want to manage temp table status for use in a procedure.

The procedure looks at pg_catalog to get information for processing.

So basically I would like to be able to know what namespace a temp table is created in, so that I can constrain lookup.

example

CREATE TEMP TABLE tagdata (test int,test2 numeric(10,2));
SELECT relname,relpersistence ,relnamespace
,pa.atttypid,attname,attnum
,nspname
FROM pg_catalog.pg_class pc
JOIN pg_attribute pa ON pc.oid=pa.attrelid
JOIN pg_namespace pn ON pn.oid=relnamespace
WHERE relname = 'tagdata' AND attnum>0

Which returns (when its run for the second time in different tabs in pgadmin)
relname   persistence namespace typeid colname colnum schema
"tagdata" "p"             "2200"         "23"    "fileid"        1      "public"
"tagdata" "p"             "2200"         "25"    "tagname"  2      "public"
"tagdata" "p"             "2200"     "1009"    "tagvalue"  3       "public"
"tagdata" "t"           "483934"        "23"    "test"          1    "pg_temp_10"
"tagdata" "t"            "538079"       "23"    "test"          1    "pg_temp_13"
"tagdata" "t"            "538079"    "1700"   "test2"        2    "pg_temp_13"

So I would like some way of knowing exactly which schema the temp table has been created in, I cant see anything obvious

thanks

Mike
Reply | Threaded
Open this post in threaded view
|

Re: Return value of CREATE TABLE

Christoph Moench-Tegeder
## Mike Martin ([hidden email]):

> So basically I would like to be able to know what namespace a temp table is
> created in, so that I can constrain lookup.

pg_my_temp_schema() returns the OID of the session's temporary schema
("or 0 if none", according to the docs).

Regards,
Christoph

--
Spare Space


Reply | Threaded
Open this post in threaded view
|

Re: Return value of CREATE TABLE

Mike Martin-2
Thanks , exactly what I was looking for

On Thu, 10 Sep 2020 at 13:16, Christoph Moench-Tegeder <[hidden email]> wrote:
## Mike Martin ([hidden email]):

> So basically I would like to be able to know what namespace a temp table is
> created in, so that I can constrain lookup.

pg_my_temp_schema() returns the OID of the session's temporary schema
("or 0 if none", according to the docs).

Regards,
Christoph

--
Spare Space