Search Postgresql Archives

Re: TG_COLUMNS_UPDATED

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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-
 
 


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux