From: | ptjm(at)interlog(dot)com (Patrick TJ McPhee) |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Check the existance of temporary table |
Date: | 2007-03-25 16:41:00 |
Message-ID: | 130d9gs91qvpm42@corp.supernews.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Webb Sprague | 2007-03-25 17:09:24 | Matrix (linear algebra) operations and types in PG? |
Previous Message | Erik Jones | 2007-03-25 16:30:21 | Re: Tracking disk writes? |