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 2:23 PM, Merlin Moncure <mmoncure@xxxxxxxxx> wrote:
> 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

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


-- 
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