From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: arcpro@digitalwizardry.net
Subject: Re: PL/Pgsql trigger function problem.
Date: Wed, 12 Feb 2003 14:48:58 +0100
Message-ID: <3E4A50CA.1295488@rodos.fzk.de> (raw)
>
> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ch@rodos.fzk.de, arcpro@digitalwizardry.net
Subject: Re: PL/Pgsql trigger function problem.
In-Reply-To: <3E4A50CA.1295488@rodos.fzk.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox