472,353 Members | 1,407 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

A conditional DROP TABLE function

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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #1
3 10828
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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 12 '05 #2
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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #3
Hi Tino,

--- Tino Wildenhain <ti**@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

10
by: Ilik | last post by:
Hi all I'm trying to create a summery table of rain data that holds a record for every 0.1mm of rain in the following format: Station Name,...
3
by: Jouke Langhout | last post by:
Hello all! For quite some time now, I've got the following problem: Access won't close properly when a user closes the application. An ACCESS...
3
by: B Love | last post by:
I would like to display a drop-down list on a form conditional on the value of another field on the same form. I am not sure if this is good form or...
1
by: Dragan Matic | last post by:
if exists (select * from sysobjects where id = object_id(N'.') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table . GO For instance, this...
2
by: tpaulson | last post by:
I have a couple of DIV's that I hide/display based on radio buttons. On the DIV's, I have multiple drop down boxes. The source shows that they are...
5
by: paulo | last post by:
Can anyone please tell me how the C language interprets the following code: #include <stdio.h> int main(void) { int a = 1; int b = 10; int...
3
by: msrviking | last post by:
Hello everybody, After several attempts of writing the query, I had to post my requirement in the forum. Here is what I have, what I need and...
5
by: Romulo NF | last post by:
Greetings, Im back here to show the new version of the drag & drop table columns (original script ). Ive found some issues with the old script,...
1
by: colemanj4 | last post by:
Hello, I have a form with 2 drop downs. In the first drop down you choose a product and the second you choose the process step. Now as an...
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand....
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.