From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bruce Momjian <bruce@momjian.us>
Cc: David G. Johnston <david.g.johnston@gmail.com>
Cc: Guyren Howe <guyren@gmail.com>
Cc: Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Re: Please provide examples of rows from
Date: Sun, 20 Sep 2020 14:09:13 -0400
Message-ID: <878016.1600625353@sss.pgh.pa.us> (raw)
In-Reply-To: <798909.1600562993@sss.pgh.pa.us>
References: <158638264419.662.2482095087061084020@wrigleys.postgresql.org>
<20200904014618.GB13932@momjian.us>
<CAKFQuwbBDr22KJRyJQRECO_XU6cyouwz3ocn0Tv4JgG4Gm5hMQ@mail.gmail.com>
<20200920003910.GA22746@momjian.us>
<798909.1600562993@sss.pgh.pa.us>
I wrote:
> Yeah, this error message needs some help. With a function having
> multiple OUT parameters, the prorettype is indeed "record", but
> the specific record type is implied by the OUT parameters so you
> do not need to (and can't) specify it in the query.
> The point of the AS feature is to allow specifying the concrete
> record type for record-returning functions that don't have a
> predefined result record type, like dblink().
> I think this error text was written before we had multiple OUT
> parameters, so it was okay at the time; but now it needs to be
> more precise.
Concretely, perhaps like the attached. I was unsurprised to find
that this error condition isn't exercised in our regression tests.
I added some coverage, but maybe that's overkill?
regards, tom lane
Attachments:improve-message-for-unwanted-coldeflist.patch (text/x-diff, 3.9K) downloaddiff --git a/src/backend/parser/parse_relation.c b/src/backend/parser/parse_relation.cindex b875a50646..a56bd86181 100644--- a/src/backend/parser/parse_relation.c+++ b/src/backend/parser/parse_relation.c@@ -1737,16 +1737,46 @@ addRangeTableEntryForFunction(ParseState *pstate,
/*
* A coldeflist is required if the function returns RECORD and hasn't
- * got a predetermined record type, and is prohibited otherwise.+ * got a predetermined record type, and is prohibited otherwise. This+ * can be a bit confusing, so we expend some effort on delivering a+ * relevant error message.
*/
if (coldeflist != NIL)
{
- if (functypclass != TYPEFUNC_RECORD)- ereport(ERROR,- (errcode(ERRCODE_SYNTAX_ERROR),- errmsg("a column definition list is only allowed for functions returning \"record\""),- parser_errposition(pstate,- exprLocation((Node *) coldeflist))));+ switch (functypclass)+ {+ case TYPEFUNC_RECORD:+ /* ok */+ break;+ case TYPEFUNC_COMPOSITE:+ case TYPEFUNC_COMPOSITE_DOMAIN:++ /*+ * If the function's raw result type is RECORD, we must+ * have resolved it using its OUT parameters. Otherwise,+ * it must have a named composite type.+ */+ if (exprType(funcexpr) == RECORDOID)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("a column definition list is redundant for a function with OUT parameters"),+ parser_errposition(pstate,+ exprLocation((Node *) coldeflist))));+ else+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("a column definition list is redundant for a function returning a named composite type"),+ parser_errposition(pstate,+ exprLocation((Node *) coldeflist))));+ break;+ default:+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("a column definition list is only allowed for functions returning \"record\""),+ parser_errposition(pstate,+ exprLocation((Node *) coldeflist))));+ break;+ }
}
else
{
diff --git a/src/test/regress/expected/rangefuncs.out b/src/test/regress/expected/rangefuncs.outindex 7eced28452..e618aec2eb 100644--- a/src/test/regress/expected/rangefuncs.out+++ b/src/test/regress/expected/rangefuncs.out@@ -2109,6 +2109,19 @@ select * from testrngfunc();
7.136178 | 7.14
(1 row)
+-- Check a couple of error cases while we're here+select * from testrngfunc() as t(f1 int8,f2 int8); -- fail, composite result+ERROR: a column definition list is redundant for a function returning a named composite type+LINE 1: select * from testrngfunc() as t(f1 int8,f2 int8);+ ^+select * from pg_get_keywords() as t(f1 int8,f2 int8); -- fail, OUT params+ERROR: a column definition list is redundant for a function with OUT parameters+LINE 1: select * from pg_get_keywords() as t(f1 int8,f2 int8);+ ^+select * from sin(3) as t(f1 int8,f2 int8); -- fail, scalar result type+ERROR: a column definition list is only allowed for functions returning "record"+LINE 1: select * from sin(3) as t(f1 int8,f2 int8);+ ^
drop type rngfunc_type cascade;
NOTICE: drop cascades to function testrngfunc()
--
diff --git a/src/test/regress/sql/rangefuncs.sql b/src/test/regress/sql/rangefuncs.sqlindex ae3119a959..5f41cb2d8d 100644--- a/src/test/regress/sql/rangefuncs.sql+++ b/src/test/regress/sql/rangefuncs.sql@@ -629,6 +629,11 @@ explain (verbose, costs off)
select * from testrngfunc();
select * from testrngfunc();
+-- Check a couple of error cases while we're here+select * from testrngfunc() as t(f1 int8,f2 int8); -- fail, composite result+select * from pg_get_keywords() as t(f1 int8,f2 int8); -- fail, OUT params+select * from sin(3) as t(f1 int8,f2 int8); -- fail, scalar result type+
drop type rngfunc_type cascade;
--
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: tgl@sss.pgh.pa.us, bruce@momjian.us, david.g.johnston@gmail.com, guyren@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Please provide examples of rows from
In-Reply-To: <878016.1600625353@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox