Обсуждение: Expression to construct a anonymous record with named columns?

Hello,

Is there a way to construct write an expression that constructs a record with with named columns.  Specificially without the need for a corresponding named type.

That is

postgres=# select row(1, 2, 3);
   row  
---------
 (1,2,3)
(1 row)

Creates a unnamed record type.  And indeed it is for example not possible to expand it:

postgres=# select (row(1, 2, 3)).*;
ERROR:  record type has not been registered

On the other hand columns listed in a multi column select clause create a row type that is expandable and named:
postgres=# select ((bar.*).x).a from (select x from (select 1 as a, 2 as b) x) bar;
 a
---
 1
(1 row)

But it seems to not be possible to do so without a from clause:

postgres=# select ((select x from (select 1 as a, 2 as b) x)).a;
ERROR:  syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).a;
                                                          ^
postgres=# select ((select x from (select 1 as a, 2 as b) x)).*;
ERROR:  syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).*;

So named anonymous records / row types seem to be strangely second class.  Can somebody clarify the restrictions and rationale or even better show a way to do the equivalent of (made up syntax ahead):

select row(1 as a, 2 as b);

Cheers,

Bene

On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote:

> So named anonymous records / row types seem to be strangely second class.  Can somebody clarify the restrictions and
rationaleor even better show a way to do the equivalent of (made up syntax ahead): 
>
> select row(1 as a, 2 as b);

select * from (values (1, 2, 3)) a (a, b, c);

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.

On 21 September 2012 07:50, Alban Hertroys <haramrae@gmail.com> wrote:

On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann
<benedikt.grundmann@gmail.com> wrote:
>
> On 21 September 2012 07:50, Alban Hertroys <haramrae@gmail.com> wrote:
>>
>> On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote:
>>
>> > So named anonymous records / row types seem to be strangely second
>> > class.  Can somebody clarify the restrictions and rationale or even better
>> > show a way to do the equivalent of (made up syntax ahead):
>> >
>> > select row(1 as a, 2 as b);
>>
>> select * from (values (1, 2, 3)) a (a, b, c);
>>
> Thank you very much.  This is very interesting. However this again seems
> to be strangely limited, because I can neither extract a column from row
> that was constructed this way in a scalar position nor expand it:
>
> proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c));
>  ?column?
> ----------
>  (1,2,3)
> (1 row)

select * from (values (1, 2, 3)) x (a, b, c);
select x.* from (values (1, 2, 3)) x (a, b, c);

:-)

merlin

> select * from (values (1, 2, 3)) x (a, b, c);
> select x.* from (values (1, 2, 3)) x (a, b, c);

And more fun with values:
select a, b, c from (values (1, 2, 3), (4, 5, 6), (7, 8, 9)) x (a, b, c);

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On Fri, Sep 21, 2012 at 12:39 PM, Benedikt Grundmann
<benedikt.grundmann@gmail.com> wrote:
> On 21 September 2012 14:04, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>> On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann
>> <benedikt.grundmann@gmail.com> wrote:
>> >
>> > On 21 September 2012 07:50, Alban Hertroys <haramrae@gmail.com> wrote:
>> >>
>> >> On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote:
>> >>
>> >> > So named anonymous records / row types seem to be strangely second
>> >> > class.  Can somebody clarify the restrictions and rationale or even
>> >> > better
>> >> > show a way to do the equivalent of (made up syntax ahead):
>> >> >
>> >> > select row(1 as a, 2 as b);
>> >>
>> >> select * from (values (1, 2, 3)) a (a, b, c);
>> >>
>> > Thank you very much.  This is very interesting. However this again seems
>> > to be strangely limited, because I can neither extract a column from row
>> > that was constructed this way in a scalar position nor expand it:
>> >
>> > proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c));
>> >  ?column?
>> > ----------
>> >  (1,2,3)
>> > (1 row)
>>
>> select * from (values (1, 2, 3)) x (a, b, c);
>> select x.* from (values (1, 2, 3)) x (a, b, c);
>>
>> :-)
>>
> I guess I'm not expressing very well what I mean.  What you wrote works just
> fine but it only works by introducing a from clause.  Where as a row
> expression can be used in scalar position without the need for a from
> clause:
>
> select row(1, 2);

solutions i use:
*) cast to defined type
postgres=# create type foo as (a int, b int);
postgres=# select (row(1,2)::foo).*;
 a | b
---+---
 1 | 2

*) hstore:
postgres=# select avals(hstore(row(1,2)));

*) textual manipulation (most fragile)
select * from regexp_split_to_array(row(1,2)::text, ',');

merlin

On Fri, Sep 21, 2012 at 2:23 PM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Sep 21, 2012 at 12:39 PM, Benedikt Grundmann
> <benedikt.grundmann@gmail.com> wrote:
>> On 21 September 2012 14:04, Merlin Moncure <mmoncure@gmail.com> wrote:
>>>
>>> On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann
>>> <benedikt.grundmann@gmail.com> wrote:
>>> >
>>> > On 21 September 2012 07:50, Alban Hertroys <haramrae@gmail.com> wrote:
>>> >>
>>> >> On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote:
>>> >>
>>> >> > So named anonymous records / row types seem to be strangely second
>>> >> > class.  Can somebody clarify the restrictions and rationale or even
>>> >> > better
>>> >> > show a way to do the equivalent of (made up syntax ahead):
>>> >> >
>>> >> > select row(1 as a, 2 as b);
>>> >>
>>> >> select * from (values (1, 2, 3)) a (a, b, c);
>>> >>
>>> > Thank you very much.  This is very interesting. However this again seems
>>> > to be strangely limited, because I can neither extract a column from row
>>> > that was constructed this way in a scalar position nor expand it:
>>> >
>>> > proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c));
>>> >  ?column?
>>> > ----------
>>> >  (1,2,3)
>>> > (1 row)
>>>
>>> select * from (values (1, 2, 3)) x (a, b, c);
>>> select x.* from (values (1, 2, 3)) x (a, b, c);
>>>
>>> :-)
>>>
>> I guess I'm not expressing very well what I mean.  What you wrote works just
>> fine but it only works by introducing a from clause.  Where as a row
>> expression can be used in scalar position without the need for a from
>> clause:
>>
>> select row(1, 2);
>
> solutions i use:
> *) cast to defined type
> postgres=# create type foo as (a int, b int);
> postgres=# select (row(1,2)::foo).*;
>  a | b
> ---+---
>  1 | 2
>
> *) hstore:
> postgres=# select avals(hstore(row(1,2)));
>
> *) textual manipulation (most fragile)
> select * from regexp_split_to_array(row(1,2)::text, ',');
>
> merlin

also, for recent postgres (9.2, or 9.1 with the extension), you can
use the row_to_json function and deal with the output that way (either
on the client side, or with the up'n'coming pl/v8).

merlin