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
>