Re: Check the existance of temporary table

In article <BAY133-DAV980F324FFC1159F12BBF7AE680(at)phx(dot)gbl>,
Martin Gainty <mgainty(at)hotmail(dot)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(at)interlog(dot)com