Обсуждение: Expression to construct a anonymous record with named columns?
Hello,
Is there a way to construct write an expression that constructs a record with with named columns. Specificially without the need for a corresponding named type.
That is
postgres=# select row(1, 2, 3);
row
---------
(1,2,3)
(1 row)
Creates a unnamed record type. And indeed it is for example not possible to expand it:
postgres=# select (row(1, 2, 3)).*;
ERROR: record type has not been registered
On the other hand columns listed in a multi column select clause create a row type that is expandable and named:
postgres=# select ((bar.*).x).a from (select x from (select 1 as a, 2 as b) x) bar;
a
---
1
(1 row)
But it seems to not be possible to do so without a from clause:
postgres=# select ((select x from (select 1 as a, 2 as b) x)).a;
ERROR: syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).a;
^
postgres=# select ((select x from (select 1 as a, 2 as b) x)).*;
ERROR: syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).*;
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);
Cheers,
Bene
Is there a way to construct write an expression that constructs a record with with named columns. Specificially without the need for a corresponding named type.
That is
postgres=# select row(1, 2, 3);
row
---------
(1,2,3)
(1 row)
Creates a unnamed record type. And indeed it is for example not possible to expand it:
postgres=# select (row(1, 2, 3)).*;
ERROR: record type has not been registered
On the other hand columns listed in a multi column select clause create a row type that is expandable and named:
postgres=# select ((bar.*).x).a from (select x from (select 1 as a, 2 as b) x) bar;
a
---
1
(1 row)
But it seems to not be possible to do so without a from clause:
postgres=# select ((select x from (select 1 as a, 2 as b) x)).a;
ERROR: syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).a;
^
postgres=# select ((select x from (select 1 as a, 2 as b) x)).*;
ERROR: syntax error at or near "."
LINE 1: select ((select x from (select 1 as a, 2 as b) x)).*;
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);
Cheers,
Bene
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 rationaleor 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); Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling.
On 21 September 2012 07:50, Alban Hertroys <haramrae@gmail.com> wrote:
On 20 Sep 2012, at 20:36, Benedikt Grundmann wrote:select * from (values (1, 2, 3)) a (a, b, c);
> 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);
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)
proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)).*;
ERROR: syntax error at or near "."
LINE 1: select (select x from (values (1, 2, 3)) x (a, b, c)).*;
^
proddb_testing=# select (select x from (values (1, 2, 3)) x (a, b, c)).a;
ERROR: syntax error at or near "."
LINE 1: select (select x from (values (1, 2, 3)) x (a, b, c)).a;
Any more insights?
Thanks,
bene
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); :-) merlin
> select * from (values (1, 2, 3)) x (a, b, c); > select x.* from (values (1, 2, 3)) x (a, b, c); And more fun with values: select a, b, c from (values (1, 2, 3), (4, 5, 6), (7, 8, 9)) x (a, b, c); -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
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
On Fri, Sep 21, 2012 at 2:23 PM, Merlin Moncure <mmoncure@gmail.com> wrote: > 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 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