------=_Part_12773_30458110.1210169761487
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
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'] !=3D 'AFTER':
plpy.error('logchange:not called AFTER')
if TD['level'] !=3D 'ROW':
plpy.error('logchange:not called per ROW')
if TD['event'] =3D=3D 'UPDATE':
# get the name of the current table.
result =3D plpy.execute("select relname from pg_class where
oid=3D'%s=
'" %
TD['relid'])
if len(result) !=3D 1:
plpy.error('no table name found in pg_class')
tblname =3D result[0]['relname']
changes =3D ''
# 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] !=3D TD['old'][k]:
changes +=3D '%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 =3D 'INSERT INTO log_audit (table, id, change) values
(%s,%s,'%s')" % \
(tblname,TD['new'][oid],changes )
plpy.debug('qry:',qry)
result =3D plpy.execute(qry)
plpy.execute('NOTIFY LOGAUDITCHANGE')
return None
$BODY$
LANGUAGE 'plpythonu';
--=20
Yuma Educational Computer Consortium
Compass Development Team
Kerri Reno
kreno@[EMAIL PROTECTED]
(928) 502-4240
..=B7:*=A8=A8*:=B7. .=B7:*=A8=A8*:=B7. .=B7:*=A8=A8*:=B7.
------=_Part_12773_30458110.1210169761487
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Fernando,<br><br>Below is a function that I hope gets you started.
It=
hasn't been tested, I cut and pasted from our procedure, which is
rath=
er more complex. You didn't say what you wanted to do with the
ch=
anges when you found them, this puts them in a log_audit table. The
t=
hing to remember about python is that it's completely based on
indentat=
ion, 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
o=
n to python! <br>
<br>If you don't have the python programming language installed on
your=
db, I think this should do it:<br>create language plpythonu<br><br>These
l=
inks could be helpful too:<br><a
href=3D"http://rgruet.free.fr/PQR25/PQR2.5=
..html">http://rgruet.free.fr/PQR25/PQR2.5.html</a><br>
<a
href=3D"http://www.postgresql.org/docs/8.2/static/plpython.html">http://=
www.postgresql.org/docs/8.2/static/plpython.html</a><br><br>Hope this
helps=
!<br>Kerri<br><br>CREATE OR REPLACE FUNCTION logchange()<br> RETURNS
=
"trigger" AS<br>
$BODY$<br><br> plpy.debug('function:
logchange')<=
br> #check to make sure i'm called correctly, error
w=
ill stop the trigger<br> if TD['when'] !=3D
'=
AFTER':<br>
plpy.error('logchange:not called
=
AFTER')<br> if TD['level'] !=3D
'ROW'=
:<br> plpy.error('logchange:not
ca=
lled per ROW')<br><br> if TD['event'] =3D=3D
=
'UPDATE':<br>
# get the name of the current
table.<br>&nbs=
p; result =3D plpy.execute("select relname
fro=
m pg_class where oid=3D'%s'" %
TD['relid'])<br> &=
nbsp; if len(result) !=3D
1:<br> &=
nbsp; plpy.error('no table name found in
pg_class')<br>
tblname =3D
result[0]['relname']<br>=
<br>
changes =3D
''<br> =
# TD['new'] and 'old' are python
dictionaries,=
so they can be traversed, in this case by
the<br> &=
nbsp; # dictionary keys<br> for k in
TD=
['new'].keys():<br>
if
TD['new']=
[k] !=3D
TD['old'][k]:<br>  =
; changes +=3D '%s was: %s now is:
%s\n\r=
' % (k,TD['old'][k],TD['new'][k])<br>
<br> if len(changes) >
0:<br>  =
; # this assumes the table has an oid, if you have your
o=
wn id #, use it<br> qry =3D 'INSERT
INT=
O log_audit (table, id, change) values (%s,%s,'%s')" % \<br>
(tblname,TD['new=
'][oid],changes )<br>
plpy.debug('q=
ry:',qry)<br> result =3D
plpy.execute(q=
ry)<br> plpy.execute('NOTIFY
LOGAUDITCH=
ANGE')<br> return None<br>
$BODY$<br>LANGUAGE 'plpythonu';<br><br><br>-- <br>Yuma Educational
=
Computer Consortium<br>Compass Development Team<br>Kerri Reno<br><a
href=3D=
"mailto:kreno@[EMAIL PROTECTED]
" target=3D"_blank" onclick=3D"return
top.js.OpenEx=
tLink(window,event,this)">kreno@[EMAIL PROTECTED]
>  =
; (928) 502-4240<br>
..=B7:*=A8=A8*:=B7. .=B7:*=A8=A8*:=B7.
.=B7:*=A8=A8*=
:=B7.
------=_Part_12773_30458110.1210169761487--
|
