Eric Humphrey

Function I devised to allow recreating all foreign keys and switching if they cascade:

CREATE OR REPLACE FUNCTION util_recreate_fks (allow_cascade bool) RETURNS bool AS $$DECLARE    constraint_cursor CURSOR FOR        SELECT t.constraint_name, t.table_schema || '.' || t.table_name as table_name, --t.constraint_type,            c1.table_schema || '.' || c1.table_name as table_name_pk, c1.column_name as column_name_pk,            c2.table_name as table_name_fk, c2.column_name as column_name_fk        FROM information_schema.table_constraints t,            information_schema.constraint_column_usage c1,            information_schema.key_column_usage c2        WHERE t.constraint_name = c1.constraint_name            AND t.constraint_name = c2.constraint_name            AND t.constraint_type = 'FOREIGN KEY';    _constraint RECORD;    cascade_setting VARCHAR;BEGIN    IF (allow_cascade) THEN        cascade_setting := 'CASCADE';    ELSE        cascade_setting := 'NO ACTION';    END IF;

    OPEN constraint_cursor;

    LOOP        FETCH constraint_cursor INTO _constraint;        EXIT WHEN NOT FOUND;        EXECUTE 'ALTER TABLE ' || _constraint.table_name || ' DROP CONSTRAINT ' || _constraint.constraint_name;        EXECUTE '            ALTER TABLE ' || _constraint.table_name || '                ADD CONSTRAINT ' || _constraint.constraint_name || ' FOREIGN KEY (' || _constraint.column_name_fk || ')                    REFERENCES ' || _constraint.table_name_pk || '(' || _constraint.column_name_pk || ')                    ON UPDATE ' || cascade_setting || '                    ON DELETE ' || cascade_setting || '        ';    END LOOP;

    CLOSE constraint_cursor;

    RETURN allow_cascade;END;$$ LANGUAGE 'plpgsql';