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

On Fri, Sep 21, 2012 at 2:23 PM, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Fri, Sep 21, 2012 at 12:39 PM, Benedikt Grundmann
> <benedikt(dot)grundmann(at)gmail(dot)com> wrote:
>> On 21 September 2012 14:04, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
>>>
>>> On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann
>>> <benedikt(dot)grundmann(at)gmail(dot)com> wrote:
>>> >
>>> > On 21 September 2012 07:50, Alban Hertroys <haramrae(at)gmail(dot)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