From: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
---|---|
To: | Benedikt Grundmann <benedikt(dot)grundmann(at)gmail(dot)com> |
Cc: | PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Expression to construct a anonymous record with named columns? |
Date: | 2012-09-21 19:23:47 |
Message-ID: | CAHyXU0xHDZO6=0XjEEK-sQL-4Gbp2Gh-f-LZieZFdGEw=zWJnA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gauthier, Dave | 2012-09-21 19:38:24 | Re: 9.1 vs 8.4 performance |
Previous Message | Scott Marlowe | 2012-09-21 19:01:22 | Re: 9.1 vs 8.4 performance |