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

postgresql

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?

Best Answer

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