pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: steve boyle <boylesa@dial.pipex.com>
To: pgsql-sql@postgresql.org
Subject: Re: Conditional test
Date: Mon, 7 Jan 2002 18:46:49 -0000
Message-ID: <a1cqfm$174q$1@news.tht.net> (raw)
References: <4eee2f8d.0112271854.276c1152@posting.google.com>

Rick, try the following plpgsql function it should do what you need:

create function f_dropTableIfExists (text) returns boolean as '
    declare
        p_tablename alias for $1;
        temp_name text;
    begin
        select tablename from pg_tables into temp_name where tablename =
p_tablename;
        if not found then
            raise notice ''Table % not found.'', p_tablename;
            return false;
        else
            execute ''drop table '' ||  p_tablename;
            raise notice ''Table % dropped.'', p_tablename;
            return true;
        end if;
    end ; '
language 'plpgsql';

you will need to 'Select f_dropTableIfExists([TableName]);' to execute the
function.

FAIK the main difference carrying out the operation this way rather than
just DROP TABLE  is to avoid an ERROR being raised if the object does not
exist.

Hih

Steve Boyle

PS. If anyone knows a better way of doing this pls post

"Rick Dayao" <rickd@open-works.com> wrote in message
news:4eee2f8d.0112271854.276c1152@posting.google.com...
> I am trying to solve this problem:
>
> If a table exists, drop the table, otherwise continue processing.
>
> For example, if table X exists, then drop table X.  How would I do
> this in SQL?  I can query the pg_tables table to see which tables
> exist, but I'm looking for an If-Else construct in SQL.
>
> Thanks in advance,
>
> Rick





view thread (3+ messages)

Message-ID: <a1cqfm$174q$1@news.tht.net>
Permalink:  ../a1cqfm$174q$1@news.tht.net/
Also on:    postgresql.org/message-id/a1cqfm$174q$1@news.tht.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: boylesa@dial.pipex.com
  Subject: Re: Conditional test
  In-Reply-To: <a1cqfm$174q$1@news.tht.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox