Re: Inspection of row types in pl/pgsql and pl/sql

2009/11/14 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Sat, Nov 14, 2009 at 3:24 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> This might look neat but I don't think it's actually useful for any
>> production application.  We'd need to find some way of expressing it
>> that allows caching of the expression plans.  But really I think the
>> entire approach is pretty much backwards from an efficiency standpoint.
>> I would sooner have some sort of primitive "changed_columns(NEW, OLD)"
>> that spits out a list of the names of changed columns (or maybe the
>> not-changed ones, not sure).  It would not require any fundamental
>> restructuring and it would run several orders of magnitude faster
>> than you could ever hope to do it at the plpgsql level.
>
> huge +1 to this.  This problem comes up all the time...I was in fact
> this exact moment working on something just like Florian for table
> auditing purposes...comparing new/old but needing to filter out
> uninteresting columns.  One of those things that should be a SMOP but
> isn't ;-).  I worked out a plpgsql approach using dynamic
> sql...performance wasn't _that_ bad, but any speedup is definitely
> welcome.

C function is_not_distinct(RECORD, RECORD, [variadic columnnames])
should not be a problem (I thing).

Pavel

>
> The way I did it was to pass both new and old to a function as text,
> and build an 'is distinct from' from with the interesting field list
> querying out fields from the expanded composite type...pretty dirty.
>
> merlin
>