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@krogh.cc, Jabber ID: jesper@jabbernet.dk