Return value of CREATE TABLE

Started by Mike Martinover 5 years ago3 messagesgeneral
Jump to latest
#1Mike Martin
redtux1@gmail.com

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

#2Christoph Moench-Tegeder
cmt@burggraben.net
In reply to: Mike Martin (#1)
Re: Return value of CREATE TABLE

## Mike Martin (redtux1@gmail.com):

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

#3Mike Martin
mike@redtux.plus.com
In reply to: Christoph Moench-Tegeder (#2)
Re: Return value of CREATE TABLE

Thanks , exactly what I was looking for

On Thu, 10 Sep 2020 at 13:16, Christoph Moench-Tegeder <cmt@burggraben.net>
wrote:

Show quoted text

## Mike Martin (redtux1@gmail.com):

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