idea: allow AS label inside ROW constructor

[Pavel Stehule]

Hi

with new functions row_to_json(b), there is more often usage of ROW
constructor. Using names in fields is relative difficult. Because ROW has
special clause in parser, I am thinking so we can enable labeling inside
ROW constructor

so instead currently supported:

select row_to_json(r) from (select 10 as a, 20 as b) r;

users can to write:

select row_to_json(row(10 as a,20 as b));

labeling will be enabled "only" inside ROW constructor. I don't propose
enable it everywhere.

What do you think about it?

Regards

Pavel

Currently supported syntax is natural for long time PostgreSQL user, but it
is relative strange for usual user.

[Pavel Stehule]

here is a motivation, why I propose this feature

http://dba.stackexchange.com/questions/27732/set-names-to-attributes-when-creating-json-with-row-to-json

same query I have in Czech postgres users mailing list

Pavel

Post by Pavel Stehule
Hi
with new functions row_to_json(b), there is more often usage of ROW
constructor. Using names in fields is relative difficult. Because ROW has
special clause in parser, I am thinking so we can enable labeling inside
ROW constructor
select row_to_json(r) from (select 10 as a, 20 as b) r;
select row_to_json(row(10 as a,20 as b));
labeling will be enabled "only" inside ROW constructor. I don't propose
enable it everywhere.
What do you think about it?
Regards
Pavel
Currently supported syntax is natural for long time PostgreSQL user, but
it is relative strange for usual user.

[Merlin Moncure]

Post by Pavel Stehule
Hi
with new functions row_to_json(b), there is more often usage of ROW
constructor. Using names in fields is relative difficult. Because ROW has
special clause in parser, I am thinking so we can enable labeling inside ROW
constructor
select row_to_json(r) from (select 10 as a, 20 as b) r;
select row_to_json(row(10 as a,20 as b));
labeling will be enabled "only" inside ROW constructor. I don't propose
enable it everywhere.
What do you think about it?

It's a neat idea -- maybe a better alternative to what I was thinking
here: http://postgresql.1045698.n5.nabble.com/Support-UPDATE-table-SET-tp5823073p5823944.html

Some questions:
*) What would the parser transformation resolve to
*) Are we ok with SQL standard
*) Do you think this (or some similar variant) would work?

select row_to_json(row(foo.*)) from foo;

merlin

[Pavel Stehule]

Post by Merlin Moncure

Post by Pavel Stehule
Hi
with new functions row_to_json(b), there is more often usage of ROW
constructor. Using names in fields is relative difficult. Because ROW has
special clause in parser, I am thinking so we can enable labeling inside

ROW

Post by Pavel Stehule
constructor
select row_to_json(r) from (select 10 as a, 20 as b) r;
select row_to_json(row(10 as a,20 as b));
labeling will be enabled "only" inside ROW constructor. I don't propose
enable it everywhere.
What do you think about it?

It's a neat idea -- maybe a better alternative to what I was thinking
http://postgresql.1045698.n5.nabble.com/Support-UPDATE-table-SET-tp5823073p5823944.html
*) What would the parser transformation resolve to

row: ROW '(' expr_list ')' { $$
= $3; }
| ROW '('
')' { $$ = NIL; }
| '(' expr_list ',' a_expr ')' {
$$ = lappend($2, $4); }
;

we can replace a expr_list by target_list. I know only so it doesn't
enforce a problems with gramatic - bison doesn't raise any warning.


*) Are we ok with SQL standard
SQL standard doesn't think named attributes in row - so it is out of range
ANSI. But it is not in conflict with standard. "AS name" is used more in
SQL/MM, SQL/XML -- and function named parameters has different syntax
"parameter_name <= value" - I checked it against SQL99.

Post by Merlin Moncure
*) Do you think this (or some similar variant) would work?
select row_to_json(row(foo.*)) from foo;

It looks like independent feature and can work too - it is more natural for
user.

Post by Merlin Moncure
merlin

[Pavel Stehule]

Hi

here is a prototype

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)

postgres=# select row_to_json(row(10, row(30, 20)));
row_to_json
----------------------------------
{"f1":10,"f2":{"f1":30,"f2":20}}
(1 row)

Regards

Pavel

Post by Pavel Stehule

Post by Pavel Stehule

Post by Pavel Stehule
Hi
with new functions row_to_json(b), there is more often usage of ROW
constructor. Using names in fields is relative difficult. Because ROW

has

Post by Pavel Stehule
special clause in parser, I am thinking so we can enable labeling

inside ROW

Post by Pavel Stehule
constructor
select row_to_json(r) from (select 10 as a, 20 as b) r;
select row_to_json(row(10 as a,20 as b));
labeling will be enabled "only" inside ROW constructor. I don't propose
enable it everywhere.
What do you think about it?

It's a neat idea -- maybe a better alternative to what I was thinking
http://postgresql.1045698.n5.nabble.com/Support-UPDATE-table-SET-tp5823073p5823944.html
*) What would the parser transformation resolve to

row: ROW '(' expr_list ')' { $$
= $3; }
| ROW '('
')' { $$ = NIL; }
| '(' expr_list ',' a_expr ')' {
$$ = lappend($2, $4); }
;
we can replace a expr_list by target_list. I know only so it doesn't
enforce a problems with gramatic - bison doesn't raise any warning.
*) Are we ok with SQL standard
SQL standard doesn't think named attributes in row - so it is out of range
ANSI. But it is not in conflict with standard. "AS name" is used more in
SQL/MM, SQL/XML -- and function named parameters has different syntax
"parameter_name <= value" - I checked it against SQL99.

Post by Pavel Stehule
*) Do you think this (or some similar variant) would work?
select row_to_json(row(foo.*)) from foo;

It looks like independent feature and can work too - it is more natural
for user.

Post by Pavel Stehule
merlin

[Merlin Moncure]

Post by Pavel Stehule
Hi
here is a prototype
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)
postgres=# select row_to_json(row(10, row(30, 20)));
row_to_json
----------------------------------
{"f1":10,"f2":{"f1":30,"f2":20}}
(1 row)

wow -- this is great. I'll take a a look.

merlin

[Andrew Dunstan]

Post by Merlin Moncure

Post by Pavel Stehule
Hi
here is a prototype
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)
postgres=# select row_to_json(row(10, row(30, 20)));
row_to_json
----------------------------------
{"f1":10,"f2":{"f1":30,"f2":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.

cheers

andrew

[Florian Pflug]

Post by Merlin Moncure

Post by Pavel Stehule
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.

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.

best regards,
Florian Pflug

[Andrew Dunstan]

Post by Florian Pflug

Post by Merlin Moncure

Post by Pavel Stehule
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.

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.

cheers

andrew

[David G Johnston]

Post by Andrew Dunstan

On Oct23, 2014, at 15:39 , Andrew Dunstan &lt;

Post by Andrew Dunstan

On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule &lt;

Post by Pavel Stehule
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.

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.

[Pavel Stehule]

Post by David G Johnston

Post by Andrew Dunstan

On Oct23, 2014, at 15:39 , Andrew Dunstan &lt;

Post by Andrew Dunstan

On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule &lt;

Post by Pavel Stehule
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.

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

Post by Andrew Dunstan

Post by Andrew Dunstan
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.

Expanding anonymous record is harder task, but it is possible probably

Pavel

Post by David G Johnston
It would be especially nice to not have to use "as" though, if the source
fields are already so named.
David J.
--
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.
--
http://www.postgresql.org/mailpref/pgsql-hackers

[Andrew Dunstan]

Post by David G Johnston

Post by Andrew Dunstan

On Oct23, 2014, at 15:39 , Andrew Dunstan &lt;

Post by Andrew Dunstan

On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule &lt;

Post by Pavel Stehule
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.

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

[David Johnston]

Post by David G Johnston

Post by Andrew Dunstan

On Oct23, 2014, at 15:39 , Andrew Dunstan &lt;

Post by Andrew Dunstan

On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule &lt;

postgres=# select row_to_json(row(10 as A, row(30 as c, 20 AS B) as

Post by Andrew Dunstan

Post by Pavel Stehule
x));
row_to_json
------------------------------
{"a":10,"x":{"c":30,"b":20}}
(1 row)
wow -- this is great. I'll take a a look.

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.

​

[Merlin Moncure]

Post by Andrew Dunstan

Post by Merlin Moncure

Post by Pavel Stehule
Hi
here is a prototype
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)
postgres=# select row_to_json(row(10, row(30, 20)));
row_to_json
----------------------------------
{"f1":10,"f2":{"f1":30,"f2":20}}
(1 row)

wow -- this is great. I'll take a a look.

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.

json_build_object is super useful for sure, but what about
performance? Application communication of data via json has been
steadily increasing in terms of overall percentage in all the work
that I do and performance is very important.

I tested at one million rows and:
A. select to_json(array(select json_build_object('a',a,'b',b) from foo f));
takes about twice as long as either:
B. select to_json(array(select row(a,b) from foo f));
or
C. select to_json(array(select f from foo f));

Note the results aren't quite the same, "B" anonymizes the columns to
'f1' etc and 'A' adds 5 extra spaces per array element (aside: the
json serialization functions are not consistently spaced -- shouldn't
they generally be as spartan as possible?). Maybe the performance
differences are a reflection if that spurious space consumption
though...looking a the code json_build_object just does basic
StringInfo processing so I don't see any reason for it to be greatly
slower.

With a nested construction
(json_build_object('a',a,'b',json_build_object('a', a, 'b', b)) vs
row(a,b,row(a,b))) the results are closer; about 1.5x the time taken
for json_build_object. Not close enough to call it a wash, but not
damning either, at least for this one case.

In terms of row() construction, there aren't many cases today because
row() is used precisely because it destroys column names unless you
have a composite type handy to cast (and it's cpu cycle sucking
overhead) so I've learned to code around it. In some cases a row()
type that preserved names would remove the need for the composite. It
doesn't happen *that* often -- usually it comes up when stashing
aggregated rows through a CTE. At least some of *those* cases are to
work around the lack of LATERAL; my production systems are still on
9.2.

All that being said, row() seems to me to have a lot of style points
and I don't think nested row constructions should have a dependency on
json/jsonb. It's just something you do, and json processing is
deferred to the last stage of processing before the data goes out the
door..that's where we would presumably apply formatting decisions on
top of that.

merlin