From: | Jesper Krogh <jesper(dot)krogh(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Generic archive functions. |
Date: | 2006-02-28 08:28:16 |
Message-ID: | du11j0$cgs$1@sea.gmane.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
We're trying to setup a generic archive function for tables in
the database, this is our first try with stored procedures and triggers,
thus we might just have a small flaw somewhere:
This function triggers will be set to trigger on "update" on the tables:
CREATE OR REPLACE FUNCTION global.update_base()
RETURNS TRIGGER AS
$$
DECLARE
BEGIN
EXECUTE 'INSERT INTO ' || TG_RELNAME || '_archive SELECT NEW.*;';
NEW.updated_initials=user;
NEW.updated=now();
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
and the table "tablename_archive" is just created using inheritance:
CREATE TABLE tablename_archive (
) INHERITS (tablename);
But it gives this error when triggered:
test=# update experiment set title ='testafdasdfasfdaasdfasfdasfsa'
where id = 2;
ERROR: NEW used in query that is not in a rule
CONTEXT: SQL statement "INSERT INTO experiment_archive SELECT NEW.*;"
PL/pgSQL function "update_base" line 3 at execute statement
So the stuff about "select new.*" was probably flawed. What would
be the way to access the original tuple from a "EXECUTE" statement be
then?
Are there more possibillities in other server-side languages for doing
this?
It would just be a shame to be forced to create new (nearly identical)
functions for every table we would like archiving on.
Jesper
--
./Jesper Krogh, jesper(at)krogh(dot)cc, Jabber ID: jesper(at)jabbernet(dot)dk
From | Date | Subject | |
---|---|---|---|
Next Message | Arnaud Lesauvage | 2006-02-28 09:55:50 | Finding the right logging level |
Previous Message | Michael Fuhr | 2006-02-27 21:30:13 | Re: unique index |