Re: idea: allow AS label inside ROW constructor

On Thu, Oct 23, 2014 at 8:51 AM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

>
> 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 &lt;
>>>>
>>> andrew@
>>> &gt; wrote:
>>>
>>>> On 10/23/2014 09:27 AM, Merlin Moncure wrote:
>>>>>
>>>>>> On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule &lt;
>>>>>>
>>>>> pavel.stehule@
>>> &gt; 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
>

​Mechanically I've wanted to do the following without creating an actual
type:

{query form}
WITH invoiceinfo (invoiceid, invoicedetailentry) AS (
SELECT invoiceid, ROW(itemid, itemdescription, itemcost, itemsale,
itemquantity)
FROM invoicelines
)
[... other CTE joins and stuff here...can carry around the 5 info fields in
a single composite until they are needed]
SELECT invoiceid, (invoicedetailentry).*
FROM invoiceinfo
;

{working example}
WITH invoiceinfo (invoiceid, invoicedetailentry) AS (
SELECT invoiceid, ROW(itemid, itemdescription, itemcost, itemsale,
itemquantity)
FROM (VALUES ('1',1,'1',0,1,1)) invoicelines (invoiceid, itemid,
itemdescription, itemcost, itemsale, itemquantity)
)
SELECT invoiceid, (invoicedetailentry).*
FROM invoiceinfo
;

This is made up but not dissimilar to what I have worked with. That said I
can and do usually either just join in the details one time or I need to do
more with the details than just carry them around and so providing a named
type usually ends up being the way to go. Regardless the form is
representative.

My most recent need for this ended up being best handled with named types
and support functions operating on those types so its hard to say I have a
strong desire for this but anyway.

David J.

​