PLPGSQL Generic query in trigger doubt

On Wed, Aug 10, 2011 at 8:38 AM, Mauro <maurogdo@yahoo.com.br> wrote:

Hi, good morning list
I'm writing a generic trigger in plpgsql to provide a system log to my
system, but I'm stopped with the folow problem:
Example:
TABLE STRUCT:
table1
   fldA    VARCHAR
   fldB    VARCHAR
   fldC    VARCHAR
FUNCTION:
DECLARE
   myrecord     RECORD; -- record that will be storing field names from
internal postres tables
   fieldtest    NAME; -- actual field name parsing
BEGIN
   -- Generic function to automatize the log of changes

   -- 1st, discover the field names from a table
   FOR myrecord IN
   SELECT
      att.attname
     FROM
      pg_attribute att,
      pg_class cls
     WHERE
          cls.oid = att.attrelid
      AND att.attnum > 0
      AND cls.relname = TG_RELNAME limit 1
   LOOP
      -- storing the actual field name
      fieldtest = myrecord.attname;

      /*
         Here I'd like to do a parse in the 'fieldtest' variable to teste if
the new value is diferent of the old value. The problem is:
         Variable name: fieldtest
         Variable content: fldA
         How I can test the two records (new and old)?
         -- new.fieldtest = fieldtest is not a field name to new record
         -- new."fieldtest" = fieldtest is not a field name to new record
         -- new.(fieldtest) = plpgsql can not do a parser in this
         -- 'new.' || fieldtest = this is a string and can not be evaluate
      */

   END LOOP;

   -- Returning
   RETURN NEW;
END;

This is a FAQ. See archives (for example, here:
http://www.mail-archive.com/pgsql-hackers@postgresql.org/msg182249.html).

A couple quick points:
*) plpgsql does not directly support record access like that.
*) you can compare new vs old directly without breaking down to fields
*) record into hstore is probably the easiest approach to dynamic
access of record fields by field name -- then you can drop the catalog
query

merlin

Hi Mauro,

If you try to determine what fields were changed you can check this post:

http://jaime2ndquadrant.blogspot.com/

It might work for you.

Ioana

--- On Wed, 8/10/11, Mauro <maurogdo@yahoo.com.br> wrote:
Hi, good morning list
I'm writing a generic trigger in plpgsql to provide a system log to my system, but I'm stopped with the folow problem:
Example:
TABLE STRUCT:table1   fldA    VARCHAR   fldB    VARCHAR   fldC    VARCHAR
FUNCTION:
DECLARE
   myrecord     RECORD; -- record that will be storing field names from internal postres tables
   fieldtest    NAME; -- actual field name parsing
BEGIN
   -- Generic function to automatize the log of changes
   
   -- 1st, discover the field names from a table
   FOR myrecord IN
   SELECT
      att.attname
     FROM
      pg_attribute att,
      pg_class cls
     WHERE
          cls.oid = att.attrelid
      AND att.attnum > 0
      AND cls.relname = TG_RELNAME limit 1
   LOOP
      -- storing
 the actual field name
      fieldtest = myrecord.attname;
      
      /*         Here I'd like to do a parse in the 'fieldtest' variable to teste if the new value is diferent of the old value. The problem is:         Variable name: fieldtest         Variable content: fldA
         How I can test the two records (new and old)?         -- new.fieldtest = fieldtest is not a field name to new record         -- new."fieldtest" = fieldtest is not a field name to new record         -- new.(fieldtest) = plpgsql can not do a parser in this         -- 'new.' || fieldtest = this is a string and can not be evaluate
      */

   END LOOP;
  
   -- Returning
   RETURN NEW;
END;

 Mauro Gonçalves