Re: Check the existance of temporary table

In article <BAY133-DAV980F324FFC1159F12BBF7AE680@phx.gbl>,
Martin Gainty <mgainty@hotmail.com> wrote:

% Assuming your schema will be  pg_temp_1

Not a particularly reasonable assumption...

% vi InitialTableDisplayStatements.sql
% select * from pg_tables where pg_namespace = 'pg_temp1';

pmcphee=# select * from pg_tables where schemaname like 'pg_temp%';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers
------------+-----------+------------+------------+------------+----------+-------------
 pg_temp_2  | x         | pmcphee    | x          | f          | f        | f
(1 row)

pmcphee=# select * from x;
ERROR:  relation "x" does not exist

But the test itself is problematic. I think this query is better.

 select pg_table_is_visible(pg_class.oid)
  from pg_class, pg_namespace
  where relname = 'x' and
    relnamespace = pg_namespace.oid and
    nspname like 'pg_temp%';

From the same session where the select failed:

pmcphee=#  select pg_table_is_visible(pg_class.oid)
pmcphee-#   from pg_class, pg_namespace
pmcphee-#   where relname = 'x' and
pmcphee-#         relnamespace = pg_namespace.oid and
pmcphee-#         nspname like 'pg_temp%';
 pg_table_is_visible
---------------------
 f
(1 row)

If I go on to create the temp table in the current session, this returns
 pg_table_is_visible
---------------------
 f
 t
(2 rows)

so you need to be ready for more than one row, or sort the output and
put a limit on it.
--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com