Thread: proposal: auxiliary functions for record type
Hello I wrote a few functions for record type - record_expand, record_get_fields, record_get_field, record_set_fields. A usage of this functions is described in my blog http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html Do you think, so these functions can be in core? These are relative strong and enough general with zero dependency. Sure, these functions are not defined in ANSI SQL. Regards Pavel Stehule
Pavel Stehule wrote: > Hello > > I wrote a few functions for record type - record_expand, > record_get_fields, record_get_field, record_set_fields. > > A usage of this functions is described in my blog > http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html > > Do you think, so these functions can be in core? These are relative > strong and enough general with zero dependency. Sure, these functions > are not defined in ANSI SQL. > > Regards > > Pavel Stehule That looks good in principle. I see it as being valuable and important that users can define generic relational operators/functions, meaning ones that can work with any relations like built-ins can, and the ability to iterate over record fields, or at least introspect a relation to see what fields it has, is a good foundation to support this. -- Darren Duncan
On Dec11, 2010, at 06:20 , Pavel Stehule wrote: > I wrote a few functions for record type - record_expand, > record_get_fields, record_get_field, record_set_fields. Just FYI, I've created something similar a while ago. The code can be found at https://github.com/fgp/pg_record_inspect The main difference seems to be that my code allows you to retrieve fields from a record without casting it to text. As aconsequence, however, it lacks the set-returning functions that your's provides. It also doesn't provide a way to modifyfields. Still, maybe we could join efforts here? I certain would *love* to see something like this either as a contrib module orin core. best regards, Florian Pflug
2010/12/11 Florian Pflug <fgp@phlo.org>: > On Dec11, 2010, at 06:20 , Pavel Stehule wrote: >> I wrote a few functions for record type - record_expand, >> record_get_fields, record_get_field, record_set_fields. > > Just FYI, I've created something similar a while ago. The code can be found at > https://github.com/fgp/pg_record_inspect > > The main difference seems to be that my code allows you to retrieve fields from a record without casting it to text. Asa consequence, however, it lacks the set-returning functions that your's provides. It also doesn't provide a way to modifyfields. Casting to text is necessary for PL/pgSQL. I am not happy from this, but there are not other way than using a common type - text - because you don't know a target type. > > Still, maybe we could join efforts here? I certain would *love* to see something like this either as a contrib module orin core. > I'll look on your module, Regards Pavel Stehule > best regards, > Florian Pflug > >
On Dec11, 2010, at 16:03 , Pavel Stehule wrote: > 2010/12/11 Florian Pflug <fgp@phlo.org>: >> On Dec11, 2010, at 06:20 , Pavel Stehule wrote: >>> I wrote a few functions for record type - record_expand, >>> record_get_fields, record_get_field, record_set_fields. >> >> Just FYI, I've created something similar a while ago. The code can be found at >> https://github.com/fgp/pg_record_inspect >> >> The main difference seems to be that my code allows you to retrieve fields from a record without casting it to text. Asa consequence, however, it lacks the set-returning functions that your's provides. It also doesn't provide a way to modifyfields. > > Casting to text is necessary for PL/pgSQL. I am not happy from this, > but there are not other way than using a common type - text - because > you don't know a target type. I use the anyarray/anyelement machinery to cheat there, at least a bit. My function fieldvalue() returns anyelement and takesa parameter <defval> of type anyelement, which serves two purposes. First, by virtue of the anyelement machinery, the return type of fieldvalue() is that of <defval>. If the actual type ofthe requested field matches that type, the value is returned. If they don't match, the parameter <coerce> decided whetherfieldvalue() tries to cast the value to the requested type, or simply raises an error. Second, to also give the *value*, not only the *type* of <defval> a meaning, it serves as the default return value. If requestedfield contains NULL, <defvalue> is returned instead. You are, of course, free to pass NULL for <defvalue> itselfto turn that mapping into a NOP. Note that the returned value's type is always the same as <defval>'s type, so the whole thing is perfectly type-safe fromthe point of view of the rest of the system. As long as you know all possible types than can appear in your record's fields, you can do in Pl/PgSQL something along theline of declarev_value_type1 type1;v_value_type2 type2;...v_value_typeN typeN; beginfor v_field in select * from fieldinfos(myrec) loop case when v_field.fieldtype = 'type1'::regtype then v_value_type1 := fieldvalue(myrec, NULL::type1, false); <Do something with v_value_type1> ... when v_field.fieldtype = 'typeN'::regtype then v_value_typeN := fieldvalue(myrec, NULL::typeN, false); <Do something with v_value_typeN> else raise exception 'Unexpected type % in record %', v_field.fieldtype,myrec; end case;end loop; end; It works pretty well for me... best regards, Florian Pflug
Hey Pavel,<br /><br />Everything looks fine, but I propose you to add record_set_field<br />just for convenience and as oppositeof record_get_field.<br /><br /><div class="gmail_quote">2010/12/11 Pavel Stehule <span dir="ltr"><<a href="mailto:pavel.stehule@gmail.com">pavel.stehule@gmail.com</a>></span><br/><blockquote class="gmail_quote" style="margin:0pt 0pt 0pt 0.8ex; border-left: 1px solid rgb(204, 204, 204); padding-left: 1ex;">Hello<br /><br /> I wrotea few functions for record type - record_expand,<br /> record_get_fields, record_get_field, record_set_fields.<br /><br/> A usage of this functions is described in my blog<br /><a href="http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html" target="_blank">http://okbob.blogspot.com/2010/12/iteration-over-record-in-plpgsql.html</a><br/><br /> Do you think, so thesefunctions can be in core? These are relative<br /> strong and enough general with zero dependency. Sure, these functions<br/> are not defined in ANSI SQL.<br /><br /> Regards<br /><br /> Pavel Stehule<br /><font color="#888888"><br/> --<br /> Sent via pgsql-hackers mailing list (<a href="mailto:pgsql-hackers@postgresql.org">pgsql-hackers@postgresql.org</a>)<br/> To make changes to your subscription:<br/><a href="http://www.postgresql.org/mailpref/pgsql-hackers" target="_blank">http://www.postgresql.org/mailpref/pgsql-hackers</a><br/></font></blockquote></div><br /><br clear="all"/><br />-- <br />// Dmitriy.<br /><br /><br />
2010/12/11 Florian Pflug <fgp@phlo.org>: > On Dec11, 2010, at 16:03 , Pavel Stehule wrote: >> 2010/12/11 Florian Pflug <fgp@phlo.org>: >>> On Dec11, 2010, at 06:20 , Pavel Stehule wrote: >>>> I wrote a few functions for record type - record_expand, >>>> record_get_fields, record_get_field, record_set_fields. >>> >>> Just FYI, I've created something similar a while ago. The code can be found at >>> https://github.com/fgp/pg_record_inspect >>> >>> The main difference seems to be that my code allows you to retrieve fields from a record without casting it to text.As a consequence, however, it lacks the set-returning functions that your's provides. It also doesn't provide a wayto modify fields. >> >> Casting to text is necessary for PL/pgSQL. I am not happy from this, >> but there are not other way than using a common type - text - because >> you don't know a target type. > > > I use the anyarray/anyelement machinery to cheat there, at least a bit. My function fieldvalue() returns anyelement andtakes a parameter <defval> of type anyelement, which serves two purposes. > > First, by virtue of the anyelement machinery, the return type of fieldvalue() is that of <defval>. If the actual type ofthe requested field matches that type, the value is returned. If they don't match, the parameter <coerce> decided whetherfieldvalue() tries to cast the value to the requested type, or simply raises an error. It same trick, that I use in record_set_fields. But I don't want to use it for reading of value. I don't like it. You don't need to know a value, you have to know a type - NULL::type. it is just not nice :). I though about it too, and maybe is a time for new polymorphic type "anytype" - and then you don't need to write a litte bit strange NULL::type it can be "fieldvalue(myrec, type1, false)" Regards Pavel Stehule > > Second, to also give the *value*, not only the *type* of <defval> a meaning, it serves as the default return value. Ifrequested field contains NULL, <defvalue> is returned instead. You are, of course, free to pass NULL for <defvalue> itselfto turn that mapping into a NOP. > > Note that the returned value's type is always the same as <defval>'s type, so the whole thing is perfectly type-safe fromthe point of view of the rest of the system. > > As long as you know all possible types than can appear in your record's fields, you can do in Pl/PgSQL something alongthe line of > declare > v_value_type1 type1; > v_value_type2 type2; > ... > v_value_typeN typeN; > begin > for v_field in select * from fieldinfos(myrec) loop > case > when v_field.fieldtype = 'type1'::regtype then > v_value_type1 := fieldvalue(myrec, NULL::type1, false); > <Do something with v_value_type1> > ... > when v_field.fieldtype = 'typeN'::regtype then > v_value_typeN := fieldvalue(myrec, NULL::typeN, false); > <Do something with v_value_typeN> > else raise exception 'Unexpected type % in record %', v_field.fieldtype, myrec; > end case; > end loop; > end; > > It works pretty well for me... > > best regards, > Florian Pflug > >
On Dec11, 2010, at 17:55 , Pavel Stehule wrote: > It same trick, that I use in record_set_fields. Oh, I see. Sorry, must haven missed that when I read your blog entry. > But I don't want to > use it for reading of value. I don't like it. You don't need to know a > value, you have to know a type - NULL::type. it is just not nice :). Well, no, it's not the most elegant API on earth, that's for sure. But I my opinion, it isn't so bad that it rectifies castingeverything to text. > I > though about it too, and maybe is a time for new polymorphic type > "anytype" - and then you don't need to write a litte bit strange > NULL::type > > it can be "fieldvalue(myrec, type1, false)" Hm, I don't think the ability to write just "type1" instead of "NULL::type1" is worth the necessary effort. If anything, I'd allow function to use anyelement as their return value *without* having any polymorphic arguments. You'dneed to surround calls to such function with a CAST() expression, unless something else determines the type. In pl/pgsql,for example, one could allow the CAST() to be skipped for assignment to variables. Then, you could writev_value:= fieldvalue(myrec, 'f1', true) That'd at least free you from having to specify the type in some cases. But still, even this seems to be a lot of effortfor quite little gain... best regards, Florian Pflug
2010/12/11 Florian Pflug <fgp@phlo.org>: > On Dec11, 2010, at 17:55 , Pavel Stehule wrote: >> It same trick, that I use in record_set_fields. > Oh, I see. Sorry, must haven missed that when I read your blog entry. > >> But I don't want to >> use it for reading of value. I don't like it. You don't need to know a >> value, you have to know a type - NULL::type. it is just not nice :). > Well, no, it's not the most elegant API on earth, that's for sure. But I my opinion, it isn't so bad that it rectifiescasting everything to text. yes, there are two no good solution now. > >> I >> though about it too, and maybe is a time for new polymorphic type >> "anytype" - and then you don't need to write a litte bit strange >> NULL::type >> >> it can be "fieldvalue(myrec, type1, false)" > Hm, I don't think the ability to write just "type1" instead of "NULL::type1" is worth the necessary effort. > > If anything, I'd allow function to use anyelement as their return value *without* having any polymorphic arguments. You'dneed to surround calls to such function with a CAST() expression, unless something else determines the type. In pl/pgsql,for example, one could allow the CAST() to be skipped for assignment to variables. Then, you could write > v_value := fieldvalue(myrec, 'f1', true) > yes, this is probably way and I like it. But now with zero support. a) there isn't mechanism for propagation target type from outer to function - function have to have to know target type b) plpgsql uses only IO cast - so values are translated to text and back everytime but CAST has own parser part and probably executor part too, so this is possible and too hard. so is possible to call: SELECT CAST(fieldvalue(myrec, 'f1', true) AS int) yes, this task should not be hard. But who will comunicate about this with Tom? Regards Pavel > That'd at least free you from having to specify the type in some cases. But still, even this seems to be a lot of effortfor quite little gain... > > best regards, > Florian Pflug > >
On Dec 11, 2010, at 9:24 AM, Florian Pflug wrote: > begin > for v_field in select * from fieldinfos(myrec) loop > case > when v_field.fieldtype = 'type1'::regtype then > v_value_type1 := fieldvalue(myrec, NULL::type1, false); > <Do something with v_value_type1> > ... > when v_field.fieldtype = 'typeN'::regtype then > v_value_typeN := fieldvalue(myrec, NULL::typeN, false); > <Do something with v_value_typeN> > else raise exception 'Unexpected type % in record %', v_field.fieldtype, myrec; > end case; > end loop; > end; All that casting looks horribly painful. :( This is a case where what we really need is a data type that can accept data of any type and tell you what the type actuallyis. Another place that could really use that are crosstabs, though that case is more complex in that you're dealingwith a recordset of fields and you don't know the type of any of them. I wonder if it would be possible to create such a type without major backend surgery... I'm thinking the type would havea structure that stores the OID of the original type and then stores the text representation of that type. CAST functionsto this type would know what the input data was and could store that information. The challenge is creating a wayto cast back to the original type... I don't see a way to do that with current infrastructure. -- Jim C. Nasby, Database Architect jim@nasby.net 512.569.9461 (cell) http://jim.nasby.net
Hello I looked on your module now and I am can't to say, what is better - both modules has little bit different target if I understand, and I don't know if they can be mixed. There are two strategies - rich contrib module or a few functions in core. Probably we can't to have a too rich API for record type like regexp has - transform result to table, to array, read values to array, to table, read tuple desc to array, to table, ... there should be one strategy - both strategies (based on array, based on tables) are legal. I prefer a table based solution, because I don't need a one "unnest", but other preferences are valid too. I dissatisfied with your design of explicit target type via unused value. I think, so we are not a infrastructure for it now - from my view is better to use a common type, that is text now. It's nothing new - plpgsql use it too. I see one well design of explicit target type based on polymorphic types that respect a PostgreSQL fmgr practice: We have to allow a polymorphic functions without polymorphic parameters. These functions shoud be designed to return value in "unknown" type format when this function has not outer information. This information can be passed in function context. When function context isn't null, then function has to read target type and should to return value in target type. Who can fill a function context? It is task for executor. And when CAST contains just function call, then we can recheck, if function is polymorphic, and if it is, then we can set function context to target type, and then we don't need to call a conversion function, because polymorphic function must returns data in correct format. So when polymorphic function is used without polymorphic parameters and without explicit CAST, then returns unknown. Without described functionality we can design a not polymorphic function, that can returns unknown type. When similar functionality will be implemented, then this function will be changed to polymorphic, but from user's perspective, there isn't a change. Regards Pavel Stehule 2010/12/11 Florian Pflug <fgp@phlo.org>: > On Dec11, 2010, at 17:55 , Pavel Stehule wrote: >> It same trick, that I use in record_set_fields. > Oh, I see. Sorry, must haven missed that when I read your blog entry. > >> But I don't want to >> use it for reading of value. I don't like it. You don't need to know a >> value, you have to know a type - NULL::type. it is just not nice :). > Well, no, it's not the most elegant API on earth, that's for sure. But I my opinion, it isn't so bad that it rectifiescasting everything to text. > >> I >> though about it too, and maybe is a time for new polymorphic type >> "anytype" - and then you don't need to write a litte bit strange >> NULL::type >> >> it can be "fieldvalue(myrec, type1, false)" > Hm, I don't think the ability to write just "type1" instead of "NULL::type1" is worth the necessary effort. > > If anything, I'd allow function to use anyelement as their return value *without* having any polymorphic arguments. You'dneed to surround calls to such function with a CAST() expression, unless something else determines the type. In pl/pgsql,for example, one could allow the CAST() to be skipped for assignment to variables. Then, you could write > v_value := fieldvalue(myrec, 'f1', true) > > That'd at least free you from having to specify the type in some cases. But still, even this seems to be a lot of effortfor quite little gain... > > best regards, > Florian Pflug > >
On Dec12, 2010, at 00:19 , Pavel Stehule wrote: > I prefer a table based > solution, because I don't need a one "unnest", but other preferences > are valid too. That's fine with me. > I dissatisfied with your design of explicit target type > via unused value. I think, so we are not a infrastructure for it now > - from my view is better to use a common type, that is text now. It's > nothing new - plpgsql use it too. Sorry, I can't follow you here. Where does plpgsql use text as "common" type? > I see one well design of explicit target type based on polymorphic > types that respect a PostgreSQL fmgr practice: > > We have to allow a polymorphic functions without polymorphic > parameters. These functions shoud be designed to return value in > "unknown" type format when this function has not outer information. I don't think "unknown" is the right type for that. As far as I known, "unknown" is still a textual type, used to have sometype to assign to string literals during parsing when no better type can be inferred. > This information can be passed in function context. When function > context isn't null, then function has to read target type and should > to return value in target type. Who can fill a function context? It is > task for executor. And when CAST contains just function call, then we > can recheck, if function is polymorphic, and if it is, then we can set > function context to target type, and then we don't need to call a > conversion function, because polymorphic function must returns data in > correct format. The main difficulty is that currently types are assigned in a bottom-up fashion as far as I know. To make functions witha polymorphic return value, but without polymorphic arguments work, you need to assign the return type in a top-downfashion (It depends on where to value *goes*, not where it *comes from*). That seems like a rather huge change andhas the potential to complicate quite a few other parts, most notably function lookup/resolution. Plus, the general case where type information must bubble up more than one level seems pretty much intractable, as it'd requirea full-blown type inference algorithm like ML or Haskell. Not a place where we want to go, I believe. The restricted case, on the other hand, brings very little benefit compared to the dummy-parameter approach. Yeah, "<polymorphicfunction>()::type" may look a bit cleaner than "<polymorphic function>(NULL::type)", but thats about is. It'sonly assignments in pl/pgsql which really benefit, since you'd be able to leave out the type completely, writing simply"v_value := <polymorphic_function>()". Does that really warrant the effort that'd be involved? > Without described functionality we can design a not polymorphic > function, that can returns unknown type. When similar functionality > will be implemented, then this function will be changed to > polymorphic, but from user's perspective, there isn't a change. I don't really understand why you resist the idea of a dummy parameter so much. It might not be pretty, but is it bad enoughto rectify putting in all this work? Plus, the whole record-manipulation stuff isn't going to win a beauty contestanytime soon. But it's better than nothing, so as long as it's reasonably efficient I think one can live with a fewwarts on the API. best regards, Florian Pflug
2010/12/12 Florian Pflug <fgp@phlo.org>: > On Dec12, 2010, at 00:19 , Pavel Stehule wrote: >> I prefer a table based >> solution, because I don't need a one "unnest", but other preferences >> are valid too. > That's fine with me. > >> I dissatisfied with your design of explicit target type >> via unused value. I think, so we are not a infrastructure for it now >> - from my view is better to use a common type, that is text now. It's >> nothing new - plpgsql use it too. > Sorry, I can't follow you here. Where does plpgsql use text as "common" type? plpgsql uses only IO casts. So inside assign statement is checked target type and real type. But this checking is late! I did a patch for early conversion to target type (in plan), but this patch was rejected. So actually, there isn't available information about target type in expression - and probably will be - from compatibility reasons. For example: when target variable is int, but you used a numeric constant, then any assignment does a IO cast from num to int. > >> I see one well design of explicit target type based on polymorphic >> types that respect a PostgreSQL fmgr practice: >> >> We have to allow a polymorphic functions without polymorphic >> parameters. These functions shoud be designed to return value in >> "unknown" type format when this function has not outer information. > I don't think "unknown" is the right type for that. As far as I known, "unknown" is still a textual type, used to havesome type to assign to string literals during parsing when no better type can be inferred. > >> This information can be passed in function context. When function >> context isn't null, then function has to read target type and should >> to return value in target type. Who can fill a function context? It is >> task for executor. And when CAST contains just function call, then we >> can recheck, if function is polymorphic, and if it is, then we can set >> function context to target type, and then we don't need to call a >> conversion function, because polymorphic function must returns data in >> correct format. > The main difficulty is that currently types are assigned in a bottom-up fashion as far as I know. To make functions witha polymorphic return value, but without polymorphic arguments work, you need to assign the return type in a top-downfashion (It depends on where to value *goes*, not where it *comes from*). That seems like a rather huge change andhas the potential to complicate quite a few other parts, most notably function lookup/resolution. I don't think: a) the place where we don't know a target type is limited only to first outer cast b) I didn't defined polymorphic function without polymorphic parameters (PFWPP) as absolutly undescribed - it returns a "unknown" or "text" in default. There isn't problem to search a this function - and isn't a problem for later work, so this function returns "text", because first outer cast ensure transformation to correct type. c) when function is called without outer cast then it runs too - but there will be one IO cast more. some alchemy with function descriptor ale used now too - when default parameters are used. > > Plus, the general case where type information must bubble up more than one level seems pretty much intractable, as it'drequire a full-blown type inference algorithm like ML or Haskell. Not a place where we want to go, I believe. > > The restricted case, on the other hand, brings very little benefit compared to the dummy-parameter approach. Yeah, "<polymorphicfunction>()::type" may look a bit cleaner than "<polymorphic function>(NULL::type)", but thats about is. It'sonly assignments in pl/pgsql which really benefit, since you'd be able to leave out the type completely, writing simply"v_value := <polymorphic_function>()". Does that really warrant the effort that'd be involved? > >> Without described functionality we can design a not polymorphic >> function, that can returns unknown type. When similar functionality >> will be implemented, then this function will be changed to >> polymorphic, but from user's perspective, there isn't a change. > I don't really understand why you resist the idea of a dummy parameter so much. It might not be pretty, but is it bad enoughto rectify putting in all this work? Plus, the whole record-manipulation stuff isn't going to win a beauty contestanytime soon. But it's better than nothing, so as long as it's reasonably efficient I think one can live with a fewwarts on the API. I wrote it. In this case, you don't need to know a value, you have to work with type. So using a typed null isn't intuitive and it isn't nice - for me - too ugly for in general module. I know, so PFWPP functions need a lot of coding without sure result, and it's reason, why I didn't used it and why I use a "text" type. And I have a other reason for - I expect so there is bigger probability to iterate over different type's fields, so coercion to one target type isn't available in one path. Using a more path (like you are showed in code) is relative "high" technique - there is a few developers who can use it and understand. So my design is oriented more to typical programmers who doesn't know a plpgsql implementation details. These people can use a some strange solution based on dynamic sql and are happy, so they has a workable code. It's question what design is more useful - I don't know. But I am strong against to add some strange API to pg. Regards Pavel > > best regards, > Florian Pflug > >
2010/12/12 Florian Pflug <fgp@phlo.org>:
> On Dec12, 2010, at 00:19 , Pavel Stehule wrote:
>> I prefer a table based
>> solution, because I don't need a one "unnest", but other preferences
>> are valid too.
> That's fine with me.
>
>> I dissatisfied with your design of explicit target type
>> via unused value. I think, so we are not a infrastructure for it now
>> - from my view is better to use a common type, that is text now. It's
>> nothing new - plpgsql use it too.
> Sorry, I can't follow you here. Where does plpgsql use text as "common" type?
>
>> I see one well design of explicit target type based on polymorphic
>> types that respect a PostgreSQL fmgr practice:
>>
>> We have to allow a polymorphic functions without polymorphic
>> parameters. These functions shoud be designed to return value in
>> "unknown" type format when this function has not outer information.
> I don't think "unknown" is the right type for that. As far as I known, "unknown" is still a textual type, used to
havesome type to assign to string literals during parsing when no better type can be inferred.
>
>> This information can be passed in function context. When function
>> context isn't null, then function has to read target type and should
>> to return value in target type. Who can fill a function context? It is
>> task for executor. And when CAST contains just function call, then we
>> can recheck, if function is polymorphic, and if it is, then we can set
>> function context to target type, and then we don't need to call a
>> conversion function, because polymorphic function must returns data in
>> correct format.
> The main difficulty is that currently types are assigned in a bottom-up fashion as far as I know. To make functions
witha polymorphic return value, but without polymorphic arguments work, you need to assign the return type in a
top-downfashion (It depends on where to value *goes*, not where it *comes from*). That seems like a rather huge change
andhas the potential to complicate quite a few other parts, most notably function lookup/resolution.
>
> Plus, the general case where type information must bubble up more than one level seems pretty much intractable, as
it'drequire a full-blown type inference algorithm like ML or Haskell. Not a place where we want to go, I believe.
>
> The restricted case, on the other hand, brings very little benefit compared to the dummy-parameter approach. Yeah,
"<polymorphicfunction>()::type" may look a bit cleaner than "<polymorphic function>(NULL::type)", but thats about is.
It'sonly assignments in pl/pgsql which really benefit, since you'd be able to leave out the type completely, writing
simply"v_value := <polymorphic_function>()". Does that really warrant the effort that'd be involved?
There is a second possibility - and hardly simpler. We can use a
specialised statement with own parser/executor node. Then
implementation should be really simply
syntax:
EXTRACT_VALUE(expr1 FROM expr2 AS typename) ... RETURNS typename
expr1 ... result must be converted to text .. fieldname
expr2 ... result must be composite type
disadvantage - EXTRACT_VALUE must be a keyword
advantage - simple implementation, available for all environments, readable
var := EXTRACT_VALUE('f1' FROM myrec AS int);
note: name for this statement isn't important now, can be EXTRACT_FIELD, ...
comments, ideas?
Regards
Pavel Stehule
On Dec13, 2010, at 08:23 , Pavel Stehule wrote: > There is a second possibility - and hardly simpler. We can use a > specialised statement with own parser/executor node. Then > implementation should be really simply > > syntax: > > EXTRACT_VALUE(expr1 FROM expr2 AS typename) ... RETURNS typename In principle, that looks nice. I'm fairly certain, however, that any proposal that adds special syntax just for this will very like get shot down quickly, so I don't really want to go there. However, I've just had an epiphany I think. Why not copy a page out of dblink's book, and make it select * from record_get(<record>, <field1>, ..., <fieldn>) as (field varchar, value <type>) The result would be field | value (varchar) | (<type>) -------------------- field1 | value1 ... fieldn | valuen If value1 ... value_n are able to be casted to <type>, and an error otherwise. If dblink is able to pull that off, so should we, or am I missing something? best regards, Florian Pflug
2010/12/16 Florian Pflug <fgp@phlo.org>: > On Dec13, 2010, at 08:23 , Pavel Stehule wrote: >> There is a second possibility - and hardly simpler. We can use a >> specialised statement with own parser/executor node. Then >> implementation should be really simply >> >> syntax: >> >> EXTRACT_VALUE(expr1 FROM expr2 AS typename) ... RETURNS typename > > > In principle, that looks nice. I'm fairly certain, however, that > any proposal that adds special syntax just for this will very like > get shot down quickly, so I don't really want to go there. > > However, I've just had an epiphany I think. Why not copy a page out > of dblink's book, and make it > > select * from record_get(<record>, <field1>, ..., <fieldn>) as (field varchar, value <type>) > > The result would be > > field | value > (varchar) | (<type>) > -------------------- > field1 | value1 > ... > fieldn | valuen > > If value1 ... value_n are able to be casted to <type>, and an error otherwise. > > If dblink is able to pull that off, so should we, or am I missing > something? no, it could work. I am not sure if it has a necessary ergonomic (I don't like to write a record descriptor), but I think so we found a combination, that can work. What about two functions: record_get(rec, field, field, field) that returns a (text, text) and record_get_raw(rec, field, field, field) that returns a some common type and should be enhanced with record descriptor ? Regards Pavel > > best regards, > Florian Pflug > >