Re: A conditional DROP TABLE function

Hi Tino,

--- Tino Wildenhain <tino(at)wildenhain(dot)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