Re: Dyamic updates of NEW with pl/pgsql

On Mon, Mar 15, 2010 at 10:02 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Not quite, but close. We also need a nice way of querying for field names
> (at least) at run time. I've seen that requested several times.

ok. just making sure we were on the same page. wasn't there a
technical objection to querying the fields at runtime? If not, maybe
you could get by with something like:

Integer variant of operator pulls fields by index
somettype v := recvar->3;

integer n := nfields(recordtype);

text[] fields := fieldnames(recordtype);

text fieldname := fieldname(recordtype, 3);
int fieldpos := fieldpos(recordtype, 'a_field');

OK, from archives (Tom wrote) quoting:
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.
/end quote

does the parse/plan objection still hold?

merlin