Re: TG_COLUMNS_UPDATED

From: Josh Kupershmidt <schmiddy(at)gmail(dot)com>
To: Bartosz Dmytrak <bdmytrak(at)gmail(dot)com>
Cc: david(dot)sahagian(at)emc(dot)com, pgsql-general(at)postgresql(dot)org
Subject: Re: TG_COLUMNS_UPDATED
Date: 2012-07-04 18:33:04
Message-ID: CAK3UJRFUiTKxw4ufUQLoZLfYmQ+XhfA6_DZGoYc8bauBQY60YQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tomonari Katsumata 2012-07-05 05:11:25 question about source download site.
Previous Message Claudio Freire 2012-07-04 14:26:36 Re: The need for clustered indexes to boost TPC-V performance