Re: Temp table exists test??

On Thu, Feb 03, 2005 at 09:39:08PM -0500, Michael Guerin wrote:

> I'm trying to detect the existence of a temp table in a function for
> that connection.

Why do you need to know this?  So you don't try to create a temporary
table multiple times?

> Connection 1:
> Create table foo (i int);
>
> Connection 2:
> select * from pg_class where relname = 'foo'
>
> returns the table from connection 1, however I need to know if there's a
> temp table foo for connection 2 not connection 1.  Each row in pg_class
> is associated with a namespace, so I'm looking for something like:
>
> select * from pg_class where relname = 'foo' and relnamespace = ???

If you don't care whether the table is temporary or not then you
could simply test if it's visible:

SELECT *
FROM pg_class
WHERE relname = 'foo'
  AND relkind = 'r'
  AND pg_table_is_visible(oid);

If you want to limit the query to temporary tables, then you could
join pg_class with pg_namespace and look for temporary schema names:

SELECT n.nspname, c.relname
FROM pg_class AS c
JOIN pg_namespace AS n ON n.oid = c.relnamespace
WHERE c.relname = 'foo'
  AND c.relkind = 'r'
  AND n.nspname LIKE 'pg_temp_%'
  AND pg_table_is_visible(c.oid);

Is that what you're looking for?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/