Re: A conditional DROP TABLE function

On Thu, 25 Sep 2003, David Link wrote:

> 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

Fine why not

BEGIN;
DELETE TABLE sales;
CREATE TABLE sales (...);
COMMIT;

This is not the same as create or replace is mysql as it will
delete all the data!
This is also the same as

DELETE FROM sales;

The advantage of this is you keep the indexes.

Peter Childs

>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>