Обсуждение: 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@une.edu.au

This is easy with plpython.  We do something similar.

Kerri




--
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@yumaed.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...@ggtours.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@yumaed.org      (928) 502-4240
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.