So I have 2 functions that I am writing, one to drop all foreign constraints for a schema, and the other will recreate these foreign constraints as they once were.
The drop constraints function works by selecting various "deferrable constraint levels" into respective temp tables (e.g. temp tables for: fully_deferrable, not_deferrable, semi_deferrable) to make sure that information exists for the subsequent recreate constraints function. I am running into an error though that will not allow me to create the constraint because this constraint already exists. So the recreate constraints function will error out with something like: " ERROR: constraint "avg_test_k_school_fkey" for relation "avg_test" already exists", even though I have already dropped this foreign constraint via the previous function.
I have checked the tables to make sure all the foreign constraints are dropped, and when I run the ALTER TABLE statement outside of the function, the foreign constraint is added with no problem. So I am wondering what in the world is going on with executing this from the function. I am thinking that perhaps I am doing something wrong control-flow wise, or that these operations are not possible to call from functions. Please let me know if you have any questions!
This function is for the recreation of foreign keys. not_deferrable_constraints is a temp table that has the child table, parent table, whether or not a constraint is deferred, the fields of the primary/foreign keys, and the name of the constraint.
Here is the SQL:
CREATE OR REPLACE FUNCTION test.recreate_all_foreign_constraints(schema_name name) RETURNS void LANGUAGE plpgsql AS $function$ declare _tbl record; add_constraint record; begin --Recreate not_deferrable_constraints for _tbl in(select child_table from not_deferrable_constraints) loop for add_constraint in(select constraint_name, fk_column, pk_column, parent_table from not_deferrable_constraints where child_table = _tbl.child_table) loop execute 'ALTER TABLE ' ||schema_name||'.'|| _tbl.child_table || ' ADD CONSTRAINT ' || add_constraint.constraint_name || ' FOREIGN KEY ('|| add_constraint.fk_column ||') REFERENCES '|| add_constraint.parent_table ||' ('|| add_constraint.pk_column ||')'; end loop; end loop; end; $function$ ;
It would be super cool if this worked! I'm so close I can feel it!! Thanks for your help!