Re: Dyamic updates of NEW with pl/pgsql

On 13.03.10 18:38 , Tom Lane wrote:
> I wrote:
>> ... Maybe it would work to devise a notation that allows fetching
>> or storing a field that has a runtime-determined name, but
>> prespecifies the field type. Actually only the "fetch" end of it is
>> an issue, since when storing the field datatype can be inferred
>> from the expression you're trying to assign to the field.
>
> [ after more thought ]
>
> I wonder if it could work to treat the result of a
> "record->fieldname" operator as being of UNKNOWN type initially, and
> resolve its actual type in the parser in the same way we do for
> undecorated literals and parameters, to wit * you can explicitly cast
> it, viz (record->fieldname)::bigint * you can let it be inferred from
> context, such as the type of whatever it's compared to * throw error
> if type is not inferrable Then at runtime, if the actual type of the
> field turns out to not be what the parser inferred, either throw
> error or attempt a run-time type coercion.  Throwing error seems
> safer, because it would avoid surprises of both semantic (unexpected
> behavior) and performance (expensive conversion you weren't expecting
> to happen) varieties. But possibly an automatic coercion would be
> useful enough to justify those risks.

This is more or less what I've done in my pg_record_inspect module, only
without parser or executor changes (it works with 8.4). The code can be
found on http://github.com/fgp/pg_record_inspect.

The module contains the function

fieldvalue(RECORD, field NAME, defval ANYELEMENT, coerce BOOLEAN)
RETURNS ANYELEMENT

which returns the field named <field> from the record. The expected
field type is specified by providing a default value in <defval> of the
expected type. Since that argument's type is ANYELEMENT, just like the
return type, the type system copes perfectly with the varying return
type. You can choose whether to auto-coerce the field's value if it has
a type other than <defval>'s type or whether to raise an error.

So in essence I'm using the ANYELEMENT trick to get a poor man's version
of your idea that doesn't require core changes.

My post about this module got zero responses though...

best regards,
Florian Pflug