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



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq