Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Sep 21, 2012 at 12:39 PM, Benedikt Grundmann
<benedikt.grundmann@xxxxxxxxx> wrote:
> On 21 September 2012 14:04, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
>>
>> On Fri, Sep 21, 2012 at 4:18 AM, Benedikt Grundmann
>> <benedikt.grundmann@xxxxxxxxx> wrote:
>> >
>> > On 21 September 2012 07:50, Alban Hertroys <haramrae@xxxxxxxxx> 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


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux