Re: Temporary table visibility

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