From: | David Link <dvlink(at)yahoo(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | A conditional DROP TABLE function |
Date: | 2003-09-25 20:36:49 |
Message-ID: | 20030925203649.90118.qmail@web13503.mail.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi All,
Here's a Conditional drop_table func for those interested. There was a
thread on this a long time back.
We do this all the time :
DELETE TABLE sales;
CREATE TABLE sales (...);
But nobody likes
ERROR: table "sales" does not exist
which we see all the time in the logs. I want to show the logs to none
db folk -- so we can't have those error messages in it.
(There must be some explaination why postgresql (and Oracle as well) do
not have CREATE OR REPLACE TABLE as it does for VIEWs, and FUNCTIONs.
Anybody know?)
Anyway here's drop_table ():
CREATE or REPLACE function drop_table (varchar) returns varchar as '
DECLARE
tablename alias for $1;
cnt int4;
BEGIN
SELECT into cnt count(*) from pg_class where relname =
tablename::name;
if cnt > 0 then
execute \'DROP TABLE \' || tablename;
return tablename || \' DROPPED\';
end if;
return tablename || \' does not exist\';
END;'
language 'plpgsql' ;
And here's it's usage in an SQL script:
\set QUIET
\pset format unaligned
\pset tuples_only
\unset QUIET
select drop_table('sale');
CREATE TABLE sale ( ... );
Regards, DAvid
__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
From | Date | Subject | |
---|---|---|---|
Next Message | Jonathan Bartlett | 2003-09-25 21:08:45 | Re: career in SQL/Database administration |
Previous Message | Yevgeny Ioffe | 2003-09-25 20:20:18 | MySQL-to-PostgreSQL |