Re: PLPGSQL Generic query in trigger doubt

On Wed, Aug 10, 2011 at 8:38 AM, Mauro <maurogdo(at)yahoo(dot)com(dot)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(at)postgresql(dot)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