Обсуждение: Check the existance of temporary table

Dear Sirs,

how I can check  the existance of temporary table?
I.e. wich query I have to use to know if MY_TEMP_TABLE exists?

IF EXISTS(??? query ???) THEN

Thank you in advance

Domenico

Domenico-

Assuming your schema will be  pg_temp_1
vi InitialTableDisplayStatements.sql
select * from pg_tables where pg_namespace = 'pg_temp1';

/usr/local/pgsql/bin/psql -f InitialTableDisplayStatements.sql -U username -h dbname -p Port > db.out

then write a quick java app to parse the db.out contents for 'Temp'

Ciao-
M--
--------------------------------------------------------------------------- 
This e-mail message (including attachments, if any) is intended for the use of the individual or entity to which it is
addressedand may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you
arenot the intended recipient, you are notified that any dissemination, distribution or copying of this communication
isstrictly prohibited.
 
--------------------------------------------------------------------------- 
Le présent message électronique (y compris les pièces qui y sont annexées, le cas échéant) s'adresse au destinataire
indiquéet peut contenir des renseignements de caractère privé ou confidentiel. Si vous n'êtes pas le destinataire de ce
document,nous vous signalons qu'il est strictement interdit de le diffuser, de le distribuer ou de le reproduire.
 
----- Original Message ----- 
From: "dfx" <dfx@dfx.it>
To: <pgsql-general@postgresql.org>
Sent: Sunday, March 25, 2007 8:32 AM
Subject: [GENERAL] Check the existance of temporary table


> Dear Sirs,
> 
> how I can check  the existance of temporary table?
> I.e. wich query I have to use to know if MY_TEMP_TABLE exists?
> 
> IF EXISTS(??? query ???) THEN
> 
> Thank you in advance
> 
> Domenico
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>

"dfx" <dfx@dfx.it> writes:
> how I can check  the existance of temporary table?
> I.e. wich query I have to use to know if MY_TEMP_TABLE exists?

As of 8.2 you can do

SELECT ... FROM pg_class
  WHERE relname = 'whatever' AND relnamespace = pg_my_temp_schema();

In earlier releases pg_my_temp_schema() isn't built in, so you have
to do some pushups to determine which schema is your temp schema.

            regards, tom lane

In stored procedures I used something like

BEGIN
    CREATE TEMPORARY TABLE tmp ...
EXCEPTION
    WHEN ... THEN ...
END

See pg error codes for details (I don't remember exactly, but maybe it is a dumplicate_table or duplicate_object exception).

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