Message-ID: <3E4A50CA.1295488@rodos.fzk.de>
Date: Wed, 12 Feb 2003 14:48:58 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: arcpro@digitalwizardry.net
Subject: Re: PL/Pgsql trigger function problem.
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
>   This is suppose to pull all the columns of the table that initiated
the t=
> rigger func from the sys catalogs, loop through them and put
everything tha=
> t has changed between OLD and NEW into a comma delimited string for
input i=
> nto a log like table for future analysis via middleware
(php,perl..,etc).  =
> Here is the problem, OLD.A results in 'old does not have field A',
which is=
>  true. I cant get the OLD and NEW record objects to realize that I
want OLD=
> .<string value of A> for the column name instead of an explicit A as
the co=
> lumn name.  The only way I can find to make this work is by using TCL
for t=
> he procedural language because of the way it casts the OLD and NEW
into an =
> associative array instead of a RECORD object, but by using TCL I will
lose =
> functionallity in the "complete" version of the following function
which ha=
> s been stripped to show my specific problem so using TCL is currently
not i=
> n my list of options.  Any insight will be greatly appreciated.
>
> create or replace function hmm() returns TRIGGER as '
> DECLARE
> table_cols RECORD;
> attribs VARCHAR;
> A VARCHAR;
> BEGIN
> IF TG_OP =3D ''UPDATE'' THEN
>      FOR table_cols IN select attname from pg_attribute where attrelid
=3D =
> TG_RELID and attnum > -1 LOOP
>       A :=3D table_cols.attname;
>       IF OLD.A !=3D NEW.A THEN  --Begin problem=20
>        IF attribs !=3D '''' THEN
>        attribs :=3D attribs || '','' || table_cols.attname || ''=3D''
|| OL=
> D.A || ''->'' || NEW.A;
>        ELSE
>        attribs :=3D table_cols.attname || ''=3D'' || OLD.A || ''->''
|| NEW=
> .A;
>        END IF;
>       END IF;
>      END LOOP;
> END IF;
> RAISE EXCEPTION ''%'', attribs;
> RETURN NULL;
> END;
> ' Language 'plpgsql';
>

James,
If I understand your intentions correctly, you are trying to achieve a
general procedure
to log all updates of all tables. Right?
The only way I can think of from my point of knowledge is use middleware
to generate
a big sql script with a CREATE PROCEDURE and CREATE TRIGGER statement
for every table you want updates being logged. This might be no option
for you as well,
but I would like to hear if at least my interpretation of your request
was correct.
Regards, Christoph