Dyamic updates of NEW with pl/pgsql
in [PgSql]
|
Prev: patch (for 9.1) string functions
Next: [HACKERS] Access violation from palloc, Visual Studio 2005, C-language function
From: Tom Lane on 15 Mar 2010 12:19 Merlin Moncure <mmoncure(a)gmail.com> writes: > On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >> If we make the implementation be such that "(rec->field)::foo" forces >> a runtime cast to foo (rather than throwing an error if it's not type >> foo already) > yeah...explicit cast should always do 'best effort' Probably so. But is it worth inventing some other notation that says "expect this field to be of type foo", with an error rather than runtime cast if it's not? If we go with treating the result of -> like UNKNOWN, then you wouldn't need that in cases where the parser guesses the right type. But there are going to be cases where you need to override the guess without necessarily wanting to buy into a forced conversion. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 15 Mar 2010 12:53 On Mon, Mar 15, 2010 at 12:19 PM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: > Merlin Moncure <mmoncure(a)gmail.com> writes: >> On Mon, Mar 15, 2010 at 11:37 AM, Tom Lane <tgl(a)sss.pgh.pa.us> wrote: >>> If we make the implementation be such that "(rec->field)::foo" forces >>> a runtime cast to foo (rather than throwing an error if it's not type >>> foo already) > >> yeah...explicit cast should always do 'best effort' > > Probably so. But is it worth inventing some other notation that says > "expect this field to be of type foo", with an error rather than runtime > cast if it's not? If we go with treating the result of -> like UNKNOWN, > then you wouldn't need that in cases where the parser guesses the right > type. But there are going to be cases where you need to override the > guess without necessarily wanting to buy into a forced conversion. Maybe. That behaves like oid vector to PQexecParams, right? Suggests a type but does not perform a cast. I see your point but I think it's going to go over the heads of most people...type association vs type coercion. Maybe instead you could just supply typeof function in order to provide very rigorous checking when wanted and presumably allow things like pointing the assignment at a special field. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Florian Pflug on 16 Mar 2010 17:53 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Merlin Moncure on 16 Mar 2010 23:08 On Tue, Mar 16, 2010 at 5:53 PM, Florian Pflug <fgp.phlo.org(a)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 :-). I think it's pretty cool. Is it safe to have the main functions immutable and not stable though? Is there any benefit missed by not going through pl/pgsql directly (I'm guessing maybe more elegant caching)? 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? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Florian Pflug on 17 Mar 2010 09:12
On 17.03.10 4:08 , Merlin Moncure wrote: > On Tue, Mar 16, 2010 at 5:53 PM, Florian > Pflug<fgp.phlo.org(a)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 -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers |