Обсуждение: Proposal: casts row to array and array to row
Hello A current limits of dynamic work with row types in PL/pgSQL can be decreased with a possible casts between rows and arrays. Now we have a lot of tools for arrays, and these tools should be used for rows too. postgres=# \d mypoint Composite type "public.mypoint"Column │ Type │ Modifiers ────────┼─────────┼───────────a │ integer │b │ integer │ postgres=# select cast(rmypoint '(10,20) as int[]); array ────────────{10,20} (1 row) postgres=# select cast(ARRAY[10,20] AS mypoint);mypoint ─────────(10,20) (1 row) What do you think about this idea? Regards Pavel Stehule
On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > A current limits of dynamic work with row types in PL/pgSQL can be > decreased with a possible casts between rows and arrays. Now we have a > lot of tools for arrays, and these tools should be used for rows too. > > postgres=# \d mypoint > Composite type "public.mypoint" > Column │ Type │ Modifiers > ────────┼─────────┼─────────── > a │ integer │ > b │ integer │ > > postgres=# select cast(rmypoint '(10,20) as int[]); > array > ──────────── > {10,20} > (1 row) > > postgres=# select cast(ARRAY[10,20] AS mypoint); > mypoint > ───────── > (10,20) > (1 row) > > What do you think about this idea? Well, a ROW can contain values of different types; an ARRAY can't. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
2011/10/11 Robert Haas <robertmhaas@gmail.com>: > On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> Hello >> >> A current limits of dynamic work with row types in PL/pgSQL can be >> decreased with a possible casts between rows and arrays. Now we have a >> lot of tools for arrays, and these tools should be used for rows too. >> >> postgres=# \d mypoint >> Composite type "public.mypoint" >> Column │ Type │ Modifiers >> ────────┼─────────┼─────────── >> a │ integer │ >> b │ integer │ >> >> postgres=# select cast(rmypoint '(10,20) as int[]); >> array >> ──────────── >> {10,20} >> (1 row) >> >> postgres=# select cast(ARRAY[10,20] AS mypoint); >> mypoint >> ───────── >> (10,20) >> (1 row) >> >> What do you think about this idea? > > Well, a ROW can contain values of different types; an ARRAY can't. yes, I know - but it should be problem only in few cases - when is not possible to cast a row field to array field. This is based on user knowledge - it has to choose a adequate array type sometimes he can use a numeric or int array, sometimes he have to select text array. Target type is selected by user, and cast fail when conversion is not possible. Pavel > > -- > Robert Haas > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
> > sometimes he can use a numeric or int array, sometimes he have to > select text array. Target type is selected by user, and cast fail when > conversion is not possible. > using a some selected type (for array field) allows a processing in plpgsql. motivation for this feature is simplification and speedup similar requests like this http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7722575#7722575 Pavel
Hi, 2011-10-11 14:23 keltezéssel, Robert Haas írta: > On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule<pavel.stehule@gmail.com> wrote: >> Hello >> >> A current limits of dynamic work with row types in PL/pgSQL can be >> decreased with a possible casts between rows and arrays. Now we have a >> lot of tools for arrays, and these tools should be used for rows too. >> >> postgres=# \d mypoint >> Composite type "public.mypoint" >> Column │ Type │ Modifiers >> ────────┼─────────┼─────────── >> a │ integer │ >> b │ integer │ >> >> postgres=# select cast(rmypoint '(10,20) as int[]); >> array >> ──────────── >> {10,20} >> (1 row) >> >> postgres=# select cast(ARRAY[10,20] AS mypoint); >> mypoint >> ───────── >> (10,20) >> (1 row) >> >> What do you think about this idea? > Well, a ROW can contain values of different types; an ARRAY can't. this reminds me that recently I thought about making anyelement a real type. anyelement[] would allow you to have different types in the same array. The real type OID and the data both would be stored and anyelement to cstring would reveal both in e.g. 'oid,value_converted_by_outfunc' format. The anyelement to real type and any type to anyelement conversion would be painless. The problem is that anyelement (when the underlying type in not text) to text conversion would be ambiguous and give different answers: anyelement -> cstring -> text gives 'oid,value_converted_by_outfunc' anyelement -> real type -> cstring -> text gives 'value_converted_by_outfunc' Stupid idea. Best regards, Zoltán Böszörményi -- ---------------------------------- Zoltán Böszörményi Cybertec Schönig& Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria Web: http://www.postgresql-support.de http://www.postgresql.at/
2011/10/11 Boszormenyi Zoltan <zb@cybertec.at>: > Hi, > > 2011-10-11 14:23 keltezéssel, Robert Haas írta: >> >> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule<pavel.stehule@gmail.com> >> wrote: >>> >>> Hello >>> >>> A current limits of dynamic work with row types in PL/pgSQL can be >>> decreased with a possible casts between rows and arrays. Now we have a >>> lot of tools for arrays, and these tools should be used for rows too. >>> >>> postgres=# \d mypoint >>> Composite type "public.mypoint" >>> Column │ Type │ Modifiers >>> ────────┼─────────┼─────────── >>> a │ integer │ >>> b │ integer │ >>> >>> postgres=# select cast(rmypoint '(10,20) as int[]); >>> array >>> ──────────── >>> {10,20} >>> (1 row) >>> >>> postgres=# select cast(ARRAY[10,20] AS mypoint); >>> mypoint >>> ───────── >>> (10,20) >>> (1 row) >>> >>> What do you think about this idea? >> >> Well, a ROW can contain values of different types; an ARRAY can't. > > this reminds me that recently I thought about making anyelement > a real type. anyelement[] would allow you to have different types in > the same array. The real type OID and the data both would be stored and > anyelement to cstring would reveal both in e.g. > 'oid,value_converted_by_outfunc' > format. The anyelement to real type and any type to anyelement conversion > would be painless. > > The problem is that anyelement (when the underlying type in not text) to > text > conversion would be ambiguous and give different answers: > anyelement -> cstring -> text gives 'oid,value_converted_by_outfunc' > anyelement -> real type -> cstring -> text gives > 'value_converted_by_outfunc' > Stupid idea. > it's near a "variant" datatype - Some times I though about some like "late binding" - but my proposal is significantly simpler, because it doesn't play with automatic choose of common subtype. It is based on user choose. Regards Pavel > Best regards, > Zoltán Böszörményi > > -- > ---------------------------------- > Zoltán Böszörményi > Cybertec Schönig& Schönig GmbH > Gröhrmühlgasse 26 > A-2700 Wiener Neustadt, Austria > Web: http://www.postgresql-support.de > http://www.postgresql.at/ > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > Hello > > A current limits of dynamic work with row types in PL/pgSQL can be > decreased with a possible casts between rows and arrays. Now we have a > lot of tools for arrays, and these tools should be used for rows too. > > postgres=# \d mypoint > Composite type "public.mypoint" > Column │ Type │ Modifiers > ────────┼─────────┼─────────── > a │ integer │ > b │ integer │ > > postgres=# select cast(rmypoint '(10,20) as int[]); > array > ──────────── > {10,20} > (1 row) > > postgres=# select cast(ARRAY[10,20] AS mypoint); > mypoint > ───────── > (10,20) > (1 row) > > What do you think about this idea? Not sure what it buys you over the syntax we already have: select row(foo[1], bar[2]); select array[(bar).a, (bar).b]; Also, in my coding of composite types, homogeneously typed rows don't really come up that often... merlin
2011/10/11 Merlin Moncure <mmoncure@gmail.com>: > On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> Hello >> >> A current limits of dynamic work with row types in PL/pgSQL can be >> decreased with a possible casts between rows and arrays. Now we have a >> lot of tools for arrays, and these tools should be used for rows too. >> >> postgres=# \d mypoint >> Composite type "public.mypoint" >> Column │ Type │ Modifiers >> ────────┼─────────┼─────────── >> a │ integer │ >> b │ integer │ >> >> postgres=# select cast(rmypoint '(10,20) as int[]); >> array >> ──────────── >> {10,20} >> (1 row) >> >> postgres=# select cast(ARRAY[10,20] AS mypoint); >> mypoint >> ───────── >> (10,20) >> (1 row) >> >> What do you think about this idea? > > Not sure what it buys you over the syntax we already have: > > select row(foo[1], bar[2]); > select array[(bar).a, (bar).b]; You can do it manually for known combinations of rowtype and arraytype. But proposed casts do it generally - what has sense mainly for plpgsql functions or some sql functions. > > Also, in my coding of composite types, homogeneously typed rows don't > really come up that often... you can use everywhere text type. When I wrote http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7722575#7722575 then I had to do lot of string operations. Proposed casts significantly do this simply - and it is enought general for general usage. Pavel > > merlin >
On Tue, Oct 11, 2011 at 8:18 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: > 2011/10/11 Merlin Moncure <mmoncure@gmail.com>: >> On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> Hello >>> >>> A current limits of dynamic work with row types in PL/pgSQL can be >>> decreased with a possible casts between rows and arrays. Now we have a >>> lot of tools for arrays, and these tools should be used for rows too. >>> >>> postgres=# \d mypoint >>> Composite type "public.mypoint" >>> Column │ Type │ Modifiers >>> ────────┼─────────┼─────────── >>> a │ integer │ >>> b │ integer │ >>> >>> postgres=# select cast(rmypoint '(10,20) as int[]); >>> array >>> ──────────── >>> {10,20} >>> (1 row) >>> >>> postgres=# select cast(ARRAY[10,20] AS mypoint); >>> mypoint >>> ───────── >>> (10,20) >>> (1 row) >>> >>> What do you think about this idea? >> >> Not sure what it buys you over the syntax we already have: >> >> select row(foo[1], bar[2]); >> select array[(bar).a, (bar).b]; > > You can do it manually for known combinations of rowtype and > arraytype. But proposed casts do it generally - what has sense mainly > for plpgsql functions or some sql functions. > >> >> Also, in my coding of composite types, homogeneously typed rows don't >> really come up that often... > > you can use everywhere text type. > > When I wrote http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7722575#7722575 > then I had to do lot of string operations. Proposed casts > significantly do this simply - and it is enought general for general > usage. How does your approach compare to hstore? hstore to me is just enhanced generic container type which supports the operations you are trying to do. It can be trivially (as of 9.0) moved in an out of both arrays and record types: postgres=# create type foo_t as (a int, b text, c float); CREATE TYPE postgres=# select row(1, 'abc', 1.0)::foo_t #= '"b"=>"def"';?column? -----------(1,def,1) (1 row) merlin
2011/10/11 Merlin Moncure <mmoncure@gmail.com>: > On Tue, Oct 11, 2011 at 8:18 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >> 2011/10/11 Merlin Moncure <mmoncure@gmail.com>: >>> On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>>> Hello >>>> >>>> A current limits of dynamic work with row types in PL/pgSQL can be >>>> decreased with a possible casts between rows and arrays. Now we have a >>>> lot of tools for arrays, and these tools should be used for rows too. >>>> >>>> postgres=# \d mypoint >>>> Composite type "public.mypoint" >>>> Column │ Type │ Modifiers >>>> ────────┼─────────┼─────────── >>>> a │ integer │ >>>> b │ integer │ >>>> >>>> postgres=# select cast(rmypoint '(10,20) as int[]); >>>> array >>>> ──────────── >>>> {10,20} >>>> (1 row) >>>> >>>> postgres=# select cast(ARRAY[10,20] AS mypoint); >>>> mypoint >>>> ───────── >>>> (10,20) >>>> (1 row) >>>> >>>> What do you think about this idea? >>> >>> Not sure what it buys you over the syntax we already have: >>> >>> select row(foo[1], bar[2]); >>> select array[(bar).a, (bar).b]; >> >> You can do it manually for known combinations of rowtype and >> arraytype. But proposed casts do it generally - what has sense mainly >> for plpgsql functions or some sql functions. >> >>> >>> Also, in my coding of composite types, homogeneously typed rows don't >>> really come up that often... >> >> you can use everywhere text type. >> >> When I wrote http://stackoverflow.com/questions/7711432/how-to-set-value-of-composite-variable-field-using-dynamic-sql/7722575#7722575 >> then I had to do lot of string operations. Proposed casts >> significantly do this simply - and it is enought general for general >> usage. > > How does your approach compare to hstore? hstore to me is just > enhanced generic container type which supports the operations you are > trying to do. It can be trivially (as of 9.0) moved in an out of both > arrays and record types: for replace some value is hstore ok, but cast to arrays is more general - you can do some tricks like table transposition, you can use a all tricks that we have for arrays. > > postgres=# create type foo_t as (a int, b text, c float); > CREATE TYPE > > postgres=# select row(1, 'abc', 1.0)::foo_t #= '"b"=>"def"'; > ?column? > ----------- > (1,def,1) > (1 row) In some future version I would to have a general indexable types - and then we can have a hash (hstore) in code, but casts to arrays or to hashs can be useful - in higher languages like plpgsql or sql. Pavel > > merlin >
Pavel Stehule <pavel.stehule@gmail.com> writes: > 2011/10/11 Robert Haas <robertmhaas@gmail.com>: >> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>> What do you think about this idea? It's a bad one. >> Well, a ROW can contain values of different types; an ARRAY can't. > yes, I know - but it should be problem only in few cases - when is not > possible to cast a row field to array field. This idea is basically the same as "data types don't matter", which is not SQL-ish and certainly not Postgres-ish. regards, tom lane
On Tue, Oct 11, 2011 at 8:36 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2011/10/11 Robert Haas <robertmhaas@gmail.com>: >>> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>>> What do you think about this idea? > > It's a bad one. > >>> Well, a ROW can contain values of different types; an ARRAY can't. > >> yes, I know - but it should be problem only in few cases - when is not >> possible to cast a row field to array field. > > This idea is basically the same as "data types don't matter", which is > not SQL-ish and certainly not Postgres-ish. hm. I agree, but if it were possible to create sql/plpgsql functions accepting 'record', then you could at least rig the cast in userland around hstore without resorting to hacky text manipulation and/or flattening the record to text before doing the operation. merlin
2011/10/11 Tom Lane <tgl@sss.pgh.pa.us>: > Pavel Stehule <pavel.stehule@gmail.com> writes: >> 2011/10/11 Robert Haas <robertmhaas@gmail.com>: >>> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote: >>>> What do you think about this idea? > > It's a bad one. > >>> Well, a ROW can contain values of different types; an ARRAY can't. > >> yes, I know - but it should be problem only in few cases - when is not >> possible to cast a row field to array field. > > This idea is basically the same as "data types don't matter", which is > not SQL-ish and certainly not Postgres-ish. This proposal is not about this. The data types are important and I don't propose a universal data type or some automatic datatype. Result of cast op has know type defined in planner time. Proposal is more about respect to datatypes than now. A some row based operations are based on serialization and deserialization to text. This is in PLPerl or PLpgSQL, on user level or system level. When you have to do some task, then you have to solve quoting, NULL replacement, ... Casts between array and rows just remove these ugly hacks - so work can be faster and more robust (without string operations (when is possible) and without quoting string ops at least). unfortunately I am not able to solve these requests on custom functions level, because I can't to specify a target type from function (I am missing a some polymorphic type like "anytype"). Regards Pavel Stehule > > regards, tom lane >
On Tue, Oct 11, 2011 at 10:40:26AM +0200, Pavel Stehule wrote: > What do you think about this idea? +1, belatedly. Having inherent casts to/from text since version 8.3 has smoothed out some aggravating corner cases. If the patch isn't invasive and the casts are all explicit-only, I anticipate a similar win. True, unlike any -> text, not every cast will actually work. However, the semantics are well-defined and incompatible choices can be detected just as readily as we do for incompatible casts among scalars.