Can't delete - Need cascading update instead

[Adam Tomjack <adam@xxxxxxxxxxxxxxxx>]

For various reasons, I can't actually delete records from my database. 
Instead, I have a boolean 'active' field for each table.  I need to 
implement something like cascading delete, but instead of deleting, I 
need to set active=false.

I've googled and haven't found a solution.  I had two ideas, neither of 
which worked out.

One thing I tried is to set ON DELETE CASCADE for all of my foreign key 
constraints.  Then I added a rule ON DELETE DO ALSO UPDATE ... and a 
BEFORE DELETE trigger to stop the actual deletion.  Unfortunately, that 
also stops the cascade.

My other idea involved an ON DELETE DO INSTEAD UPDATE ... rule and a 
BEFORE UPDATE PL/pgSQL trigger that manually implemented the cascading. 
 The problem with that is that the only way I can find to generate an 
approproate UPDATE or DELETE statement is to create a string and then 
EXECUTE it, but I need values from the NEW or OLD records, which 
apparently aren't usable from an EXECUTE statement.  I'll include my 
code at the end.

I haven't looked into using C.  If that's what it takes, it'll be faster 
for me to just do it client side.

Are there any other potential server-side solutions that I'm missing?

Thanks for reading,

Adam Tomjack


--------------

My failed update trigger:

CREATE OR REPLACE FUNCTION my_cascading_deleter()
  RETURNS "trigger" LANGUAGE 'plpgsql' VOLATILE AS
$BODY$
DECLARE
	r RECORD;
	r2 RECORD;
	r3 RECORD;
	i RECORD;
	sql TEXT;
BEGIN
IF NEW.active=false AND OLD.active=true THEN
  -- Loop over each table that references this one.
  FOR r IN SELECT child.relname AS child, child.oid AS childid,
                  parent.oid AS parentid, c.conkey AS childkey,
                  c.confkey AS parentkey FROM pg_constraint c
    JOIN pg_class child ON (child.oid=c.conrelid)
    JOIN pg_class parent ON (parent.oid=c.confrelid)
    WHERE contype='f' and parent.oid=TG_RELID
  LOOP
    sql := 'DELETE FROM '||r.child||' WHERE ';
    -- Loop over every column in the primary key
    FOR i IN 1 .. array_upper(r.childkey, 1) LOOP
      SELECT INTO r2 attname FROM pg_attribute WHERE attrelid=r.childid
                                         AND attnum=r.childkey[i];
      SELECT INTO r3 attname FROM pg_attribute WHERE attrelid=r.parentid
                                         AND attnum=r.parentkey[i];
      sql := sql || ' ' || r2.attname || '=OLD.' || r3.attname;
    END LOOP;
    EXECUTE sql; -- ERROR, doesn't understand the OLD record
  END LOOP;
END IF;
RETURN NEW;
END;
$BODY$
;


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

              http://archives.postgresql.org