PLPGSQL Generic query in trigger doubt

From: Mauro <maurogdo(at)yahoo(dot)com(dot)br>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: PLPGSQL Generic query in trigger doubt
Date: 2011-08-10 13:38:05
Message-ID: 1312983485.46191.YahooMailNeo@web130205.mail.mud.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-08-10 13:51:42 Re: PLPGSQL Generic query in trigger doubt
Previous Message Alex Lai 2011-08-10 13:37:31 Need to purge applied log from arch_replicate directory