Re: field incrementing in a PL/pgSQL trigger

Hi,

My boss wants to add some logging functionality 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 wing postgres pgSQL triggers.

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 row 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 rows
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" <tvadnais(at)earthlink(dot)net> writes:
> My questions are: Is there a way I can dynamically determine the number of
> fields in the row 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); )

plpgsql isn't capable of doing dynamic field access. You could do this
in pltcl, I think, and definitely in C. There are some related examples
in contrib/spi/.

regards, tom lane

Create a different trigger function for each table, then each trigger can
be customized to know the column names.
You can generate the triggers from a little script which queries the
system tables to get at the column names. It would spit code like 'IF
NEW.fieldname != OLD.fieldname THEN (record modification...) END IF for
each field...
Less elegent than a general solution, but why not.

> Hi,
>
> My boss wants to add some logging functionality 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 wing postgres pgSQL triggers.
>
> 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 row 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
> rows
> to compare the values. (e.g. if (NEW.field != OLD.field) do something;);
>
> Can anyone help me with this? Thank you in advance.
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>