In our DB schema we have defined a class of tables containing important
data for which we'd like to keep an audit trail of any change. These
tables have the following inheritance structure:
+----> <table> (real, live table with constraints)
<table>_type | +----> <table>_archive (archive without any constraints)
The parent <table>_type contains no data, is only used to define the
columns common to <table> and <table>_archive.
On each UPDATE or DELETE to any <table> we would like to record the
modified/deleted row as is in the <table>_archive.
Here is the trigger function that I'm working on:
create function archive_row() returns opaque as 'DECLARE rec RECORD; /* initialise future query string */
atttext := ''INSERT INTO '';BEGIN /* prepare the query, converting <table> to <table>_archive */ att := att
||TG_RELNAME || ''_archive VALUES (''; /* get all column names for trigger <table> through PG system tables */
FOR rec IN SELECT a.attname FROM pg_class c, pg_attribute a WHERE c.relname = TG_RELNAME AND a.attnum > 0
AND a.attrelid = c.oid ORDER BY a.attnum LOOP/* RAISE NOTICE ''column name for % is %'', TG_RELNAME,
rec.attname;*/ att := att || ''OLD.'' || rec.attname || '',''; END LOOP; /* remove last coma, add closing
paren */ att := rtrim(att,'','') || '')''; RAISE NOTICE ''query is %'', att; EXECUTE att; RETURN
NEW;END;'language 'plpgsql';
The EXECUTE gives the following error:
psql:archive.sql:40: ERROR: OLD used in non-rule query
The best solution would be to simply do:
INSERT INTO table_archive SELECT OLD.*;
but it doesn't work.
Is there a clean solution in pl/pgsql or should I directly try in C?
-- THERAMENE: Prends soin après ma mort de ma chère Aricie. Cher ami, si mon père un jour désabusé
Plaintle malheur d'un fils faussement accusé, Pour apaiser mon sang et mon ombre plaintive, Dis-lui
qu'avecdouceur il traite sa captive, Qu'il lui rende... A ce mot ce héros expiré N'a laissé dans
mesbras qu'un corps défiguré, Triste objet, où des Dieux triomphe la colère, Et que méconnaîtrait
l'oeilmême de son père. (Phèdre, J-B Racine, acte 5, scène 6)