Re: Is this possible in a trigger?

I want to keep a history of changes on a field in a table. This will be
the case in multiple tables.

Can I create a trigger that loops the OLD and NEW values and compares
the values and if they are different creates a change string as follows:

e.g;

FOR EACH field IN NEW
IF field.value <> OLD.field.name THEN
changes := changes
|| field.name
|| ' was: '
|| OLD.field.value
|| ' now is: '
|| field.value
|| '\n\r';
END IF
END FOR;

Your help is really appreciated.

Thank you.

Fernando wrote:
> I want to keep a history of changes on a field in a table. This will
> be the case in multiple tables.
>
> Can I create a trigger that loops the OLD and NEW values and compares
> the values and if they are different creates a change string as follows:
>
> e.g;
>
> FOR EACH field IN NEW
> IF field.value <> OLD.field.name THEN
> changes := changes
> || field.name
> || ' was: '
> || OLD.field.value
> || ' now is: '
> || field.value
> || '\n\r';
> END IF
> END FOR;
>
> Your help is really appreciated.
You can't in plpgsql. It doesn't have the equivalent of a walkable
fields collection. Its possible in some other procedure languages (I've
seen it done in C).

Having said that, you might be able to create new and old temp tables
and then use the system tables to walk the columns list executing sql to
check for differences.

something like

create temp table oldblah as select old.*;
create temp table newblah as select new.*;
for arecord in
select columnname
from pg_??columns??
join pg_??tables?? on ??columns??.xxx = ??tables??.yyy
where tablename = oldblah and pg_table_is_visible
loop

execute 'select old.' || arecord.columname || '::text , new. '
|| arecord.columname || '::text' ||
' from oldblah old, newblah new ' ||
' where oldblah.' || arecord.columnname || ' <>
newblah.' ||arecord.columnname into oldval,newval;

changes := changes || arecord.columnname || ' was ' || oldval ||
' now ' || newval;
end loop;
execute 'drop table oldblah';
execute 'drop table newblah';

performance could be awful though.

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4(at)une(dot)edu(dot)au

This is easy with plpython. We do something similar.

Kerri

On Tue, May 6, 2008 at 6:10 PM, Klint Gore <kgore4(at)une(dot)edu(dot)au> wrote:

> Fernando wrote:
>
> > I want to keep a history of changes on a field in a table. This will be
> > the case in multiple tables.
> >
> > Can I create a trigger that loops the OLD and NEW values and compares
> > the values and if they are different creates a change string as follows:
> >
> > e.g;
> >
> > FOR EACH field IN NEW
> > IF field.value <> OLD.field.name THEN
> > changes := changes
> > || field.name
> > || ' was: '
> > || OLD.field.value
> > || ' now is: '
> > || field.value
> > || '\n\r';
> > END IF
> > END FOR;
> >
> > Your help is really appreciated.
> >
> You can't in plpgsql. It doesn't have the equivalent of a walkable fields
> collection. Its possible in some other procedure languages (I've seen it
> done in C).
>
> Having said that, you might be able to create new and old temp tables and
> then use the system tables to walk the columns list executing sql to check
> for differences.
>
> something like
>
> create temp table oldblah as select old.*;
> create temp table newblah as select new.*;
> for arecord in
> select columnname
> from pg_??columns??
> join pg_??tables?? on ??columns??.xxx = ??tables??.yyy
> where tablename = oldblah and pg_table_is_visible
> loop
>
> execute 'select old.' || arecord.columname || '::text , new. ' ||
> arecord.columname || '::text' ||
> ' from oldblah old, newblah new ' ||
> ' where oldblah.' || arecord.columnname || ' <>
> newblah.' ||arecord.columnname into oldval,newval;
>
> changes := changes || arecord.columnname || ' was ' || oldval || '
> now ' || newval;
> end loop;
> execute 'drop table oldblah';
> execute 'drop table newblah';
>
> performance could be awful though.
>
> klint.
>
> --
> Klint Gore
> Database Manager
> Sheep CRC
> A.G.B.U.
> University of New England
> Armidale NSW 2350
>
> Ph: 02 6773 3789 Fax: 02 6773 3266
> EMail: kgore4(at)une(dot)edu(dot)au
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.

On Tuesday 06 May 2008 20:10:50 Klint Gore wrote:
> Fernando wrote:
> > I want to keep a history of changes on a field in a table. This will
> > be the case in multiple tables.
> >
> > Can I create a trigger that loops the OLD and NEW values and compares
> > the values and if they are different creates a change string as follows:
> >
> > e.g;
> >
> > FOR EACH field IN NEW
> > IF field.value <> OLD.field.name THEN
> > changes := changes
> >
> > || field.name
> > || ' was: '
> > || OLD.field.value
> > || ' now is: '
> > || field.value
> > || '\n\r';
> >
> > END IF
> > END FOR;
> >
> > Your help is really appreciated.
>
> You can't in plpgsql. It doesn't have the equivalent of a walkable
> fields collection. Its possible in some other procedure languages (I've
> seen it done in C).
>

I did it once by setting up the function to accept the tablename and ctid of
the row involved, and then grabbing the info from the system tables.
Certainly easier to do it in plperl though.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Tue, May 06, 2008 at 05:05:37PM -0400, Fernando wrote:
> I want to keep a history of changes on a field in a table. This will be
> the case in multiple tables.

http://pgfoundry.org/projects/tablelog/

depesz

--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)

On May 6, 11:05 pm, ferna(dot)(dot)(dot)(at)ggtours(dot)ca (Fernando) wrote:
> I want to keep a history of changes on a field in a table.  This will be
> the case in multiple tables.
>
> Can I create a trigger that loops the OLD and NEW values and compares
> the values and if they are different creates a change string as follows:
>
> e.g;
>
> FOR EACH field IN NEW
>     IF field.value <> OLD.field.name THEN
>        changes := changes
>             || field.name
>             || ' was: '
>             || OLD.field.value
>             || ' now is: '
>             || field.value
>             || '\n\r';
>     END IF
> END FOR;
>
> Your help is really appreciated.
>
> Thank you.

in plpgsql you could

select new into textVar;

and then do acrobatics with the text value of that record... or
converting the text value into a known table record type with EXECUTE
'select ' || quote_literal(textVar) || '::tableRecord' INTO
tableRecordVar statement. But the field names are to be extracted from
the catalog anyway.

Or use plperl or plpython :(

Thank you for your answer. I guess I better create this history in the
application's data class.

Klint Gore wrote:
> Fernando wrote:
>> I want to keep a history of changes on a field in a table. This will
>> be the case in multiple tables.
>>
>> Can I create a trigger that loops the OLD and NEW values and compares
>> the values and if they are different creates a change string as follows:
>>
>> e.g;
>>
>> FOR EACH field IN NEW
>> IF field.value <> OLD.field.name THEN
>> changes := changes
>> || field.name
>> || ' was: '
>> || OLD.field.value
>> || ' now is: '
>> || field.value
>> || '\n\r';
>> END IF
>> END FOR;
>>
>> Your help is really appreciated.
> You can't in plpgsql. It doesn't have the equivalent of a walkable
> fields collection. Its possible in some other procedure languages
> (I've seen it done in C).
>
> Having said that, you might be able to create new and old temp tables
> and then use the system tables to walk the columns list executing sql
> to check for differences.
>
> something like
>
> create temp table oldblah as select old.*;
> create temp table newblah as select new.*;
> for arecord in
> select columnname
> from pg_??columns??
> join pg_??tables?? on ??columns??.xxx = ??tables??.yyy
> where tablename = oldblah and pg_table_is_visible
> loop
>
> execute 'select old.' || arecord.columname || '::text , new. '
> || arecord.columname || '::text' ||
> ' from oldblah old, newblah new ' ||
> ' where oldblah.' || arecord.columnname || ' <>
> newblah.' ||arecord.columnname into oldval,newval;
>
> changes := changes || arecord.columnname || ' was ' || oldval ||
> ' now ' || newval;
> end loop;
> execute 'drop table oldblah';
> execute 'drop table newblah';
>
> performance could be awful though.
>
> klint.
>

Fernando,

Below is a function that I hope gets you started. It hasn't been tested, I
cut and pasted from our procedure, which is rather more complex. You didn't
say what you wanted to do with the changes when you found them, this puts
them in a log_audit table. The thing to remember about python is that it's
completely based on indentation, so if you have trouble, it's probably
because the indent isn't correct. Also, # means comment. Feel free to
contact me if you have questions or problems. I'm trying to turn the world
on to python!

If you don't have the python programming language installed on your db, I
think this should do it:
create language plpythonu

These links could be helpful too:
http://rgruet.free.fr/PQR25/PQR2.5.html
http://www.postgresql.org/docs/8.2/static/plpython.html

Hope this helps!
Kerri

CREATE OR REPLACE FUNCTION logchange()
RETURNS "trigger" AS
$BODY$

plpy.debug('function: logchange')
#check to make sure i'm called correctly, error will stop the trigger
if TD['when'] != 'AFTER':
plpy.error('logchange:not called AFTER')
if TD['level'] != 'ROW':
plpy.error('logchange:not called per ROW')

if TD['event'] == 'UPDATE':
# get the name of the current table.
result = plpy.execute("select relname from pg_class where oid='%s'" %
TD['relid'])
if len(result) != 1:
plpy.error('no table name found in pg_class')
tblname = result[0]['relname']

changes = ''
# TD['new'] and 'old' are python dictionaries, so they can be
traversed, in this case by the
# dictionary keys
for k in TD['new'].keys():
if TD['new'][k] != TD['old'][k]:
changes += '%s was: %s now is: %s\n\r' %
(k,TD['old'][k],TD['new'][k])

if len(changes) > 0:
# this assumes the table has an oid, if you have your own id #, use
it
qry = 'INSERT INTO log_audit (table, id, change) values
(%s,%s,'%s')" % \
(tblname,TD['new'][oid],changes )
plpy.debug('qry:',qry)
result = plpy.execute(qry)
plpy.execute('NOTIFY LOGAUDITCHANGE')
return None
$BODY$
LANGUAGE 'plpythonu';

--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno(at)yumaed(dot)org (928) 502-4240
.·:*¨¨*:·. .·:*¨¨*:·. .·:*¨¨*:·.