Re: Is this possible in a trigger?

by fernando@[EMAIL PROTECTED] (Fernando) May 7, 2008 at 09:37 AM

This is a multi-part message in MIME format.
--------------050909080107030605020802
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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.
>

--------------050909080107030605020802
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<font face="Comic Sans MS">Thank you for your answer.  I guess I better
create this history in the application's data class.</font><br>
<br>
Klint Gore wrote:
<blockquote cite="mid:4820F38A.2080005@[EMAIL PROTECTED]
" type="cite">Fernando
wrote:
  <br>
  <blockquote type="cite">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 values 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 &lt;&gt; OLD.field.name THEN
    <br>
       changes := changes
    <br>
            || field.name
    <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>
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).
  <br>
  <br>
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.
  <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 = ??tables??.yyy
  <br>
      where tablename = 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 || '
&lt;&gt; newblah.' ||arecord.columnname    into oldval,newval;
  <br>
  <br>
      changes := changes || arecord.columnname || ' was ' || 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>
</blockquote>
</body>
</html>

--------------050909080107030605020802--