pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedPL/Pgsql trigger function issue..
2+ messages / 2 participants
[nested] [flat]
* PL/Pgsql trigger function issue..
@ 2003-02-11 15:49 James C. Ousley <jcousley@mikrotec.com>
0 siblings, 0 replies; 2+ messages in thread
From: James C. Ousley @ 2003-02-11 15:49 UTC (permalink / raw)
To: pgsql-sql
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';
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: PL/Pgsql trigger function issue...
@ 2003-02-11 16:31 Peter Childs <blue.dragon@blueyonder.co.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Peter Childs @ 2003-02-11 16:31 UTC (permalink / raw)
To: pgsql-sql
On Tue, 11 Feb 103, arcpro wrote:
> 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.
I have a trigger that does the same thing written in pl/python
I've already posted it twice (to general and bugs about a 2 weeks ago) so
I will not post it again :)
The problem with it was (the reason it got on bugs) is that when I
had it switched on on almost all my tables after a series of queries the
back-end of 7.3.1 crashes. I think its something to do with which particular
query run in what order but the queries work fine without the trigger in
the database and don't without it. I've still had no response so I have
left it off for the moment but I would like to switch it on.
I might try 7.3.2 but I don't know whether its worth it!
The other place to look is at the replication scripts such as
dbmirror in contrib which I must get going (or something like that) at some
point (Need 24x7 uptime!!)
There seam to be several replication projects which one is the
best/most developed/easy to implement. I would like a dual master rather
than a master slave configuration if that possible.
Peter Childs
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-02-11 16:31 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-11 15:49 PL/Pgsql trigger function issue.. James C. Ousley <jcousley@mikrotec.com>
2003-02-11 16:31 Re: PL/Pgsql trigger function issue... Peter Childs <blue.dragon@blueyonder.co.uk>
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