CREATE STATISTICS: improve misleading error message

I think the error message for a different condition was inadvertently
copied.

This problem seems to have been introduced by commit a4d75c86bf15.

Author: Álvaro Herrera <alvherre@kurilemu.de>
Reported-by: jian he <jian.universality@gmail.com>
Reviewed-by: Tom Lane <tgl@sss.pgh.pa.us>
Backpatch-through: 14
Discussion: https://postgr.es/m/CACJufxEZ48toGH0Em_6vdsT57Y3L8pLF=DZCQ_gCii6=C3MeXw@mail.gmail.com

3 files changed, 40 insertions, 1 deletions

diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c
index 4f4191b0ea6..5f442bc3bd4 100644
--- a/src/backend/tcop/utility.c
+++ b/src/backend/tcop/utility.c
@@ -1874,7 +1874,8 @@ ProcessUtilitySlow(ParseState *pstate,
 					if (!IsA(rel, RangeVar))
 						ereport(ERROR,
 								(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
-								 errmsg("only a single relation is allowed in CREATE STATISTICS")));
+								 errmsg("cannot create statistics on the specified relation"),
+								 errdetail("CREATE STATISTICS only supports tables, foreign tables and materialized views.")));
 
 					/*
 					 * CREATE STATISTICS will influence future execution plans
diff --git a/src/test/regress/expected/stats_ext.out b/src/test/regress/expected/stats_ext.out
index c66e09f8b16..a1f83b58b23 100644
--- a/src/test/regress/expected/stats_ext.out
+++ b/src/test/regress/expected/stats_ext.out
@@ -54,6 +54,32 @@ CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), y FROM ext_stats_test;
 ERROR:  duplicate expression in statistics definition
 CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
 ERROR:  unrecognized statistics kind "unrecognized"
+-- unsupported targets
+CREATE STATISTICS tst ON a FROM (VALUES (x)) AS foo;
+ERROR:  cannot create statistics on the specified relation
+DETAIL:  CREATE STATISTICS only supports tables, foreign tables and materialized views.
+CREATE STATISTICS tst ON a FROM foo NATURAL JOIN bar;
+ERROR:  cannot create statistics on the specified relation
+DETAIL:  CREATE STATISTICS only supports tables, foreign tables and materialized views.
+CREATE STATISTICS tst ON a FROM (SELECT * FROM ext_stats_test) AS foo;
+ERROR:  cannot create statistics on the specified relation
+DETAIL:  CREATE STATISTICS only supports tables, foreign tables and materialized views.
+CREATE STATISTICS tst ON a FROM ext_stats_test s TABLESAMPLE system (x);
+ERROR:  cannot create statistics on the specified relation
+DETAIL:  CREATE STATISTICS only supports tables, foreign tables and materialized views.
+CREATE STATISTICS tst ON a FROM XMLTABLE('foo' PASSING 'bar' COLUMNS a text);
+ERROR:  cannot create statistics on the specified relation
+DETAIL:  CREATE STATISTICS only supports tables, foreign tables and materialized views.
+CREATE STATISTICS tst ON a FROM JSON_TABLE(jsonb '123', '$' COLUMNS (item int));
+ERROR:  cannot create statistics on the specified relation
+DETAIL:  CREATE STATISTICS only supports tables, foreign tables and materialized views.
+CREATE FUNCTION tftest(int) returns table(a int, b int) as $$
+SELECT $1, $1+i FROM generate_series(1,5) g(i);
+$$ LANGUAGE sql IMMUTABLE STRICT;
+CREATE STATISTICS alt_stat2 ON a FROM tftest(1);
+ERROR:  cannot create statistics on the specified relation
+DETAIL:  CREATE STATISTICS only supports tables, foreign tables and materialized views.
+DROP FUNCTION tftest;
 -- incorrect expressions
 CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
 ERROR:  extended statistics require at least 2 columns
diff --git a/src/test/regress/sql/stats_ext.sql b/src/test/regress/sql/stats_ext.sql
index 9ce4c670ecb..823c7db9dab 100644
--- a/src/test/regress/sql/stats_ext.sql
+++ b/src/test/regress/sql/stats_ext.sql
@@ -40,6 +40,18 @@ CREATE STATISTICS tst ON x, x, y, x, x, (x || 'x'), (y + 1), (x || 'x'), (x || '
 CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1), (x || 'x'), (x || 'x'), (y + 1) FROM ext_stats_test;
 CREATE STATISTICS tst ON (x || 'x'), (x || 'x'), y FROM ext_stats_test;
 CREATE STATISTICS tst (unrecognized) ON x, y FROM ext_stats_test;
+-- unsupported targets
+CREATE STATISTICS tst ON a FROM (VALUES (x)) AS foo;
+CREATE STATISTICS tst ON a FROM foo NATURAL JOIN bar;
+CREATE STATISTICS tst ON a FROM (SELECT * FROM ext_stats_test) AS foo;
+CREATE STATISTICS tst ON a FROM ext_stats_test s TABLESAMPLE system (x);
+CREATE STATISTICS tst ON a FROM XMLTABLE('foo' PASSING 'bar' COLUMNS a text);
+CREATE STATISTICS tst ON a FROM JSON_TABLE(jsonb '123', '$' COLUMNS (item int));
+CREATE FUNCTION tftest(int) returns table(a int, b int) as $$
+SELECT $1, $1+i FROM generate_series(1,5) g(i);
+$$ LANGUAGE sql IMMUTABLE STRICT;
+CREATE STATISTICS alt_stat2 ON a FROM tftest(1);
+DROP FUNCTION tftest;
 -- incorrect expressions
 CREATE STATISTICS tst ON (y) FROM ext_stats_test; -- single column reference
 CREATE STATISTICS tst ON y + z FROM ext_stats_test; -- missing parentheses