Thread

1. Dropping all foreign keys for a column in a table

   Andreas Joseph Krogh <andreak@officenet.no> — 2012-08-29T08:34:53Z

   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@officenet.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
   
   
   
   

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

   Bartosz Dmytrak <bdmytrak@gmail.com> — 2012-08-29T12:15:02Z

   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@officenet.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@officenet.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@postgresql.org)
   > To make changes to your subscription:
   > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general>
   >