On 1/25/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jaime Casanova <systemguards@gmail.com> writes:
> > On 1/25/06, James Croft <james.croft@lumison.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
>
jeje... need more coffee... and i really have to put my hands on that
regex book on the corner...
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)