pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL Script Question
3+ messages / 3 participants
[nested] [flat]
* SQL Script Question
@ 2000-02-23 06:32 Carolyn Wong <carolyn@kss.net.au>
2000-02-23 12:24 ` Re: [SQL] SQL Script Question Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 1 reply; 3+ messages in thread
From: Carolyn Wong @ 2000-02-23 06:32 UTC (permalink / raw)
To: pgsql-sql
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 <table exists>
drop table xxx;
How can i write this 'if' condition? Or are there any other ways to
check this??
Thanks in advance.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [SQL] SQL Script Question
2000-02-23 06:32 SQL Script Question Carolyn Wong <carolyn@kss.net.au>
@ 2000-02-23 12:24 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
0 siblings, 0 replies; 3+ messages in thread
From: Kovacs Zoltan Sandor @ 2000-02-23 12:24 UTC (permalink / raw)
To: Carolyn Wong <carolyn@kss.net.au>; +Cc: pgsql-sql
> if <table exists>
> 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
^ permalink raw reply [nested|flat] 3+ messages in thread
* RE: [SQL] SQL Script Question
@ 2000-02-23 10:06 Andrzej Mazurkiewicz <andrzej.mazurkiewicz@polkomtel.com.pl>
0 siblings, 0 replies; 3+ messages in thread
From: Andrzej Mazurkiewicz @ 2000-02-23 10:06 UTC (permalink / raw)
To: 'Carolyn Wong' <carolyn@kss.net.au>; +Cc: pgsql-sql
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 <table exists>
> drop table xxx;
>
> How can i write this 'if' condition? Or are there any other ways to
> check this??
>
> Thanks in advance.
>
> ************
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2000-02-23 12:24 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-02-23 06:32 SQL Script Question Carolyn Wong <carolyn@kss.net.au>
2000-02-23 12:24 ` Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
2000-02-23 10:06 RE: [SQL] SQL Script Question Andrzej Mazurkiewicz <andrzej.mazurkiewicz@polkomtel.com.pl>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox