History

    I need to record history for a table so I have come up with a
trigger (in plpython) to automatically insert records when anything
happerns into a history table. (could also be used for replication etc...)
The problem is that the table is likly to grow very quickly once I put the
trigger on a few tables. Should I use inherrtance and have a different
table for each table I want history for or one table for the lot?
    Currently the table looks like this.... (my key may not be an int)

              Table "public.history"
 Column  |            Type             | Modifiers
---------+-----------------------------+-----------
 tab     | text                        |
 field   | text                        |
 action  | text                        |
 before  | text                        |
 after   | text                        |
 occured | timestamp without time zone |
 key     | text                        |
 who     | text                        |
Indexes: history_tab btree (tab),
         history_tab_field btree (tab, field),
         history_tab_key btree (tab, "key"),
         history_who btree (who)

and for anyone who wants to see the trigger....
I found I could not do this at all in plpgsql.

Peter Childs

PS The Trigger..... for anyone intrested (will work on any table)


CREATE OR REPLACE FUNCTION history_update() RETURNS TRIGGER AS '
if TD["event"] == "INSERT":
  lookup = "new"
elif TD["event"] == "DELETE":
  lookup = "old"
else:
  lookup = "new"
p = plpy.execute(" SELECT CASE i.indproc WHEN (''-''::pg_catalog.regproc) THEN a.attname ELSE
SUBSTR(pg_catalog.pg_get_indexdef(attrelid),POSITION(''('' in pg_catalog.pg_get_indexdef(attrelid))) END as pkey,
a.atttypid::int,c2.relname FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i,
pg_catalog.pg_attributea WHERE c.oid = " + TD["relid"] + " AND c.oid = i.indrelid AND i.indexrelid = c2.oid and
a.attrelid= i.indexrelid and NOT a.attisdropped and i.indisprimary ORDER BY i.indisprimary DESC, i.indisunique DESC,
c2.relname;")
if len(p) > 0:
  pkey = TD[lookup][p[0]["pkey"]]
  ppkey = p[0]["pkey"]
else:
  pkey = ""
  ppkey = ""
rel = plpy.execute("select relname from pg_class where oid=" + TD["relid"] + ";")
relname = rel[0]["relname"]
plan = plpy.prepare("INSERT INTO history (tab,field,action,before,after,occured,who,key) values
($1,$2,$3,$4,$5,now(),user,$6);",["text","text","text","text","text","text"])
if TD["event"] == "INSERT":
  old = ""
  new = pkey
  plpy.execute(plan,[relname,ppkey,TD["event"],old,new,pkey])
else:
  for key in TD[lookup].keys():
    dont = 0
    if TD["event"] == "INSERT":
      old = ""
      new = TD["new"][key]
      if new == None:
        dont = 1
    elif TD["event"] == "UPDATE":
      old = TD["old"][key]
      new = TD["new"][key]
    else:
      old = TD["old"][key]
      new = ""
    if old == None:
      old = "Null"
    if new == None:
      new = "Null"
    if not(dont):
      plpy.execute(plan,[relname,key,TD["event"],old,new,pkey])
' LANGUAGE 'plpython';


DROP TRIGGER test_history_update ON test;
CREATE TRIGGER test_history_update AFTER INSERT OR UPDATE OR DELETE ON test FOR EACH ROW
  EXECUTE PROCEDURE history_update();