Re: Dyamic updates of NEW with pl/pgsql
От | Florian Pflug |
---|---|
Тема | Re: Dyamic updates of NEW with pl/pgsql |
Дата | |
Msg-id | 4BA0D522.4070503@gmail.com обсуждение исходный текст |
Ответ на | Re: Dyamic updates of NEW with pl/pgsql (Merlin Moncure <mmoncure@gmail.com>) |
Список | pgsql-hackers |
On 17.03.10 4:08 , Merlin Moncure wrote: > On Tue, Mar 16, 2010 at 5:53 PM, Florian > Pflug<fgp.phlo.org@gmail.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
В списке pgsql-hackers по дате отправления:
Предыдущее
От: Simon RiggsДата:
Сообщение: Re: Re: [COMMITTERS] pgsql: Make standby server continuously retry restoring the next WAL