Thread: A conditional DROP TABLE function

A conditional DROP TABLE function

From
David Link
Date:
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

Re: A conditional DROP TABLE function

From
Peter Childs
Date:
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
>


Re: A conditional DROP TABLE function

From
Tino Wildenhain
Date:
Hi David,

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 (...);
>
Hm. "all the time" enables all the warning lights -
what are you doing to have to delete and create
the tables every time?

> 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.

grep -v "ERROR" should do 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?)

Nobody needs this?
There is:

1) delete from table;
2) truncate table;

to remove all the data

3) alter table ...

to change tables layout.

HTH
Tino Wildenhain


Re: A conditional DROP TABLE function

From
David Link
Date:
Hi Tino,

--- Tino Wildenhain <tino@wildenhain.de> wrote:
> 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 (...);
> >
> Hm. "all the time" enables all the warning lights -
> what are you doing to have to delete and create
> the tables every time?

By 'all the time' i mean once a week. (incidently, it is DROP and not
DELETE table, of course).

We are working with weekly loads of data.  because of the volumns i'm
using a separate sales table per week, (ie, sale_200301, sale_200302,
etc), becuase when it was in one big happy table (ie, sale) it is
slower  -- especially recreating indexes.

Now you can see, to make the weekly data load process rerunnable (and
we do rerun it often) i drop and [re]create this weeks collection of
sales related tables.  The logs contain statistics, warnings and
errors, which we share with the non-geeks who use the data.

Also, standard procedure (I believe) for maintaining a data model is
creating and using database creation scripts (with DROP and CREATE) -
so every time you set up a bran new system, you can get those (i
believe) unnecessary messages: ERROR table does not exist.

-Thanks.

>
> > 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.
>
> grep -v "ERROR" should do it.

Yes, but then you've got to wrap things in a log filter to generate
reports rather than just using the processing log as it comes out.

>
> >
> > (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?)
>
> Nobody needs this?
> There is:
>
> 1) delete from table;
> 2) truncate table;
>
> to remove all the data
>
> 3) alter table ...
>
> to change tables layout.

these do not create the table should it not yet exist.
and i needed it. -- so i wrote the simple stored procedure to make
meself happy.  i noticed others have asked for a solution to the
problem as well.  (are you being a stickler?)

also in the name of consistency, CREATE OR REPLACE exist for stored
procedures (and views?).

PS: I LOVE Postegres.  It has made my life (as an Oracle DBA) charming
rather than hell!  Thanks.

>
> HTH
> Tino Wildenhain
>


__________________________________
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com