Re: [GENERAL] Check the existance of temporary table
In article <[EMAIL PROTECTED]>, Martin Gainty <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Check the existance of temporary table
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). On 3/25/07, Tom Lane <[EMAIL PROTECTED]> wrote: "dfx" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Check the existance of temporary table
"dfx" <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Check the existance of temporary table
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 addressed and may contain information that is privileged, proprietary , confidential and exempt from disclosure. If you are not the intended recipient, you are notified that any dissemination, distribution or copying of this communication is strictly 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" <[EMAIL PROTECTED]> To: 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 [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
