Обсуждение: 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 >