>
> 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
