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