From carolyn@kss.net.au Wed Feb 23 06:33:13 2000 Received: from kss.net.au ([203.41.122.41]) by hub.org (8.9.3/8.9.3) with ESMTP id BAA03753 for ; Wed, 23 Feb 2000 01:33:13 -0500 (EST) (envelope-from carolyn@kss.net.au) Received: from kss.net.au (coco [10.10.1.9]) by kss.net.au (8.8.7/8.8.7) with ESMTP id EAA10439 for ; Thu, 24 Feb 2000 04:36:15 +1100 Message-ID: <38B37F13.C1D2D6F4@kss.net.au> Date: Wed, 23 Feb 2000 17:32:51 +1100 From: Carolyn Wong Organization: Kwiker Software Solutions X-Mailer: Mozilla 4.7 [en] (WinNT; I) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: SQL Script Question Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I'm trying to write SQL script to create tables as follows: -------------------------------- drop table xxx; create table xxx ( ...... ); grant all on xxx to public; -------------------------------- Before dropping the table, I'd like to check if the table exists as the following psuedo code: if drop table xxx; How can i write this 'if' condition? Or are there any other ways to check this?? Thanks in advance. From andrzej.mazurkiewicz@polkomtel.com.pl Wed Feb 23 10:10:12 2000 Received: from imcwaw3.plusnet (imcwaw3.polkomtel.com.pl [212.2.96.75]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA30963 for ; Wed, 23 Feb 2000 05:10:12 -0500 (EST) (envelope-from andrzej.mazurkiewicz@polkomtel.com.pl) Received: by imcwaw3.plusnet with Internet Mail Service (5.5.2448.0) id <1JFGGJZ2>; Wed, 23 Feb 2000 11:09:29 +0100 Message-ID: <13288F4408ADD11186FF0060B06A431303648C5C@MSGWAW1> From: Andrzej Mazurkiewicz To: "'Carolyn Wong'" Cc: pgsql-sql@postgresql.org Subject: RE: [SQL] SQL Script Question Date: Wed, 23 Feb 2000 11:06:13 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2448.0) Content-Type: text/plain Hello. You can utilize quick and dirty approach, i. e. BEGIN WORK; DROP TABLE ... COMMIT WORK; If table is not present it will not drop it but you will have consistent state of transactions. The other solution is to use pgtclsh or perl with Pg library. As far as I know psql gives no control constructs (IF, CASE, WHILE ...). Regards, Andrzej Mazurkiewicz www.mazurkiewicz.org > -----Original Message----- > From: Carolyn Wong [SMTP:carolyn@kss.net.au] > Sent: 23 lutego 2000 07:33 > To: pgsql-sql@postgresql.org > Subject: [SQL] SQL Script Question > > I'm trying to write SQL script to create tables as follows: > > -------------------------------- > drop table xxx; > create table xxx ( > ...... > ); > grant all on xxx to public; > -------------------------------- > > Before dropping the table, I'd like to check if the table exists as the > following psuedo code: > > if
> drop table xxx; > > How can i write this 'if' condition? Or are there any other ways to > check this?? > > Thanks in advance. > > ************ From tip@pc10.radnoti-szeged.sulinet.hu Wed Feb 23 12:38:18 2000 Received: from pc10.radnoti-szeged.sulinet.hu (root@pc10.radnoti-szeged.sulinet.hu [195.199.49.170]) by hub.org (8.9.3/8.9.3) with ESMTP id HAA39487 for ; Wed, 23 Feb 2000 07:38:18 -0500 (EST) (envelope-from tip@pc10.radnoti-szeged.sulinet.hu) Received: from localhost (tip@localhost) by pc10.radnoti-szeged.sulinet.hu (8.8.8/8.8.8) with ESMTP id NAA15102; Wed, 23 Feb 2000 13:24:34 +0100 Date: Wed, 23 Feb 2000 13:24:34 +0100 (CET) From: Kovacs Zoltan Sandor To: Carolyn Wong cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] SQL Script Question In-Reply-To: <38B37F13.C1D2D6F4@kss.net.au> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII > if
> drop table xxx; > > How can i write this 'if' condition? Or are there any other ways to > check this?? This SELECT tells you whether a table exists or not: SELECT tablename FROM pg_tables WHERE tablename='searched_table_name'; With this you can check the condition. Then you may write a PLPGSQL function which can decide if there was any found table with searched_table_name or not, see the PLPGSQL example in the src/plpgsql library. Then you may call an SQL function (you should write it first) from the PLPGSQL function which drops table. As I know, you cannot drop a table inside a PLPGSQL function (only if you call an external SQL function). I hope this helps. If not, ask for details. Regards, Zoltan