ALTER TABLE ADD CONSTRAINT failing when executed from function

Question

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.

• mustaccio: Do you happen to call both functions -- the one that drops constraints and the one that adds them back -- in the same transaction?
• Eshara Mondal: I do not currently. As I'm developing, I am running them in separate transactions. Which makes this more curious. In the future I foresee reworking this workflow a bit.
• Eshara Mondal: This did it!! What the heck. haha. Thank you! Is there a way to mark your answer as the answer?
• ypercubeᵀᴹ: Yes, there is ;)

Answer 1

I think you need:

for _tbl in(select DISTINCT child_table from not_deferrable_constraints) loop 

If a table has more than one constraints, your code will try to recreate each one of them many times.

Or just use a single for loop, I don't think you need the nesting.

--Recreate not_deferrable_constraints
for add_constraint in (select constraint_name, fk_column, pk_column, parent_table, child_table 
 from not_deferrable_constraints) loop
 execute 'ALTER TABLE ' ||schema_name||'.'|| add_constraint.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;

• ypercubeᵀᴹ: Also note that FK constraints can be devised from many columns. Your code wouldn't work with those.
• Eshara Mondal: Are you talking about multi-column FK constraints? Or multiple FK constraints on one table?
• ypercubeᵀᴹ: Multi-column FKs
• Eshara Mondal: Ah yes, definitely wouldn't. Thanks for looking out!