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.
I'd be tempted to add triggers to the delete to copy old versions of the data to a set of archive tables.
Alternatively, if you made "active" part of the primary and foreign-keys on the tables concerned you could cascade updates.
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.
sql := sql || ' ' || r2.attname || '=OLD.' || r3.attname;
You can't refer to the OLD.xxx or NEW.xxx in the query-string itself, you need to add its value. Of course, that causes problems because you can't dynamically refer to OLD[r3.attname] or whatever syntax you'd be tempted by.
TCL or one of the other dynamic languages is better for this. I've attached a sample of some code and history tables that do something similar to what you're trying to do. I don't make any great claims for my TCL coding skills - most of it was pieced together from tutorials.
HTH -- Richard Huxton Archonet Ltd
-- History Tracking Trigger-Functions
--
CREATE TABLE history (
hid SERIAL UNIQUE NOT NULL,
cid int4 NOT NULL CONSTRAINT valid_client REFERENCES client ON DELETE CASCADE,
ts timestamp(0) with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
uid int4 NOT NULL CONSTRAINT valid_uid REFERENCES user_info DEFAULT app_session_int_vol('UID'),
tbl varchar(32) NOT NULL,
act char(1) NOT NULL CONSTRAINT valid_act CHECK (act IN ('U','I','D')),
PRIMARY KEY (hid)
);
CREATE TABLE history_detail (
hid integer NOT NULL CONSTRAINT valid_hid REFERENCES client_history,
col varchar(32) NOT NULL,
was text,
PRIMARY KEY (hid,col)
);
-- tcl_track_history(TABLE-NAME)
-- Set TABLE-NAME when creating the trigger. Will automatically record change
-- details in tables history/history_detail
--
CREATE OR REPLACE FUNCTION tcl_track_history() RETURNS trigger AS '
switch $TG_op {
DELETE {
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'D\')"
}
INSERT {
if { [llength [array names NEW cid]] > 0 } {
set clival $NEW(cid)
} else {
set clival "NULL"
}
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'I\')"
}
UPDATE {
if { [llength [array names OLD cid]] > 0 } {
set clival $OLD(cid)
} else {
set clival "NULL"
}
set inserted_main_history_row false
foreach {col} $TG_relatts {
# First result seems to be an empty string when stepping through columns
if { $col > "" } {
# Check if OLD/NEW contain a value
if { [llength [array names OLD $col]] > 0 } {
set oldval $OLD($col)
} else {
set oldval "NULL"
}
if { [llength [array names NEW $col]] > 0 } {
set newval $NEW($col)
} else {
set newval "NULL"
}
if { $oldval != $newval } {
if { !$inserted_main_history_row } {
spi_exec "INSERT INTO history (cid,tbl,act) VALUES ($clival, \'$1\', \'U\')"
set inserted_main_history_row true
}
spi_exec "INSERT INTO history_detail (col,was) VALUES (\'$col\', \'[ quote $oldval ]\')"
}
}
}
}
}
return OK
' LANGUAGE pltcl;
CREATE TRIGGER history_trig AFTER INSERT OR UPDATE OR DELETE ON client FOR EACH ROW EXECUTE PROCEDURE tcl_track_history('client');
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
