Re: 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(dot)stehule(at)gmail(dot)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(at)gmail(dot)com>:
> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(dot)stehule(at)gmail(dot)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(at)cybertec(dot)at>:
> Hi,
>
> 2011-10-11 14:23 keltezéssel, Robert Haas írta:
>>
>> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule<pavel(dot)stehule(at)gmail(dot)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(at)postgresql(dot)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(dot)stehule(at)gmail(dot)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(at)gmail(dot)com>:
> On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(dot)stehule(at)gmail(dot)com> wrote:
> 2011/10/11 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>> On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2011/10/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
>> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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

2011/10/11 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Tue, Oct 11, 2011 at 8:18 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>> 2011/10/11 Merlin Moncure <mmoncure(at)gmail(dot)com>:
>>> On Tue, Oct 11, 2011 at 3:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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
>

On Tue, Oct 11, 2011 at 8:36 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2011/10/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
>>> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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(at)sss(dot)pgh(dot)pa(dot)us>:
> Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> writes:
>> 2011/10/11 Robert Haas <robertmhaas(at)gmail(dot)com>:
>>> On Tue, Oct 11, 2011 at 4:40 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.