Hello!
I have created a patch to allow additional commas between columns, and
at the end of the SELECT clause.
Motivation:
Commas of this type are allowed in many programming languages, in some
it is even recommended to use them at the ends of lists or objects. A
new generation of programmers expects a more forgiving language just as
our generation enjoyed LIMIT and the ability to write `select` in lowercase.
Accepted:
SELECT 1,;
SELECT 1,,,,,;
SELECT *, from information_schema.sql_features;
(...) RETURNING a,,b,c,;
Not accepted:
SELECT ,;
SELECT ,1;
SELECT ,,,;
Advantages:
- simplifies the creation and debugging of queries by reducing the most
common syntax error,
- eliminates the need to use the popular `1::int as dummy` at the end of
a SELECT list,
- simplifies query generators,
- the query is still deterministic,
Disadvantages:
- counting of returned columns can be difficult,
- syntax checkers will still report errors,
- probably not SQL standard compliant,
- functionality can be controversial,
I attach the patch along with the tests.
What do you think?
Your opinions are very much welcome!
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e8b619926e..8bf0a2690e 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -17174,6 +17174,7 @@ opt_target_list: target_list
{ $$ = $1; }
target_list:
target_el
{ $$ = list_make1($1); }
| target_list ',' target_el
{ $$ = lappend($1, $3); }
+ | target_list ','
/* Handling trailing comma */
;
target_el: a_expr AS ColLabel
diff --git a/src/test/regress/expected/returning.out
b/src/test/regress/expected/returning.out
index cb51bb8687..8604e50e18 100644
--- a/src/test/regress/expected/returning.out
+++ b/src/test/regress/expected/returning.out
@@ -238,7 +238,7 @@ CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO INSTEAD
UPDATE foo SET f1 = new.f1, f2 = new.f2 WHERE f1 = old.f1
RETURNING f1, f2;
update voo set f1 = f1 + 1 where f2 = 'zoo2';
-update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
+update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2,;
f1 | f2 | ?column?
----+------+----------
16 | zoo2 | 32
@@ -264,7 +264,7 @@ SELECT * FROM voo;
CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
DELETE FROM foo WHERE f1 = old.f1
- RETURNING f1, f2;
+ RETURNING f1, , , , f2;
DELETE FROM foo WHERE f1 = 13;
DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
f1 | f2 | f3 | f4
diff --git a/src/test/regress/expected/select.out
b/src/test/regress/expected/select.out
index 33a6dceb0e..e4b9e916da 100644
--- a/src/test/regress/expected/select.out
+++ b/src/test/regress/expected/select.out
@@ -968,3 +968,50 @@ explain (costs off) select * from list_parted_tbl;
(2 rows)
drop table list_parted_tbl;
+-- Handling trailing comma
+SELECT *, FROM onek
+ WHERE onek.unique1 < 10
+ ORDER BY onek.unique1;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand |
twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+ 0 | 998 | 0 | 0 | 0 | 0 | 0 | 0 |
0 | 0 | 0 | 0 | 1 | AAAAAA | KMBAAA | OOOOxx
+ 1 | 214 | 1 | 1 | 1 | 1 | 1 | 1 |
1 | 1 | 1 | 2 | 3 | BAAAAA | GIAAAA | OOOOxx
+ 2 | 326 | 0 | 2 | 2 | 2 | 2 | 2 |
2 | 2 | 2 | 4 | 5 | CAAAAA | OMAAAA | OOOOxx
+ 3 | 431 | 1 | 3 | 3 | 3 | 3 | 3 |
3 | 3 | 3 | 6 | 7 | DAAAAA | PQAAAA | VVVVxx
+ 4 | 833 | 0 | 0 | 4 | 4 | 4 | 4 |
4 | 4 | 4 | 8 | 9 | EAAAAA | BGBAAA | HHHHxx
+ 5 | 541 | 1 | 1 | 5 | 5 | 5 | 5 |
5 | 5 | 5 | 10 | 11 | FAAAAA | VUAAAA | HHHHxx
+ 6 | 978 | 0 | 2 | 6 | 6 | 6 | 6 |
6 | 6 | 6 | 12 | 13 | GAAAAA | QLBAAA | OOOOxx
+ 7 | 647 | 1 | 3 | 7 | 7 | 7 | 7 |
7 | 7 | 7 | 14 | 15 | HAAAAA | XYAAAA | VVVVxx
+ 8 | 653 | 0 | 0 | 8 | 8 | 8 | 8 |
8 | 8 | 8 | 16 | 17 | IAAAAA | DZAAAA | HHHHxx
+ 9 | 49 | 1 | 1 | 9 | 9 | 9 | 9 |
9 | 9 | 9 | 18 | 19 | JAAAAA | XBAAAA | HHHHxx
+(10 rows)
+
+SELECT 1,,2,,,3 as dummy,;
+ ?column? | ?column? | dummy
+----------+----------+-------
+ 1 | 2 | 3
+(1 row)
+
+SELECT count(*),;
+ count
+-------
+ 1
+(1 row)
+
+SELECT count(*), FROM onek;
+ count
+-------
+ 1000
+(1 row)
+
+SELECT v.k as x, FROM (VALUES(1)) v(k);
+ x
+---
+ 1
+(1 row)
+
+-- Error is expected
+SELECT ,;
+ERROR: syntax error at or near ","
+LINE 1: SELECT ,;
+ ^
diff --git a/src/test/regress/sql/returning.sql
b/src/test/regress/sql/returning.sql
index a460f82fb7..a3cb0586af 100644
--- a/src/test/regress/sql/returning.sql
+++ b/src/test/regress/sql/returning.sql
@@ -117,14 +117,14 @@ CREATE OR REPLACE RULE voo_u AS ON UPDATE TO voo DO
INSTEAD
RETURNING f1, f2;
update voo set f1 = f1 + 1 where f2 = 'zoo2';
-update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2;
+update voo set f1 = f1 + 1 where f2 = 'zoo2' RETURNING *, f1*2,;
SELECT * FROM foo;
SELECT * FROM voo;
CREATE OR REPLACE RULE voo_d AS ON DELETE TO voo DO INSTEAD
DELETE FROM foo WHERE f1 = old.f1
- RETURNING f1, f2;
+ RETURNING f1, , , , f2;
DELETE FROM foo WHERE f1 = 13;
DELETE FROM foo WHERE f2 = 'zit' RETURNING *;
diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql
index 019f1e7673..e98794c135 100644
--- a/src/test/regress/sql/select.sql
+++ b/src/test/regress/sql/select.sql
@@ -262,3 +262,20 @@ create table list_parted_tbl1 partition of list_parted_tbl
for values in (1) partition by list(b);
explain (costs off) select * from list_parted_tbl;
drop table list_parted_tbl;
+
+
+-- Handling trailing comma
+SELECT *, FROM onek
+ WHERE onek.unique1 < 10
+ ORDER BY onek.unique1;
+
+SELECT 1,,2,,,3 as dummy,;
+
+SELECT count(*),;
+
+SELECT count(*), FROM onek;
+
+SELECT v.k as x, FROM (VALUES(1)) v(k);
+
+-- Error is expected
+SELECT ,;
