Re: [HACKERS] idea: allow AS label inside ROW constructor
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 2014-10-22 19:09 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: 2014-10-22 18:35 GMT+02:00 Merlin Moncure mmonc...@gmail.com: On Wed, Oct 22, 2014 at 11:21 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 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? 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 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. *) 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. merlin diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y new file mode 100644 index 0de9584..682506d *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *** static void processCASbits(int cas_bits, *** 174,179 --- 174,180 bool *deferrable, bool *initdeferred, bool *not_valid, bool *no_inherit, core_yyscan_t yyscanner); static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); + static List *extractArgs(List *target_list); %} *** static Node *makeRecursiveViewSelect(cha *** 431,437 %type list ExclusionConstraintList ExclusionConstraintElem %type list func_arg_list %type node func_arg_expr ! %type list row type_list array_expr_list %type node case_expr case_arg when_clause case_default %type list when_clause_list %type ival sub_type --- 432,439 %type list ExclusionConstraintList ExclusionConstraintElem %type list func_arg_list %type node func_arg_expr ! %type list row type_list array_expr_list row_field_list ! %type target row_field_el %type node case_expr case_arg when_clause case_default %type list when_clause_list %type ival sub_type *** a_expr: c_expr { $$ = $1; } *** 11162,11179 } | row OVERLAPS row { ! if (list_length($1) != 2) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg(wrong number of parameters on left side of OVERLAPS expression), parser_errposition(@1))); ! if (list_length($3) != 2) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg(wrong number of parameters on right side of OVERLAPS expression), parser_errposition(@3))); $$ = (Node *) makeFuncCall(SystemFuncName(overlaps), ! list_concat($1, $3), @2); } | a_expr IS TRUE_P %prec IS --- 11164,11184 } | row OVERLAPS row { ! List *l1 = extractArgs($1); ! List *l2 = extractArgs($3); ! ! if (list_length(l1) != 2) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg(wrong number of parameters on left side of OVERLAPS expression), parser_errposition(@1))); ! if (list_length(l2) != 2) ereport(ERROR, (errcode(ERRCODE_SYNTAX_ERROR), errmsg(wrong number of parameters on right side of OVERLAPS expression), parser_errposition(@3))); $$ = (Node *) makeFuncCall(SystemFuncName(overlaps), ! list_concat(l1, l2), @2); } | a_expr IS TRUE_P %prec IS *** frame_bound: *** 12302,12310
Re: [HACKERS] idea: allow AS label inside ROW constructor
On 10/23/2014 09:27 AM, Merlin Moncure wrote: On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: allow AS label inside ROW constructor
On Oct23, 2014, at 15:39 , Andrew Dunstan and...@dunslane.net wrote: On 10/23/2014 09:27 AM, Merlin Moncure wrote: On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule pavel.steh...@gmail.com 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. best regards, Florian Pflug -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: allow AS label inside ROW constructor
On 10/23/2014 09:57 AM, Florian Pflug wrote: On Oct23, 2014, at 15:39 , Andrew Dunstan and...@dunslane.net wrote: On 10/23/2014 09:27 AM, Merlin Moncure wrote: On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule pavel.steh...@gmail.com 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. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 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. 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: allow AS label inside ROW constructor
2014-10-23 17:36 GMT+02:00 David G Johnston david.g.johns...@gmail.com: 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. Expanding anonymous record is harder task, but it is possible probably Pavel 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. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: allow AS label inside ROW constructor
On Thu, Oct 23, 2014 at 8:51 AM, Andrew Dunstan and...@dunslane.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.
Re: [HACKERS] idea: allow AS label inside ROW constructor
On Thu, Oct 23, 2014 at 8:39 AM, Andrew Dunstan and...@dunslane.net wrote: On 10/23/2014 09:27 AM, Merlin Moncure wrote: On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 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. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] idea: allow AS label inside ROW constructor
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.
Re: [HACKERS] idea: allow AS label inside ROW constructor
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 2014-10-22 18:21 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: 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.
Re: [HACKERS] idea: allow AS label inside ROW constructor
On Wed, Oct 22, 2014 at 11:21 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 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? 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: allow AS label inside ROW constructor
2014-10-22 18:35 GMT+02:00 Merlin Moncure mmonc...@gmail.com: On Wed, Oct 22, 2014 at 11:21 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 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? 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 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. *) 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. merlin