From tlqmail@yahoo.com Fri Feb 14 15:34:09 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 2528C474E4F for ; Fri, 14 Feb 2003 10:34:09 -0500 (EST) Received: from web14802.mail.yahoo.com (web14802.mail.yahoo.com [216.136.224.218]) by postgresql.org (Postfix) with SMTP id 70F2A474E42 for ; Fri, 14 Feb 2003 10:34:08 -0500 (EST) Message-ID: <20030214153413.24680.qmail@web14802.mail.yahoo.com> Received: from [66.139.15.154] by web14802.mail.yahoo.com via HTTP; Fri, 14 Feb 2003 07:34:13 PST Date: Fri, 14 Feb 2003 07:34:13 -0800 (PST) From: Mintoo Lall Subject: Drop temporary table only if it exists To: pgsql-sql@postgresql.org MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-182278681-1045236853=:24294" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/330 X-Sequence-Number: 11728 --0-182278681-1045236853=:24294 Content-Type: text/plain; charset=us-ascii Hi Everybody, How do I drop a temporary table ( or for that matter a sequence) only if it exists or create them if they donot exist ? I want to do this to avoid any errors Any help is appreciated . Thanks, Tarun --------------------------------- Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day --0-182278681-1045236853=:24294 Content-Type: text/html; charset=us-ascii

Hi Everybody,

How do I drop a temporary table ( or for that matter a sequence) only if it exists  or create them if they donot exist ? I want to do this to avoid any errors

Any help is appreciated .

Thanks,

Tarun


Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day --0-182278681-1045236853=:24294-- From josh@agliodbs.com Fri Feb 14 17:48:40 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7F15C475AEC for ; Fri, 14 Feb 2003 12:48:40 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id C325E475AD7 for ; Fri, 14 Feb 2003 12:48:39 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2840224; Fri, 14 Feb 2003 09:48:52 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Mintoo Lall , pgsql-sql@postgresql.org Subject: Re: Drop temporary table only if it exists Date: Fri, 14 Feb 2003 09:48:15 -0800 User-Agent: KMail/1.4.3 References: <20030214153413.24680.qmail@web14802.mail.yahoo.com> In-Reply-To: <20030214153413.24680.qmail@web14802.mail.yahoo.com> MIME-Version: 1.0 Message-Id: <200302140948.15889.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/338 X-Sequence-Number: 11736 Mintoo, > How do I drop a temporary table ( or for that matter a sequence) only if = it > exists or create them if they donot exist ? I want to do this to avoid a= ny > errors A quick check for existance:=20 SELECT * FROM pg_class WHERE relname =3D 'name_of_temp_table' and relkind = =3D ? You'll want to look in the docs about the PG system tables to see what=20 "relkind" you should test on for each type of object. --=20 Josh Berkus Aglio Database Solutions San Francisco From tlqmail@yahoo.com Fri Feb 14 18:12:19 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6A790475957 for ; Fri, 14 Feb 2003 13:12:19 -0500 (EST) Received: from web14802.mail.yahoo.com (web14802.mail.yahoo.com [216.136.224.218]) by postgresql.org (Postfix) with SMTP id B3FA44758E6 for ; Fri, 14 Feb 2003 13:12:15 -0500 (EST) Message-ID: <20030214181214.75346.qmail@web14802.mail.yahoo.com> Received: from [66.139.15.154] by web14802.mail.yahoo.com via HTTP; Fri, 14 Feb 2003 10:12:14 PST Date: Fri, 14 Feb 2003 10:12:14 -0800 (PST) From: Mintoo Lall Subject: Re: Drop temporary table only if it exists To: Josh Berkus , pgsql-sql@postgresql.org In-Reply-To: <200302140948.15889.josh@agliodbs.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-778709281-1045246334=:73625" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/340 X-Sequence-Number: 11738 --0-778709281-1045246334=:73625 Content-Type: text/plain; charset=us-ascii Thanks Josh Berkus wrote:Mintoo, > How do I drop a temporary table ( or for that matter a sequence) only if it > exists or create them if they donot exist ? I want to do this to avoid any > errors A quick check for existance: SELECT * FROM pg_class WHERE relname = 'name_of_temp_table' and relkind = ? You'll want to look in the docs about the PG system tables to see what "relkind" you should test on for each type of object. -- Josh Berkus Aglio Database Solutions San Francisco --------------------------------- Do you Yahoo!? Yahoo! Shopping - Send Flowers for Valentine's Day --0-778709281-1045246334=:73625 Content-Type: text/html; charset=us-ascii

Thanks

 Josh Berkus <josh@agliodbs.com> wrote:

Mintoo,

> How do I drop a temporary table ( or for that matter a sequence) only if it
> exists or create them if they donot exist ? I want to do this to avoid any
> errors

A quick check for existance:

SELECT * FROM pg_class WHERE relname = 'name_of_temp_table' and relkind = ?

You'll want to look in the docs about the PG system tables to see what
"relkind" you should test on for each type of object.

--
Josh Berkus
Aglio Database Solutions
San Francisco


Do you Yahoo!?
Yahoo! Shopping - Send Flowers for Valentine's Day --0-778709281-1045246334=:73625-- From greg@turnstep.com Fri Feb 14 18:41:10 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id A2749474E4F for ; Fri, 14 Feb 2003 13:41:10 -0500 (EST) Received: from biglumber.com (biglumber.com [207.228.252.42]) by postgresql.org (Postfix) with SMTP id F2D10474E42 for ; Fri, 14 Feb 2003 13:41:09 -0500 (EST) Received: (qmail 13445 invoked from network); 14 Feb 2003 18:41:10 -0000 Received: from biglumber.com (HELO localhost) (207.228.252.42) by 0 with SMTP; 14 Feb 2003 18:41:10 -0000 From: greg@turnstep.com To: pgsql-sql@postgresql.org Subject: Re: Drop temporary table only if it exists X-PGP-Key: 2529 DF6A B8F7 9407 E944 45B4 BC9B 9067 1496 4AC8 In-Reply-To: <200302140948.15889.josh@agliodbs.com> Date: Fri, 14 Feb 2003 18:41:10 -0000 X-Mailer: JoyMail 1.36 Message-ID: <784d32a2b3c0b617ad1f80c9d9c6f002@biglumber.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/343 X-Sequence-Number: 11741 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 > A quick check for existance: > > SELECT * FROM pg_class WHERE relname = 'name_of_temp_table' and relkind = ? > > You'll want to look in the docs about the PG system tables to see what > "relkind" you should test on for each type of object. If you are using 7.3, you also need to to consider the namespace: temporary relations live in their own schema. To find out if the temporary table (relkind='r') named "neptune" exists: SELECT COUNT(*) FROM pg_class WHERE LOWER(relname) = 'neptune' AND relkind = 'r' AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname ~ '^pg_temp'); To find out about a sequence (relkind='S') named "saturn" exists: SELECT COUNT(*) FROM pg_class WHERE LOWER(relname) = 'saturn' AND relkind = 'S' AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname ~ '^pg_temp'); To find out if "jupiter" is used by any temp relations: SELECT COUNT(*) FROM pg_class WHERE LOWER(relname) = 'jupiter' AND relnamespace IN (SELECT oid FROM pg_namespace WHERE nspname ~ '^pg_temp'); To use the above on a pre-7.3 system, just remove the last line that mentions relnamespace. - -- Greg Sabino Mullane greg@turnstep.com PGP Key: 0x14964AC8 200302141327 -----BEGIN PGP SIGNATURE----- Comment: http://www.turnstep.com/pgp.html iD8DBQE+TTdmvJuQZxSWSsgRAtVNAKDtWkTxxg++vqJ7asqMG54BDCzPiQCg3dBG SbKhIdttHz4fVV1SMeyGqXE= =+JMB -----END PGP SIGNATURE----- From JanWieck@Yahoo.com Tue Feb 18 17:40:04 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 9A365475AAC for ; Tue, 18 Feb 2003 12:40:04 -0500 (EST) Received: from smtp011.mail.yahoo.com (smtp011.mail.yahoo.com [216.136.173.31]) by postgresql.org (Postfix) with SMTP id E0170475A9E for ; Tue, 18 Feb 2003 12:40:03 -0500 (EST) Received: from psc.progress.com (HELO Yahoo.com) (janwieck@192.233.92.200 with plain) by smtp.mail.vip.sc5.yahoo.com with SMTP; 18 Feb 2003 17:40:09 -0000 Message-ID: <3E526F86.69391A03@Yahoo.com> Date: Tue, 18 Feb 2003 12:38:14 -0500 From: Jan Wieck Organization: Home X-Mailer: Mozilla 4.8 [en] (Windows NT 5.0; U) X-Accept-Language: en,ru MIME-Version: 1.0 To: Mintoo Lall Cc: pgsql-sql@postgresql.org Subject: Re: Drop temporary table only if it exists References: <20030214153413.24680.qmail@web14802.mail.yahoo.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/411 X-Sequence-Number: 11809 Mintoo Lall wrote: > > Hi Everybody, > > How do I drop a temporary table ( or for that matter a sequence) only > if it exists or create them if they donot exist ? I want to do this > to avoid any errors I wonder what type of application cannot remember if it created a temp object or not during the lifetime of it's database connection. Jan > > Any help is appreciated . > > Thanks, > > Tarun > > ---------------------------------------------------------------------- > Do you Yahoo!? > Yahoo! Shopping - Send Flowers for Valentine's Day -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # From josh@agliodbs.com Tue Feb 18 18:28:35 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 7520B475AD7 for ; Tue, 18 Feb 2003 13:28:35 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id B8F04475AA9 for ; Tue, 18 Feb 2003 13:28:34 -0500 (EST) Received: from [66.219.92.2] (HELO chocolate-mousse) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2845586; Tue, 18 Feb 2003 10:28:40 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Reply-To: josh@agliodbs.com Organization: Aglio Database Solutions To: Jan Wieck , Mintoo Lall Subject: Re: Drop temporary table only if it exists Date: Tue, 18 Feb 2003 10:28:01 -0800 X-Mailer: KMail [version 1.4] Cc: pgsql-sql@postgresql.org References: <20030214153413.24680.qmail@web14802.mail.yahoo.com> <3E526F86.69391A03@Yahoo.com> In-Reply-To: <3E526F86.69391A03@Yahoo.com> MIME-Version: 1.0 Content-Transfer-Encoding: quoted-printable Message-Id: <200302181028.01599.josh@agliodbs.com> X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/413 X-Sequence-Number: 11811 Jan, > > How do I drop a temporary table ( or for that matter a sequence) only > > if it exists or create them if they donot exist ? I want to do this > > to avoid any errors >=20 > I wonder what type of application cannot remember if it created a temp > object or not during the lifetime of it's database connection. A real one, Jan. This sort of test is particularly necessary for Exception handling routines= .=20=20=20 One wants the exception handler to drop all temporary obejcts for a clean= =20 slate, but since an exception has occurred, one cannot be sure which object= s=20 were created successfully. I've personally written several such routines.=20=20 --=20 -Josh Berkus Aglio Database Solutions San Francisco From JanWieck@Yahoo.com Tue Feb 18 20:49:44 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 0AFC5475AE4 for ; Tue, 18 Feb 2003 15:49:44 -0500 (EST) Received: from smtp013.mail.yahoo.com (smtp013.mail.yahoo.com [216.136.173.57]) by postgresql.org (Postfix) with SMTP id 3E790475ADE for ; Tue, 18 Feb 2003 15:49:43 -0500 (EST) Received: from psc.progress.com (HELO Yahoo.com) (janwieck@192.233.92.200 with plain) by smtp.mail.vip.sc5.yahoo.com with SMTP; 18 Feb 2003 20:49:44 -0000 Message-ID: <3E529BF0.D373AA9C@Yahoo.com> Date: Tue, 18 Feb 2003 15:47:44 -0500 From: Jan Wieck Organization: Home X-Mailer: Mozilla 4.8 [en] (Windows NT 5.0; U) X-Accept-Language: en,ru MIME-Version: 1.0 To: josh@agliodbs.com Cc: Mintoo Lall , pgsql-sql@postgresql.org Subject: Re: Drop temporary table only if it exists References: <20030214153413.24680.qmail@web14802.mail.yahoo.com> <3E526F86.69391A03@Yahoo.com> <200302181028.01599.josh@agliodbs.com> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/416 X-Sequence-Number: 11814 Josh Berkus wrote: > > Jan, > > > > How do I drop a temporary table ( or for that matter a sequence) only > > > if it exists or create them if they donot exist ? I want to do this > > > to avoid any errors > > > > I wonder what type of application cannot remember if it created a temp > > object or not during the lifetime of it's database connection. > > A real one, Jan. > > This sort of test is particularly necessary for Exception handling routines. > One wants the exception handler to drop all temporary obejcts for a clean > slate, but since an exception has occurred, one cannot be sure which objects > were created successfully. All temporary objects that need to persist across transactions should be created at session start. Everything else is created inside the transaction that needs it and is therefore automatically cleaned up. If there are gazillions of those session level temporary objects, I doubt that the database is really the right place to "cache" this sort of data. > > I've personally written several such routines. I never needed to. I might when I come around and create the next generation of PL that supports packages with package initialization functions and the like. Let's see ... Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # From josh@agliodbs.com Wed Feb 19 06:30:30 2003 X-Original-To: pgsql-sql@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E0E2247580B for ; Wed, 19 Feb 2003 01:30:30 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 4C05B475458 for ; Wed, 19 Feb 2003 01:30:30 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2846771; Tue, 18 Feb 2003 22:30:38 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: Jan Wieck Subject: Re: Drop temporary table only if it exists Date: Tue, 18 Feb 2003 22:29:25 -0800 User-Agent: KMail/1.4.3 Cc: Mintoo Lall , pgsql-sql@postgresql.org References: <20030214153413.24680.qmail@web14802.mail.yahoo.com> <200302181028.01599.josh@agliodbs.com> <3E529BF0.D373AA9C@Yahoo.com> In-Reply-To: <3E529BF0.D373AA9C@Yahoo.com> MIME-Version: 1.0 Message-Id: <200302182229.25872.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/419 X-Sequence-Number: 11817 Jan, > All temporary objects that need to persist across transactions should be > created at session start. Everything else is created inside the > transaction that needs it and is therefore automatically cleaned up. That's a good idea for Mintoo: open your temporary objects inside a=20 transaction. Then you can dismiss them by rolling back the transaction. > I never needed to. I might when I come around and create the next > generation of PL that supports packages with package initialization > functions and the like. Let's see ... Hey, drop a line by David Fetter. He's been making noises about overhaulin= g=20 PL/pgSQL. --=20 Josh Berkus Aglio Database Solutions San Francisco