Postgres - Selectively either null or delete foreign key rows (without cascade)

Question

I understand this can easily be achived with cascade behaviour, but cascade isn't desirable in this case.

Is there a way of automatically either nulling the column or deleting the row referencing a foreign key depending on whether or not the column is nullable in the referencing table?

Say we have the following tables:

 Table "public.a"
 Column | Type | Modifiers
----------------------------+---------+--------------------
 pk | integer | not null
 Table "public.b"
 Column | Type | Modifiers
----------------------------+---------+--------------------
 fk | integer | not null
"b_a_fk_fkey" FOREIGN KEY (fk) REFERENCES a(pk)
 Table "public.c"
 Column | Type | Modifiers
----------------------------+---------+--------------------
 fk | integer | 
"c_a_fk_fkey" FOREIGN KEY (fk) REFERENCES a(pk)

We can get tables referencing a table with queries such as this. Does anyone have an example of how to generically delete rows in table 'b' and null columns in table 'c' referencing a given value for a.pk?

• whoasked: I appreciate this is essentially cascade behaviour, it's just there's a presumption against cascading deletes for the sake of safety - most throughout this database are "on delete restrict". Very little is deleted in the general course of things.
• jjanes: OK, I think I see. You want to choose the CASCADE or SET NULL behavior on a statement by statement basis for special maintenance operations, without having it be the default for other statements involving the constraint? That would be nice to have, but alas I don't have an answer for you on how to do it easily. (I'd be happy if I could even alter constraints to change the behavior without have to drop and re-create and re-validate them)
• whoasked: @jjanes fwiw I kind of got there (see answer), but it only cascades one level

Answer 1

EDIT: Recursive version. Lots of debug but peace of mind is nice when you're running queries like this. I'm sure this is by no means perfect, it assumes the PKs are ints for one, but it does the job for me:

create or replace function delete_cascade(table_name text, column_name text, column_value int) returns void 
language plpgsql 
as 
$func$ 
 declare 
 sql_temp text := ''; 
 rec record;
 recur record;
 recur2 record;
 begin 
 raise notice 'enter';
 sql_temp := format('' ||
 'with fkey as ' ||
 '( ' ||
 ' select pg_class.oid, pg_attribute.attnum ' ||
 ' from pg_attribute ' ||
 ' inner join pg_class on (pg_class.oid = pg_attribute.attrelid) ' ||
 ' where pg_class.relname = ''%s'' and pg_attribute.attname = ''%s'' ' ||
 ') ' ||
 'select pg_class.relname, pg_attribute.attname, pg_attribute.attnotnull ' ||
 'from pg_constraint ' ||
 'inner join pg_attribute on (pg_attribute.attrelid = pg_constraint.conrelid) ' ||
 'inner join pg_class on (pg_class.oid = pg_attribute.attrelid) ' ||
 'inner join fkey on (fkey.oid = pg_constraint.confrelid and (array_position(pg_constraint.confkey, fkey.attnum) > 0)) ' ||
 'where array_position(pg_constraint.conkey, pg_attribute.attnum) > 0',
 table_name, column_name); 
 for rec in execute sql_temp
 loop 
 sql_temp := format('select pg_attribute.attname from pg_index inner join pg_attribute on ((pg_attribute.attrelid = pg_index.indrelid) and (pg_attribute.attnum = any(pg_index.indkey))) where (pg_index.indrelid = ''%s''::regclass) and pg_index.indisprimary', rec.relname); 
 for recur in execute sql_temp 
 loop 
 sql_temp := format('select %s as col_val from %s where %s = 1ドル', recur.attname, rec.relname, rec.attname);
 raise notice '%', sql_temp; 
 for recur2 in execute sql_temp using column_value 
 loop 
 sql_temp := format('select delete_cascade(''%s'', ''%s'', %s)', rec.relname, recur.attname, recur2.col_val); 
 raise notice '%', sql_temp; 
 execute sql_temp; 
 end loop; 
 end loop; 
 if rec.attnotnull = true then 
 sql_temp := format('delete from %s where %s = %s', rec.relname, rec.attname, column_value); 
 raise notice '%', sql_temp; 
 execute sql_temp; 
 else 
 sql_temp := format('update %s set %s = null where %s = %s', rec.relname, rec.attname, rec.attname, column_value);
 raise notice '%', sql_temp; 
 execute sql_temp; 
 end if; 
 end loop; 
 sql_temp := format('delete from %s where %s = %s', table_name, column_name, column_value); 
 raise notice '%', sql_temp; 
 execute sql_temp; 
 raise notice 'leave';
 end 
$func$; 

Original, more readable but "depth = 1" answer:

In the unlikely event anyone wants to follow in my footsteps here, this is what I ended up with. It only "cascades" one level, but as it happens that's good enough for me for the task in hand. I'll leave recursive cascades to the interested reader ;-)

create or replace function delete_cascade(table_name text, column_name text, column_value int) returns void 
language plpgsql 
as 
$func$ 
 declare 
 cur cursor for 
 with fkey as 
 ( 
 select pg_class.oid, pg_attribute.attnum 
 from pg_attribute 
 inner join pg_class on (pg_class.oid = pg_attribute.attrelid) 
 where pg_class.relname = table_name and pg_attribute.attname = column_name 
 ) 
 select pg_class.relname, pg_attribute.attname, pg_attribute.attnotnull 
 from pg_constraint 
 inner join pg_attribute on (pg_attribute.attrelid = pg_constraint.conrelid) 
 inner join pg_class on (pg_class.oid = pg_attribute.attrelid) 
 inner join fkey on (fkey.oid = pg_constraint.confrelid and (array_position(pg_constraint.confkey, fkey.attnum) > 0)) 
 where array_position(pg_constraint.conkey, pg_attribute.attnum) > 0; 
 rec record; 
 begin 
 for rec in cur loop 
 if rec.attnotnull = true then 
 execute format('delete from %s where %s = %s', rec.relname, rec.attname, column_value); 
 else 
 execute format('update %s set %s = null where %s = %s', rec.relname, rec.attname, rec.attname, column_value); 
 end if; 
 end loop; 
 execute format('delete from %s where %s = %s', table_name, column_name, column_value); 
 end 
$func$;