Thread: PLPGSQL Generic query in trigger doubt
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;
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;
-- Returning
RETURN NEW;
END;
Mauro Gonçalves
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 |