Re: TG_COLUMNS_UPDATED

[Bartosz Dmytrak <bdmytrak@xxxxxxxxx>]

Hi,

I am not sure if it is bullet proof, but could be good starting point. Maybe someone else could find better solution:

CREATE OR REPLACE FUNCTION myschema."doCheckChanges"()

  RETURNS trigger AS

$BODY$

DECLARE

v_match_array BOOLEAN[];

v_match BOOLEAN;

v_row RECORD;

BEGIN

FOR v_row IN

SELECT attname

FROM pg_attribute

WHERE attrelid = (quote_ident(TG_TABLE_SCHEMA) || '.' || quote_ident(TG_TABLE_NAME))::text::regclass

AND attnum > 0

ORDER BY attnum

LOOP

EXECUTE 'SELECT NOT ($1.' || quote_ident(v_row.attname) || ' = $2.' || quote_ident(v_row.attname)  || ')' INTO v_match USING NEW, OLD;

v_match_array = array_append (v_match_array, v_match);

END LOOP;

RAISE NOTICE 'array: %', (array_to_string(v_match_array, ','));

RETURN NEW;

END;

$BODY$

LANGUAGE plpgsql SECURITY DEFINER

assumption: this is on update trigger function - You could extend this code to check trigger conditions and do what You want to do with v_match_array.

Regards,
Bartek

2012/7/3 <david.sahagian@xxxxxxx>

 

I would like another TG_* special variable to be available to a PL/pgSQL trigger-function.

 

     TG_COLUMNS_UPDATED 

 

Its value would be NULL unless: TG_OP == ' UPDATE' and TG_LEVEL == 'ROW'

 

Data type == varbit

One bit for each column of the table that the trigger is created on.

     1 means that the column was in the set clause of the update statement that made the trigger fire

     0 means it was not

 

I understand that CREATE TRIGGER already has

     UPDATE [ OF column_name [, ... ] ]

 

 

Is this a relatively straightforward enhancement ?

 

It would allow me to know whether various timestamp columns in the row were

unlucky enough to have been set to the same exact value already existing in the table

*versus* were simply not set by the UPDATE statement.

 

Thanks,

-dvs-