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