Re: [GENERAL] Is this possible in a trigger?

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
.·:*¨¨*:·.   .·:*¨¨*:·.   .·:*¨¨*:·.