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

Andrew Dunstan <andrew(at)dunslane(dot)net> writes:
> Tom Lane wrote:
>>> Perhaps it would help if we looked at some specific use-cases that
>>> people need, rather than debating abstractly. What do you need your
>>> generic trigger to *do*?

> One case I have is a custom audit package that ignores certain fields
> when logging changes. So it would be nice to be able to iterate over the
> field names and check if NEW.foo is distinct from OLD.foo, skipping the
> field names we don't care about to decide if the change needs to be logged.

So, inventing syntax at will, what you're imagining is something like

modified := false;
for name in names(NEW) loop
-- ignore modified_timestamp
continue if name = 'modified_timestamp';
-- check all other columns
if NEW.{name} is distinct from OLD.{name} then
modified := true;
exit;
end if;
end loop;
if modified then ...

While this is perhaps doable, the performance would take your breath
away ... and I don't mean that in a positive sense. The only way we
could implement that in plpgsql as it stands would be that every
single execution of the IF would invole a parse/plan cycle for the
"$1 IS DISTINCT FROM $2" expression. At best we would avoid a replan
when successive executions had the same datatypes for the tested
columns (ie, adjacent columns in the table have the same types).
Which would happen some of the time, but the cost of the replans would
still be enough to sink you.

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.

regards, tom lane