Re: Dyamic updates of NEW with pl/pgsql

On 17.03.10 4:08 , Merlin Moncure wrote:
> On Tue, Mar 16, 2010 at 5:53 PM, Florian
> Pflug<fgp(dot)phlo(dot)org(at)gmail(dot)com> wrote:
>> 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...
>
> Why should we use what you've already written when we can just write
> it ourselves? Next you are going to say you're already using it and
> it works really well :-).
Well, compared to the solution it replaced it works extraordinarily well
- but that solution was a mess of plpgsql functions generating other
plpgsql functions - so shining in comparison doesn't really prove much :-)

> I think it's pretty cool. Is it safe to have the main functions
> immutable and not stable though?
I think it's safe - if a table or composite type is modified, a query
using that table or type will have to be re-planned anyway, independent
from whether fieldvalue() is used or not.

> Is there any benefit missed by not going through pl/pgsql directly
> (I'm guessing maybe more elegant caching)?
AFAIK in pl/pgsql your only options to retrieve a field by name is to
either use hstore which coerces all values to text, or to use
EXECUTE 'SELECT %1' || v_fieldname INTO v_fieldvalue USING v_record. The
execute query will need to be planned on every execution, while my
fieldvalue() function tries to cache as much information as possible.

The EXECUTE method will also always coerce the field's value to the type
of v_fieldvalue - AFAICS there is no way to get the behaviour of
fieldvalue() with <coerce> set to false.

> It's a little weird that you can return anyelement from your function
> in cases that don't guarantee a type from the query. Are there any
> downsides to doing that?
Hm, the type of fieldvalue()'s return value is always the same as the
one of the ANYELEMENT input value <defvalue>. If <coerce> is true, then
the field value's type may be different, but fieldvalue() takes care of
coercing it to <defvalue>'s type *before* returning it.

So from a type system's perspective, fieldvalue() plays entirely by the
rules.

The only open issue in my code is the caching of the coercion plans -
currently, they're cached in fcinfo->flinfo->fn_extra, and never
invalidated. I believe the plan invalidation machinery might make it
possible to invalidate those plans should the CAST definitions change,
but I haven't really looked into that yet.

best regards,
Florian Pflug