Обсуждение: 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.