Re: idea: allow AS label inside ROW constructor

On 10/23/2014 11:36 AM, David G Johnston wrote:
> 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.
>
>


You can already name the output of CTEs and in many cases subqueries, 
too. Maybe if you or someone gave a concrete example of something you 
can't do that this would enable I'd be more convinced.

cheers

andrew