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$;