Обсуждение: pg_temp 101 question

My application creates/uses a temporary table "X" via multiple
connections at the same time. Is there a way to determine which
pg_temp_N belongs to the current connection?

I need this to obtain list of attributes for the temporary table...
All connections are using the same temp table name (by design made
long time ago for another DB). So if I query:
select T.schemaname, T.tablename, A.attname, A.atttypid, TY.typname, D.adsrc
from pg_attribute A
inner join pg_class C on (A.attrelid=C.oid)
inner join pg_tables T on (C.relname=T.tablename)
inner join pg_namespace NS on (NS.oid=C.relnamespace and
NS.nspname=T.schemaname)
inner join pg_type TY on (TY.oid=A.atttypid)
left outer join pg_attrdef D on (D.adrelid=C.oid and D.adnum=A.attnum)
where A.attnum>0 and A.attisdropped='f' and T.schemaname like
'pg_temp%' and T.tablename='X'

it returns list of all columns in all temporary tables. I just need
columns list for temp table for the current connection.

Thank you!

Konstantin Izmailov <pgfizm@gmail.com> writes:
> My application creates/uses a temporary table "X" via multiple
> connections at the same time. Is there a way to determine which
> pg_temp_N belongs to the current connection?

It seems unlikely that you need to determine that explicitly to solve
your problem.  Just use WHERE C.oid = 'X'::regclass to constrain the
pg_class query, and forget matching to the schema name at all.

            regards, tom lane

Tom, thank you for the suggestion - it looks like it is working!

I've found another solution by looking into psql source code:

  nspname like 'pg_temp%' AND pg_catalog.pg_table_is_visible(C.oid)

can also be added to the query for the purpose.


On 1/3/11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Konstantin Izmailov <pgfizm@gmail.com> writes:
>> My application creates/uses a temporary table "X" via multiple
>> connections at the same time. Is there a way to determine which
>> pg_temp_N belongs to the current connection?
>
> It seems unlikely that you need to determine that explicitly to solve
> your problem.  Just use WHERE C.oid = 'X'::regclass to constrain the
> pg_class query, and forget matching to the schema name at all.
>
>             regards, tom lane
>