Re: Dropping all foreign keys for a column in a table

Hi,
thanks, this will help me :)

Maybe one small hint:
You use only table name variable (p_table_name) which I assume should
contain schema name. If so then quote_ident ('aaA.bbbB') will give You
"aaA.bbbB" but not "aaA"."bbbB". This will produce error. It is better
idea, in my oppinion, to add p_schema_name variable to function parameters
or table OID as p_table_name, and then get table and schema name (fully
qualified) from casting oid to regclass:

e.g.
SELECT 'pg_class'::regclass::oid
gives me: 1259
and
SELECT 1259::regclass
gives me: pg_class

You can try this with any table and second casting will give You fully
qualified name besed on provided OID.

Regards,
Bartek

2012/8/29 Andreas Joseph Krogh <andreak(at)officenet(dot)no>

> 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(at)officenet(dot)no> - 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<http://home.officenet.no/~andreak/public_key.asc>
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
>