pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedConditional test
3+ messages / 3 participants
[nested] [flat]
* Conditional test
@ 2001-12-28 02:54 Rick Dayao <rickd@open-works.com>
2002-01-03 17:04 ` Re: Conditional test Bruce Momjian <pgman@candle.pha.pa.us>
2002-01-07 18:46 ` Re: Conditional test steve boyle <boylesa@dial.pipex.com>
0 siblings, 2 replies; 3+ messages in thread
From: rickd@open-works.com @ 2001-12-28 02:54 UTC (permalink / raw)
To: pgsql-sql
I am trying to solve this problem:
If a table exists, drop the table, otherwise continue processing.
For example, if table X exists, then drop table X. How would I do
this in SQL? I can query the pg_tables table to see which tables
exist, but I'm looking for an If-Else construct in SQL.
Thanks in advance,
Rick
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Conditional test
2001-12-28 02:54 Conditional test Rick Dayao <rickd@open-works.com>
@ 2002-01-03 17:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Bruce Momjian @ 2002-01-03 17:04 UTC (permalink / raw)
To: Rick Dayao <rickd@open-works.com>; +Cc: pgsql-sql
Rick Dayao wrote:
> I am trying to solve this problem:
>
> If a table exists, drop the table, otherwise continue processing.
>
> For example, if table X exists, then drop table X. How would I do
> this in SQL? I can query the pg_tables table to see which tables
> exist, but I'm looking for an If-Else construct in SQL.
You can drop the table and ignore the error if returned. We will have
CREATE OR REPLACE in 7.2, but no conditional DROP.
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Conditional test
2001-12-28 02:54 Conditional test Rick Dayao <rickd@open-works.com>
@ 2002-01-07 18:46 ` steve boyle <boylesa@dial.pipex.com>
1 sibling, 0 replies; 3+ messages in thread
From: steve boyle @ 2002-01-07 18:46 UTC (permalink / raw)
To: pgsql-sql
Rick, try the following plpgsql function it should do what you need:
create function f_dropTableIfExists (text) returns boolean as '
declare
p_tablename alias for $1;
temp_name text;
begin
select tablename from pg_tables into temp_name where tablename =
p_tablename;
if not found then
raise notice ''Table % not found.'', p_tablename;
return false;
else
execute ''drop table '' || p_tablename;
raise notice ''Table % dropped.'', p_tablename;
return true;
end if;
end ; '
language 'plpgsql';
you will need to 'Select f_dropTableIfExists([TableName]);' to execute the
function.
FAIK the main difference carrying out the operation this way rather than
just DROP TABLE is to avoid an ERROR being raised if the object does not
exist.
Hih
Steve Boyle
PS. If anyone knows a better way of doing this pls post
"Rick Dayao" <rickd@open-works.com> wrote in message
news:4eee2f8d.0112271854.276c1152@posting.google.com...
> I am trying to solve this problem:
>
> If a table exists, drop the table, otherwise continue processing.
>
> For example, if table X exists, then drop table X. How would I do
> this in SQL? I can query the pg_tables table to see which tables
> exist, but I'm looking for an If-Else construct in SQL.
>
> Thanks in advance,
>
> Rick
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-01-07 18:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-12-28 02:54 Conditional test Rick Dayao <rickd@open-works.com>
2002-01-03 17:04 ` Bruce Momjian <pgman@candle.pha.pa.us>
2002-01-07 18:46 ` steve boyle <boylesa@dial.pipex.com>
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