Re: DROP CONSTRAINT IF EXISTS - simulating in 7 and 8?

On 25/07/2010 04:03, Lyle wrote:
> Hi,
> I really like the new:-
> ALTER TABLE *table* DROP CONSTRAINT IF EXISTS *contraint*
> But I need to achieve the same thing on earlier versions. I've tried
> googling with no luck, how do I do it?

I've created functions to achieve this for INDEXes and CONSTRAINTs:-

CREATE OR REPLACE FUNCTION DropIndex(tblSchema VARCHAR, tblName VARCHAR,
ndxName VARCHAR, OUT prod int) AS $$
DECLARE
exec_string TEXT;
BEGIN
exec_string := 'ALTER TABLE ';
IF tblSchema != NULL THEN
exec_string := exec_string || quote_ident(tblSchema) || '.';
END IF;
exec_string := exec_string || quote_ident(tblName)
|| ' DROP INDEX '
|| quote_ident(ndxName);
EXECUTE exec_string;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION DropConstraint(tblSchema VARCHAR, tblName
VARCHAR, cstName VARCHAR) RETURNS void AS $$
DECLARE
exec_string TEXT;
BEGIN
exec_string := 'ALTER TABLE ';
IF tblSchema != NULL THEN
exec_string := exec_string || quote_ident(tblSchema) || '.';
END IF;
exec_string := exec_string || quote_ident(tblName)
|| ' DROP CONSTRAINT '
|| quote_ident(cstName);
EXECUTE exec_string;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
$$ LANGUAGE plpgsql;

Maybe I should not user OTHERS... or not exceptions at all, and instead
to a select to see if the index/constraint exists? At least this works :)

Lyle