Re: drop table if exists - Mailing list pgsql-novice

Kevin Coyner wrote:
>>Is there an equivalent to "IF EXISTS" in Postgres?
>

No, there is no direct equivalent (and I have often wished there was --
but not enough to try to implement it, at least not yet).

You can fake it with a plpgsql function (very lightly tested):

CREATE OR REPLACE FUNCTION drop_table_if_exists(text, bool) RETURNS bool
AS '
DECLARE
   opt text;
   rec record;
BEGIN
   IF $2 THEN
     opt := '' CASCADE'';
   ELSE
     opt := '''';
   END IF;

   SELECT INTO rec oid FROM pg_class WHERE relname = $1::name;

   IF FOUND THEN
     EXECUTE ''DROP TABLE '' || $1 || opt;
     RETURN true;
   END IF;

   RETURN false;
END;
' LANGUAGE 'plpgsql';

regression=# SELECT drop_table_if_exists('foo', false);
  drop_table_if_exists
----------------------
  t
(1 row)

regression=# SELECT drop_table_if_exists('foo', false);
  drop_table_if_exists
----------------------
  f
(1 row)

HTH,

Joe