0

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?

  • 1
    You say you don't want cascade, but "deleting the row referencing a foreign key" is exactly what cascade is. So if you want cascade, but don't want it to be called 'cascade', then I think you need to explain in more detail. Sounds like you want one case to be on delete cascade and the other to be on delete set null. – jjanes Sep 19 '18 at 20:12
  • 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. – whoasked Sep 20 '18 at 9:08
  • 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) – jjanes Sep 20 '18 at 13:41
  • @jjanes fwiw I kind of got there (see answer), but it only cascades one level – whoasked Sep 20 '18 at 14:16
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.