Dropping all foreign keys for a column in a table

[Andreas Joseph Krogh <andreak@xxxxxxxxxxxx>]

Here is a function for removing all FKs on a column (yes, PG for some 
reason allows multiple similar FKs on a column):

create or replace function remove_fk_by_table_and_column(p_table_name 
varchar, p_column_name varchar) returns INTEGER as $$
declare
    v_fk_name varchar := NULL;
    v_fk_num_removed INTEGER := 0;
begin
    FOR v_fk_name IN (SELECT ss2.conname
        FROM pg_attribute af, pg_attribute a,
            (SELECT conname, conrelid,confrelid,conkey[i] AS conkey, 
confkey[i] AS confkey
                FROM (SELECT conname, conrelid,confrelid,conkey,confkey,
                    generate_series(1,array_upper(conkey,1)) AS i
                    FROM pg_constraint WHERE contype = 'f') ss) ss2
        WHERE af.attnum = confkey
            AND af.attrelid = confrelid
            AND a.attnum = conkey
            AND a.attrelid = conrelid
            AND a.attrelid = p_table_name::regclass
            AND a.attname = p_column_name) LOOP
        execute 'alter table ' || quote_ident(p_table_name) || ' drop 
constraint ' || quote_ident(v_fk_name);
        v_fk_num_removed = v_fk_num_removed + 1;
    END LOOP;

    return v_fk_num_removed;

end;
$$ language plpgsql;

Usage:

select remove_fk_by_table_and_column('my_table', 'some_column');

I find myself often having to remove FK-constraints on a column because 
they are refactored to point to other columns or whatever, and I thought 
this might be useful to others.

--
Andreas Joseph Krogh<andreak@xxxxxxxxxxxx>  - mob: +47 909 56 963
Senior Software Developer / CEO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc



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