From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Jaime Casanova <systemguards(at)gmail(dot)com> |
Cc: | James Croft <james(dot)croft(at)lumison(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Temporary table visibility |
Date: | 2006-01-25 16:07:40 |
Message-ID: | 1325.1138205260@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Jaime Casanova <systemguards(at)gmail(dot)com> writes:
> On 1/25/06, James Croft <james(dot)croft(at)lumison(dot)net> wrote:
>> How can I determine if one of the above relations is a temporary
>> table in the current session (one of them, the first in ns 2200, is a
>> normal permanent table)?
> SELECT n.nspname as "Schema", c.relname as "Name",
> CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i'
> THEN 'ndex' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
> "Type",
> r.rolname as "Owner"
> FROM pg_catalog.pg_class c
> LEFT JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','v','S','')
> AND n.nspname LIKE 'pg_temp%'
> AND pg_catalog.pg_table_is_visible(c.oid);
Close, but you really ought to escape the _ to avoid it being a LIKE
wildcard. I'd tend to use a regex instead since _ isn't a wildcard
in regex patterns. So the essential part of this is something like
select relname
from pg_catalog.pg_class c
join pg_catalog.pg_namespace n on n.oid = c.relnamespace
where nspname ~ '^pg_temp_'
and pg_catalog.pg_table_is_visible(c.oid);
The test on the namespace name tells you it's temp (yes, this is a
legit way to do it, it's the same way the backend decides it's a
temp namespace) and the test on visibility is an easy way to see if
it's your temp namespace or someone else's.
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Fuhr | 2006-01-25 16:09:08 | Re: FW: deleted records |
Previous Message | Martijn van Oosterhout | 2006-01-25 16:01:39 | Re: Alternative to knoda, kexi and rekall? |