Re: TG_COLUMNS_UPDATED

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-

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(dot)sahagian(at)emc(dot)com>

>
> 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-
>
>
>

On Tue, Jul 3, 2012 at 2:47 PM, Bartosz Dmytrak <bdmytrak(at)gmail(dot)com> wrote:

[snip]

> 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;

A few problems with this function:
1.) The comparison should be using 'IS DISTINCT FROM' instead of != to
handle NULLs
2.) The query against pg_attribute should respect 'attisdropped'.
(There are also other ways to iterate over NEW/OLD fields, e.g. using
hstore.)
3.) This solution doesn't solve the OP's stated goal:

>> 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.

I'm not sure how feasible it'd be add a new TG_ variable available to
plpgsql for the problem above.

Josh