Improve error handling of column references in expression transformation

Column references are not allowed in default expressions and partition
bound expressions, and are restricted as such once the transformation of
their expressions is done.  However, trying to use more complex column
references can lead to confusing error messages.  For example, trying to
use a two-field column reference name for default expressions and
partition bounds leads to "missing FROM-clause entry for table", which
makes no sense in their respective context.

In order to make the errors generated more useful, this commit adds more
verbose messages when transforming column references depending on the
context.  This has a little consequence though: for example an
expression using an aggregate with a column reference as argument would
cause an error to be generated for the column reference, while the
aggregate was the problem reported before this commit because column
references get transformed first.

The confusion exists for default expressions for a long time, and the
problem is new as of v12 for partition bounds.  Still per the lack of
complaints on the matter no backpatch is done.

The patch has been written by Amit Langote and me, and Tom Lane has
provided the improvement of the documentation for default expressions on
the CREATE TABLE page.

Author: Amit Langote, Michael Paquier
Reviewed-by: Tom Lane
Discussion: https://postgr.es/m/20190326020853.GM2558@paquier.xyz

2 files changed, 60 insertions, 12 deletions

diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 1cf21cc26f0..ad0cb32678d 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -297,6 +297,40 @@ ERROR:  tables declared WITH OIDS are not supported
 -- but explicitly not adding oids is still supported
 CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid;
 CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid;
+-- check restriction with default expressions
+-- invalid use of column reference in default expressions
+CREATE TABLE default_expr_column (id int DEFAULT (id));
+ERROR:  cannot use column reference in DEFAULT expression
+LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (id));
+                                                          ^
+CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
+ERROR:  cannot use column reference in DEFAULT expression
+LINE 1: CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
+                                                          ^
+CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
+ERROR:  cannot use column reference in DEFAULT expression
+LINE 1: ...TE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
+                                                                 ^
+-- invalid column definition
+CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent)));
+ERROR:  cannot use column reference in DEFAULT expression
+LINE 1: ...TABLE default_expr_non_column (a int DEFAULT (avg(non_existe...
+                                                             ^
+-- invalid use of aggregate
+CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
+ERROR:  aggregate functions are not allowed in DEFAULT expressions
+LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
+                                                      ^
+-- invalid use of subquery
+CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
+ERROR:  cannot use subquery in DEFAULT expression
+LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
+                                                     ^
+-- invalid use of set-returning function
+CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
+ERROR:  set-returning functions are not allowed in DEFAULT expressions
+LINE 1: CREATE TABLE default_expr_agg (a int DEFAULT (generate_serie...
+                                                      ^
 --
 -- Partitioned tables
 --
@@ -491,23 +525,23 @@ Partitions: part_null FOR VALUES IN (NULL),
 
 -- forbidden expressions for partition bound with list partitioned table
 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
-ERROR:  column "somename" does not exist
+ERROR:  cannot use column reference in partition bound expression
 LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename);
                                                              ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (somename.somename);
-ERROR:  missing FROM-clause entry for table "somename"
+ERROR:  cannot use column reference in partition bound expression
 LINE 1: ...expr_fail PARTITION OF list_parted FOR VALUES IN (somename.s...
                                                              ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
-ERROR:  cannot use column references in partition bound expression
+ERROR:  cannot use column reference in partition bound expression
 LINE 1: ..._bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (a);
                                                                     ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
-ERROR:  aggregate functions are not allowed in partition bound
+ERROR:  cannot use column reference in partition bound expression
 LINE 1: ...s_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(a));
-                                                               ^
+                                                                   ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(somename));
-ERROR:  column "somename" does not exist
+ERROR:  cannot use column reference in partition bound expression
 LINE 1: ..._fail PARTITION OF list_parted FOR VALUES IN (sum(somename))...
                                                              ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF list_parted FOR VALUES IN (sum(1));
@@ -573,27 +607,27 @@ CREATE TABLE range_parted (
 -- forbidden expressions for partition bounds with range partitioned table
 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
   FOR VALUES FROM (somename) TO ('2019-01-01');
-ERROR:  column "somename" does not exist
+ERROR:  cannot use column reference in partition bound expression
 LINE 2:   FOR VALUES FROM (somename) TO ('2019-01-01');
                            ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
   FOR VALUES FROM (somename.somename) TO ('2019-01-01');
-ERROR:  missing FROM-clause entry for table "somename"
+ERROR:  cannot use column reference in partition bound expression
 LINE 2:   FOR VALUES FROM (somename.somename) TO ('2019-01-01');
                            ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
   FOR VALUES FROM (a) TO ('2019-01-01');
-ERROR:  cannot use column references in partition bound expression
+ERROR:  cannot use column reference in partition bound expression
 LINE 2:   FOR VALUES FROM (a) TO ('2019-01-01');
                            ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
   FOR VALUES FROM (max(a)) TO ('2019-01-01');
-ERROR:  aggregate functions are not allowed in partition bound
+ERROR:  cannot use column reference in partition bound expression
 LINE 2:   FOR VALUES FROM (max(a)) TO ('2019-01-01');
-                           ^
+                               ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
   FOR VALUES FROM (max(somename)) TO ('2019-01-01');
-ERROR:  column "somename" does not exist
+ERROR:  cannot use column reference in partition bound expression
 LINE 2:   FOR VALUES FROM (max(somename)) TO ('2019-01-01');
                                ^
 CREATE TABLE part_bogus_expr_fail PARTITION OF range_parted
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index ba935488ba8..751c0d39f5d 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -304,6 +304,20 @@ CREATE TABLE withoid() WITH (oids = true);
 CREATE TEMP TABLE withoutoid() WITHOUT OIDS; DROP TABLE withoutoid;
 CREATE TEMP TABLE withoutoid() WITH (oids = false); DROP TABLE withoutoid;
 
+-- check restriction with default expressions
+-- invalid use of column reference in default expressions
+CREATE TABLE default_expr_column (id int DEFAULT (id));
+CREATE TABLE default_expr_column (id int DEFAULT (bar.id));
+CREATE TABLE default_expr_agg_column (id int DEFAULT (avg(id)));
+-- invalid column definition
+CREATE TABLE default_expr_non_column (a int DEFAULT (avg(non_existent)));
+-- invalid use of aggregate
+CREATE TABLE default_expr_agg (a int DEFAULT (avg(1)));
+-- invalid use of subquery
+CREATE TABLE default_expr_agg (a int DEFAULT (select 1));
+-- invalid use of set-returning function
+CREATE TABLE default_expr_agg (a int DEFAULT (generate_series(1,3)));
+
 --
 -- Partitioned tables
 --