ALTER TABLE ADD CONSTRAINT failing when executed from function
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;