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

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.

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