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
		 */
		att text := ''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ï¿½
            Plaint le malheur d'un fils faussement accusï¿½,
            Pour apaiser mon sang et mon ombre plaintive,
            Dis-lui qu'avec douceur il traite sa captive,
            Qu'il lui rende... A ce mot ce hï¿½ros expirï¿½
            N'a laissï¿½ dans mes bras qu'un corps dï¿½figurï¿½,
            Triste objet, oï¿½ des Dieux triomphe la colï¿½re,
            Et que mï¿½connaï¿½trait l'oeil mï¿½me de son pï¿½re.
                                          (Phï¿½dre, J-B Racine, acte 5, scï¿½ne 6)

Tue, 17 Apr 2001 18:00:05 +0200
Louis-David Mitterrand <vindex@apartia.ch>
row archiving trigger function