From: James C. Ousley <jcousley@mikrotec.com>
To: pgsql-sql@postgresql.org
Subject: PL/Pgsql trigger function issue..
Date: Tue, 11 Feb 2003 10:49:12 -0500
Message-ID: <3E491B78.7040603@mikrotec.com> (raw)
This is suppose to pull all the columns of the table that initiated the
trigger func from the sys catalogs, loop through them and put everything
that has changed between OLD and NEW into a comma delimited string for
input into 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 column name. The only way I can
find to make this work is by using TCL for the 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 has been
stripped to show my specific problem so using TCL is currently not in 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 = ''UPDATE'' THEN
FOR table_cols IN select attname from pg_attribute where attrelid =
TG_RELID and attnum > -1 LOOP
A := table_cols.attname;
IF OLD.A != NEW.A THEN --Begin problem
IF attribs != '''' THEN
attribs := attribs || '','' || table_cols.attname || ''='' ||
OLD.A || ''->'' || NEW.A;
ELSE
attribs := table_cols.attname || ''='' || OLD.A || ''->'' || NEW.A;
END IF;
END IF;
END LOOP;
END IF;
RAISE EXCEPTION ''%'', attribs;
RETURN NULL;
END;
' Language 'plpgsql';
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: jcousley@mikrotec.com
Subject: Re: PL/Pgsql trigger function issue..
In-Reply-To: <3E491B78.7040603@mikrotec.com>
* 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