Re: Temporary table visibility

On 1/25/06, James Croft <james(dot)croft(at)lumison(dot)net> wrote:
>
> On 25 Jan 2006, at 14:17, Jaime Casanova wrote:
>
> >> How can I determine what temporary tables exist in my session,
> >> bearing in mind that other sessions contain temp tables using the
> >> same names?
> >>
> >
> > just the ones you have created in your session, temporary tables in
> > other sessions are invisible to you...
>
>
> Thanks Jaime but that's not really what I meant.
>
> I know that if a session creates a temporary table it is only visible
> to that session. I'm not doing a good job of explaining this but
> basically given the following results...
>
> test=> select relname, relnamespace, reltype from pg_class where
> relname = 'session_data';
> relname | relnamespace | reltype
> --------------+--------------+----------
> session_data | 2200 | 16114367
> session_data | 16120903 | 16314010
> session_data | 16120709 | 16314030
> session_data | 16122659 | 16314133
> session_data | 16123201 | 16314285
> session_data | 16124398 | 16315049
> session_data | 16767 | 16315527
> session_data | 16120382 | 16315818
> session_data | 16125558 | 16315816
> session_data | 16114413 | 16316810
> session_data | 16127654 | 16317471
> session_data | 16114683 | 16317551
> session_data | 16118447 | 16317563
> session_data | 15035529 | 16317579
> (14 rows)
>
> 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)?
>
>
> Thanks,
> James
>
>

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);

Maybe this is what you want?

FWIW, this was make just with psql -E (to view what query \d executes
and changing the "AND n.nspname NOT IN " line for something more
apropiate...

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)