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

[Lyle]

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general