Skip site navigation (1) Skip section navigation (2)

Generic archive functions.

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 (view raw or flat)
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



pgsql-novice by date

Next:From: Arnaud LesauvageDate: 2006-02-28 09:55:50
Subject: Finding the right logging level
Previous:From: Michael FuhrDate: 2006-02-27 21:30:13
Subject: Re: unique index

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group