thanks, this will help me :)
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@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
