Re: idea: allow AS label inside ROW constructor

Andrew Dunstan wrote
> On 10/23/2014 09:57 AM, Florian Pflug wrote:
>> On Oct23, 2014, at 15:39 , Andrew Dunstan <

> andrew@

> > wrote:
>>> On 10/23/2014 09:27 AM, Merlin Moncure wrote:
>>>> On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule <

> pavel.stehule@

> > wrote:
>>>>> postgres=# select row_to_json(row(10 as A, row(30 as c, 20 AS B) as
>>>>> x));
>>>>> row_to_json
>>>>> ------------------------------
>>>>> {"a":10,"x":{"c":30,"b":20}}
>>>>> (1 row)
>>>>>
>>>> wow -- this is great. I'll take a a look.
>>>>
>>> Already in 9.4:
>>>
>>> andrew=# select
>>> json_build_object('a',10,'x',json_build_object('c',30,'b',20));
>>> json_build_object
>>> ----------------------------------------
>>> {"a" : 10, "x" : {"c" : 30, "b" : 20}}
>>> (1 row)
>>> So I'm not sure why we want another mechanism unless it's needed in some
>>> other context.
>> I've wanted to name the field of rows created with ROW() on more than
>> one occasion, quite independent from whether the resulting row is
>> converted
>> to JSON or not. And quite apart from usefulness, this is a matter of
>> orthogonality. If we have named fields in anonymous record types, we
>> should
>> provide a convenient way of specifying the field names.
>>
>> So to summarize, I think this is an excellent idea, json_build_object
>> non-withstanding.
>>
>
> Well, I think we need to see those other use cases. The only use case I
> recall seeing involves the already provided case of constructing JSON.

Even if it simply allows CTE and sibqueries to form anonymous record types
which can then be re-expanded in the outer layer for table-like final output
this feature would be useful. When working with wide tables and using
multiple aggregates and joins being able to avoid specifying individual
columns repeatedly is quite desirable.

It would be especially nice to not have to use "as" though, if the source
fields are already so named.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/idea-allow-AS-label-inside-ROW-constructor-tp5823954p5824045.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.