History

From: Peter Childs <blue(dot)dragon(at)blueyonder(dot)co(dot)uk>
To: pgsql-general(at)postgresql(dot)org
Subject: History
Date: 2003-01-31 11:27:43
Message-ID: Pine.LNX.4.44.0301311118420.8103-100000@RedDragon.Childs
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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_attribute a 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();

Responses

  • Re: History at 2003-02-02 00:47:05 from Alan Gutierrez

Browse pgsql-general by date

  From Date Subject
Next Message Arjen van der Meijden 2003-01-31 12:19:23 Re: Weird query plans for my queries,
Previous Message Christian 2003-01-31 10:59:05 Re: Unicode error with pgaccess