469,572 Members | 1,216 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

field incrementing in a PL/pgSQL trigger

Hi,

My boss wants to add a special type of logging to some of our tables on
update/delete/insert. I need to log who, when, table_name, field name,
original value and new value for each record, but only logging modified
fields, and he wants me to do this using postgres pgSQL triggers. The
changes would be inserted into a second table.

We are given 10 automatically created variables. Some of which I know I can
use: NEW, OLD, TG_WHEN, TG_OP and TG_RELNAME. I can use these to get
general information for the update, but when the trigger is called, I don't
know how many fields are in the tables that are being updated.

My questions are: Is there a way I can dynamically determine the number of
fields in the rows that is being maintained. (a function much like:
PQnfields(const PGresult *); )
Then I need a way to get the name of the field (using a function much like:
PQfname(const PGresult *, int); )

Using the dynamically generated name I could then walk the NEW and OLD
columns to compare the values. (e.g. if (NEW.field != OLD.field) do
something;);

Can anyone help me with this? Thank you in advance.

Tim Vadnais

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Nov 23 '05 #1
2 4493
"Tim Vadnais" <tv******@earthlink.net> writes:
My questions are: Is there a way I can dynamically determine the number of
fields in the rows that is being maintained.


I'm starting to think there should be a FAQ entry for this ;-)

plpgsql is essentially incapable of doing anything that involves dynamic
field access, especially if the field types aren't known in advance either.
I believe you can do what you want in pltcl, and you can definitely
write such a trigger in C, but plpgsql is the wrong tool for the job.

If you want to try it in C, there are some relevant examples in
contrib/spi/ in the PG source distribution.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #2
On Thu, Oct 28, 2004 at 09:14:17AM -0700, Tim Vadnais wrote:

My questions are: Is there a way I can dynamically determine the number of
fields in the rows that is being maintained. (a function much like:
PQnfields(const PGresult *); )
Then I need a way to get the name of the field (using a function much like:
PQfname(const PGresult *, int); )


You asked this last week and there were a couple of responses:

http://archives.postgresql.org/pgsql...0/msg01077.php
http://archives.postgresql.org/pgsql...0/msg01097.php
http://archives.postgresql.org/pgsql...0/msg01112.php

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Josué Maldonado | last post: by
3 posts views Thread by D. Stimits | last post: by
1 post views Thread by Jim Bowe | last post: by
3 posts views Thread by Justin Clift | last post: by
1 post views Thread by Graeme Hinchliffe | last post: by
2 posts views Thread by Tim Vadnais | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.