------=_Part_10618_27563894.1210122809277
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
This is easy with plpython. We do something similar.
Kerri
On Tue, May 6, 2008 at 6:10 PM, Klint Gore <kgore4@[EMAIL PROTECTED]
> wrote:
> Fernando wrote:
>
> > I want to keep a history of changes on a field in a table. This will
b=
e
> > 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 :=3D 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
field=
s
> 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
chec=
k
> 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 =3D ??tables??.yyy
> where tablename =3D 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 :=3D 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@[EMAIL PROTECTED]
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
--=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_10618_27563894.1210122809277
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
This is easy with plpython. We do something
similar.<br><br>Kerri<br>=
<br><div class=3D"gmail_quote">On Tue, May 6, 2008 at 6:10 PM, Klint Gore
&=
lt;<a href=3D"mailto:kgore4@[EMAIL PROTECTED]
">kgore4@[EMAIL PROTECTED]
>>
wrote:<br=
><blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid
rgb(204,=
204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;">
<div><div></div><div class=3D"Wj3C7c">Fernando wrote:<br>
<blockquote class=3D"gmail_quote" style=3D"border-left: 1px solid rgb(204,
=
204, 204); padding-left: 1ex;">
I want to keep a history of changes on a field in a table. This will
=
be the case in multiple tables.<br>
<br>
Can I create a trigger that loops the OLD and NEW values and compares the
v=
alues and if they are different creates a change string as follows:<br>
<br>
e.g;<br>
<br>
FOR EACH field IN NEW<br>
IF field.value <> <a href=3D"http://OLD.field.name"
tar=
get=3D"_blank">OLD.field.name</a> THEN<br>
changes :=3D changes<br>
|| <a href=3D"http://field.name"
=
target=3D"_blank">field.name</a><br>
|| ' was: '<br>
|| OLD.field.value<br>
|| ' now is: '<br>
|| field.value<br>
|| '\n\r';<br>
END IF<br>
END FOR;<br>
<br>
Your help is really appreciated.<br>
</blockquote></div></div>
You can't in plpgsql. It doesn't have the equivalent of a
wal=
kable fields collection. Its possible in some other procedure
languag=
es (I've seen it done in C).<br>
<br>
Having said that, you might be able to create new and old temp tables and
t=
hen use the system tables to walk the columns list executing sql to check
f=
or differences.<br>
<br>
something like<br>
<br>
create temp table oldblah as select old.*;<br>
create temp table newblah as select new.*;<br>
for arecord in<br>
select columnname<br>
from pg_??columns??<br>
join pg_??tables?? on ??columns??.xxx =3D
??tables??.=
yyy<br>
where tablename =3D oldblah and
pg_table_is_visible<br=
>
loop<br>
<br>
execute 'select old.' || arecord.columname
||=
'::text , new. ' || arecord.columname || '::text' ||<br>
' from
=
oldblah old, newblah new ' ||<br>
'
where=
oldblah.' || arecord.columnname || ' <> newblah.'
||arec=
ord.columnname into oldval,newval;<br>
<br>
changes :=3D changes || arecord.columnname || '
wa=
s ' || oldval || ' now ' || newval;<br>
end loop;<br>
execute 'drop table oldblah';<br>
execute 'drop table newblah';<br>
<br>
performance could be awful though.<br>
<br>
klint.<br>
<br>
-- <br>
Klint Gore<br>
Database Manager<br>
Sheep CRC<br>
A.G.B.U.<br>
University of New England<br>
Armidale NSW 2350<br>
<br>
Ph: 02 6773 3789 Fax: 02 6773 3266<br>
EMail: <a href=3D"mailto:kgore4@[EMAIL PROTECTED]
"
target=3D"_blank">kgore4@[EMAIL PROTECTED]
><br><font color=3D"#888888">
<br>
<br>
-- <br>
Sent via pgsql-general mailing list (<a
href=3D"mailto:pgsql-general@[EMAIL PROTECTED]
" target=3D"_blank">pgsql-general@[EMAIL PROTECTED]
>)<br>
To make changes to your subscription:<br>
<a href=3D"http://www.postgresql.org/mailpref/pgsql-general"
target=3D"_bla=
nk">http://www.postgresql.org/mailpref/pgsql-general</a><br>
</font></blockquote></div><br><br clear=3D"all"><br>-- <br>Yuma
Educational=
Computer Consortium<br>Compass Development Team<br>Kerri Reno<br><a href=
=3D"mailto:kreno@[EMAIL PROTECTED]
">kreno@[EMAIL PROTECTED]
> (928)
502-4240<br>.=B7:*=
=A8=A8*:=B7. .=B7:*=A8=A8*:=B7. .=B7:*=A8=A8*:=B7.
------=_Part_10618_27563894.1210122809277--
|
