Обсуждение: DROP TABLE IF EXISTS

I am wondering how to do this simple mysql task in postgres.  Any hints?

Thanks,
Sean

* Sean Davis <sdavis2@mail.nih.gov> [2004-10-21 12:26:47 -0400]:

> I am wondering how to do this simple mysql task in postgres.  Any
> hints?

This reply from Ron Johnson seems to suffice:

http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php

--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www.commandprompt.com/
PostgreSQL Replication & Support Services, (503) 667-4564

I'm not sure why this even matters. You do DROP TABLE on a table that
doesn't exist, all that will happen is that you will get an error back
but your program will continue on it's merry way anyways. The only thing
IF EXISTS would give you is the suppression of the error message.


Steven Klassen wrote:

> * Sean Davis <sdavis2@mail.nih.gov> [2004-10-21 12:26:47 -0400]:
>
>
>>I am wondering how to do this simple mysql task in postgres.  Any
>>hints?
>
>
> This reply from Ron Johnson seems to suffice:
>
> http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php
>

Point taken.  However, in a perl/DBI setting, the program does die unless I
explicitly trap the error, etc.  So, it is a convenience, true enough, but
not an issue that I had to deal with in MySQL, so just thought I would ask.

Sean


-----Original Message-----
From: William Yu
To: pgsql-novice@postgresql.org
Sent: 10/25/2004 2:05 PM
Subject: Re: [NOVICE] DROP TABLE IF EXISTS

I'm not sure why this even matters. You do DROP TABLE on a table that
doesn't exist, all that will happen is that you will get an error back
but your program will continue on it's merry way anyways. The only thing

IF EXISTS would give you is the suppression of the error message.


Steven Klassen wrote:

> * Sean Davis <sdavis2@mail.nih.gov> [2004-10-21 12:26:47 -0400]:
>
>
>>I am wondering how to do this simple mysql task in postgres.  Any
>>hints?
>
>
> This reply from Ron Johnson seems to suffice:
>
> http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php
>

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

That's a new one to me. I use perl/DBI also and my scripts just ignore
errors w/o any error trapping. Maybe my defaults are set to ignore
errors. I only trap errors when I there's a possibility of catastrophic
failure. (Example, DB is down -- SELECT * FROM zzz returns an error so
the row count is 0 -- page looks normal except there's no content versus
some generic apache error message.)




Davis, Sean (NIH/NHGRI) wrote:
> Point taken.  However, in a perl/DBI setting, the program does die unless I
> explicitly trap the error, etc.  So, it is a convenience, true enough, but
> not an issue that I had to deal with in MySQL, so just thought I would ask.

 --- William Yu <wyu@talisys.com> escribió:
> I'm not sure why this even matters. You do DROP
> TABLE on a table that
> doesn't exist, all that will happen is that you will
> get an error back
> but your program will continue on it's merry way
> anyways. The only thing
> IF EXISTS would give you is the suppression of the
> error message.
>
>
> Steven Klassen wrote:
>
> > * Sean Davis <sdavis2@mail.nih.gov> [2004-10-21
> 12:26:47 -0400]:
> >
> >
> >>I am wondering how to do this simple mysql task in
> postgres.  Any
> >>hints?
> >
> >
> > This reply from Ron Johnson seems to suffice:
> >
> >
>
http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php


what about the Ron Johnson solution?
 if exists (select 1 from pg_tables where tablename =
"thetable")
> drop table thetable

regards,
Jaime Casanova

_________________________________________________________
Do You Yahoo!?
Información de Estados Unidos y América Latina, en Yahoo! Noticias.
Visítanos en http://noticias.espanol.yahoo.com

Jaime,

Thanks for the reply.

On Oct 26, 2004, at 4:51 PM, Jaime Casanova wrote:
> http://archives.postgresql.org/pgsql-interfaces/2002-05/msg00102.php
>
>
> what about the Ron Johnson solution?
>  if exists (select 1 from pg_tables where tablename =
> "thetable")
>> drop table thetable
>>

Actually, Ron gave some other possibilities, but the query above does
NOT work (and was the original source of the question).  Just for
information, here is a function that I had come up with that works.  It
returns 1 or 0 just as a sanity check.

create or replace function drop_if_exists (text) returns INTEGER AS '
DECLARE
    tbl_name ALIAS FOR $1;
BEGIN
    IF (select count(*) from pg_tables where tablename=$1) THEN        EXECUTE
''DROP TABLE '' || $1;
        RETURN 1;
    END IF;
    RETURN 0;
END;
'
language 'plpgsql';

Sean