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
>