Re: Expression to construct a anonymous record with named columns?

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