Re: drop table if exists

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