Thread: Re: row_to_json(), NULL values, and AS
I wrote:
> Neil Conway <neil.conway@gmail.com> writes:
>> On Fri, Jun 15, 2018 at 7:53 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>> I'm a bit hesitant to muck with this behavior, given that it's stood
>>> for ~20 years. However, if we did want to touch it, maybe the right
>>> thing would be to give up the absolutist position that f(x) and x.f
>>> are exactly interchangeable. We could say instead that we prefer the
>>> function interpretation if function syntax is used, and the column
>>> interpretation if column syntax is used.
>> Interesting! Your proposed change seems quite reasonable to me.
> Here's a proposed patch for that. (It needs to be applied over the
> fixes in <14497.1529089235@sss.pgh.pa.us>, which are unrelated but
> touch some of the same code.) I didn't add any test cases yet,
> but probably it's desirable to have one.
It occurred to me this morning that there's actually a dump/reload
hazard in the current behavior. Consider
regression=# create table t1 (f1 int, f2 text);
CREATE TABLE
regression=# create view v1 as select row_to_json(t1) as j from t1;
CREATE VIEW
regression=# alter table t1 add column row_to_json text;
ALTER TABLE
regression=# \d+ v1
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+------+-----------+----------+---------+----------+-------------
j | json | | | | extended |
View definition:
SELECT row_to_json(t1.*) AS j
FROM t1;
At this point, pg_dump will naively dump the view as
CREATE VIEW public.v1 AS
SELECT row_to_json(t1.*) AS j
FROM public.t1;
which, with the historical behavior, will be read as a reference to
the subsequently-added column, giving a view definition that means
something else entirely:
regression=# \d+ v1
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+------+-----------+----------+---------+----------+-------------
j | text | | | | extended |
View definition:
SELECT t1.row_to_json AS j
FROM t1;
The proposed change fixes this on the parsing side, with no need
for any hacking in ruleutils.c (hence it will fix it for pre-existing
dump files too).
So I'm now pretty well convinced that this is a good change and we
should slip it into v11. I'm still afraid to back-patch though.
I vaguely recall one or two prior complaints in this area, but not
so many that it's worth taking a chance of breaking applications
in minor releases.
regards, tom lane
I wrote:
> So I'm now pretty well convinced that this is a good change and we
> should slip it into v11.
I wrote a test case and was about ready to commit this, when I started
wondering about the behavior for fdresult == FUNCDETAIL_MULTIPLE.
That is, suppose that the notation f(x) matches more than one candidate
function f, and there's also a column named f. The old code would have
chosen the column interpretation, and so would the patch I posted
previously. But I don't really see how we can square that behavior with
documenting that "we prefer the function interpretation for functional
notation". I think you should get a "function is not unique" error in
such a case, not silently picking the column instead. If it's the
column you want, write it like a column.
So the attached updated patch rearranges the error handling logic to
make that happen.
BTW, while trying to make a test case I was reminded that CREATE FUNCTION
tries to prevent this situation from arising:
regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create function f1(t1) returns int as 'select 42' language sql;
ERROR: "f1" is already an attribute of type t1
That's totally non-bulletproof, though, since you can still create the
situation by renaming, or by adding the conflicting column after you
create the function; not to mention Neil's original problem where the
conflicting function is declared to take record or some such.
Furthermore, this behavior itself creates a dump/reload hazard, since
once you've done one of those things pg_dump will produce output that
fails this check.
While I've not done anything about it here, I think we should just
remove that error check. With this patch, functions and columns can
coexist reasonably peacefully, so we shouldn't need bogus hacks that
try to prevent it.
regards, tom lane
diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgml
index 3f24293..2f924b1 100644
*** a/doc/src/sgml/rowtypes.sgml
--- b/doc/src/sgml/rowtypes.sgml
*************** SELECT c.somefunc FROM inventory_item c;
*** 441,449 ****
Because of this behavior, it's unwise to give a function that takes a
single composite-type argument the same name as any of the fields of
that composite type. If there is ambiguity, the field-name
! interpretation will be preferred, so that such a function could not be
! called without tricks. One way to force the function interpretation is
! to schema-qualify the function name, that is, write
<literal><replaceable>schema</replaceable>.<replaceable>func</replaceable>(<replaceable>compositevalue</replaceable>)</literal>.
</para>
</tip>
--- 441,452 ----
Because of this behavior, it's unwise to give a function that takes a
single composite-type argument the same name as any of the fields of
that composite type. If there is ambiguity, the field-name
! interpretation will be chosen if field-name syntax is used, while the
! function will be chosen if function-call syntax is used. However,
! <productname>PostgreSQL</productname> versions before 11 always chose the
! field-name interpretation, unless the syntax of the call required it to
! be a function call. One way to force the function interpretation in
! older versions is to schema-qualify the function name, that is, write
<literal><replaceable>schema</replaceable>.<replaceable>func</replaceable>(<replaceable>compositevalue</replaceable>)</literal>.
</para>
</tip>
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 21ddd5b..abe1dbc 100644
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
*************** static Node *ParseComplexProjection(Pars
*** 49,63 ****
* For historical reasons, Postgres tries to treat the notations tab.col
* and col(tab) as equivalent: if a single-argument function call has an
* argument of complex type and the (unqualified) function name matches
! * any attribute of the type, we take it as a column projection. Conversely
! * a function of a single complex-type argument can be written like a
! * column reference, allowing functions to act like computed columns.
*
* Hence, both cases come through here. If fn is null, we're dealing with
! * column syntax not function syntax, but in principle that should not
! * affect the lookup behavior, only which error messages we deliver.
! * The FuncCall struct is needed however to carry various decoration that
! * applies to aggregate and window functions.
*
* Also, when fn is null, we return NULL on failure rather than
* reporting a no-such-function error.
--- 49,65 ----
* For historical reasons, Postgres tries to treat the notations tab.col
* and col(tab) as equivalent: if a single-argument function call has an
* argument of complex type and the (unqualified) function name matches
! * any attribute of the type, we can interpret it as a column projection.
! * Conversely a function of a single complex-type argument can be written
! * like a column reference, allowing functions to act like computed columns.
! *
! * If both interpretations are possible, we prefer the one matching the
! * syntactic form, but otherwise the form does not matter.
*
* Hence, both cases come through here. If fn is null, we're dealing with
! * column syntax not function syntax. In the function-syntax case,
! * the FuncCall struct is needed to carry various decoration that applies
! * to aggregate and window functions.
*
* Also, when fn is null, we return NULL on failure rather than
* reporting a no-such-function error.
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 84,89 ****
--- 86,92 ----
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
WindowDef *over = (fn ? fn->over : NULL);
+ bool could_be_projection;
Oid rettype;
Oid funcid;
ListCell *l;
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 202,237 ****
}
/*
! * Check for column projection: if function has one argument, and that
! * argument is of complex type, and function name is not qualified, then
! * the "function call" could be a projection. We also check that there
! * wasn't any aggregate or variadic decoration, nor an argument name.
*/
! if (nargs == 1 && !proc_call &&
! agg_order == NIL && agg_filter == NULL && !agg_star &&
! !agg_distinct && over == NULL && !func_variadic && argnames == NIL &&
! list_length(funcname) == 1)
! {
! Oid argtype = actual_arg_types[0];
! if (argtype == RECORDOID || ISCOMPLEX(argtype))
! {
! retval = ParseComplexProjection(pstate,
! strVal(linitial(funcname)),
! first_arg,
! location);
! if (retval)
! return retval;
! /*
! * If ParseComplexProjection doesn't recognize it as a projection,
! * just press on.
! */
! }
}
/*
- * Okay, it's not a column projection, so it must really be a function.
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles inheritance, and
* returns the funcid and type and set or singleton status of the
--- 205,243 ----
}
/*
! * Decide whether it's legitimate to consider the construct to be a column
! * projection. For that, there has to be a single argument of complex
! * type, the function name must not be qualified, and there cannot be any
! * syntactic decoration that'd require it to be a function (such as
! * aggregate or variadic decoration, or named arguments).
*/
! could_be_projection = (nargs == 1 && !proc_call &&
! agg_order == NIL && agg_filter == NULL &&
! !agg_star && !agg_distinct && over == NULL &&
! !func_variadic && argnames == NIL &&
! list_length(funcname) == 1 &&
! (actual_arg_types[0] == RECORDOID ||
! ISCOMPLEX(actual_arg_types[0])));
! /*
! * If it's column syntax, check for column projection case first.
! */
! if (could_be_projection && is_column)
! {
! retval = ParseComplexProjection(pstate,
! strVal(linitial(funcname)),
! first_arg,
! location);
! if (retval)
! return retval;
! /*
! * If ParseComplexProjection doesn't recognize it as a projection,
! * just press on.
! */
}
/*
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles inheritance, and
* returns the funcid and type and set or singleton status of the
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 334,340 ****
}
/*
! * So far so good, so do some routine-type-specific processing.
*/
if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
{
--- 340,346 ----
}
/*
! * So far so good, so do some fdresult-type-specific processing.
*/
if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
{
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 524,553 ****
actual_arg_types[0], rettype, -1,
COERCION_EXPLICIT, COERCE_EXPLICIT_CALL, location);
}
else
{
/*
! * Oops. Time to die.
! *
! * If we are dealing with the attribute notation rel.function, let the
! * caller handle failure.
*/
if (is_column)
return NULL;
/*
! * Else generate a detailed complaint for a function
*/
! if (fdresult == FUNCDETAIL_MULTIPLE)
! ereport(ERROR,
! (errcode(ERRCODE_AMBIGUOUS_FUNCTION),
! errmsg("function %s is not unique",
! func_signature_string(funcname, nargs, argnames,
! actual_arg_types)),
! errhint("Could not choose a best candidate function. "
! "You might need to add explicit type casts."),
! parser_errposition(pstate, location)));
! else if (list_length(agg_order) > 1 && !agg_within_group)
{
/* It's agg(x, ORDER BY y,z) ... perhaps misplaced ORDER BY */
ereport(ERROR,
--- 530,584 ----
actual_arg_types[0], rettype, -1,
COERCION_EXPLICIT, COERCE_EXPLICIT_CALL, location);
}
+ else if (fdresult == FUNCDETAIL_MULTIPLE)
+ {
+ /*
+ * We found multiple possible functional matches. If we are dealing
+ * with attribute notation, return failure, letting the caller report
+ * "no such column" (we already determined there wasn't one). If
+ * dealing with function notation, report "ambiguous function",
+ * regardless of whether there's also a column by this name.
+ */
+ if (is_column)
+ return NULL;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_AMBIGUOUS_FUNCTION),
+ errmsg("function %s is not unique",
+ func_signature_string(funcname, nargs, argnames,
+ actual_arg_types)),
+ errhint("Could not choose a best candidate function. "
+ "You might need to add explicit type casts."),
+ parser_errposition(pstate, location)));
+ }
else
{
/*
! * Not found as a function. If we are dealing with attribute
! * notation, return failure, letting the caller report "no such
! * column" (we already determined there wasn't one).
*/
if (is_column)
return NULL;
/*
! * Check for column projection interpretation, since we didn't before.
*/
! if (could_be_projection)
! {
! retval = ParseComplexProjection(pstate,
! strVal(linitial(funcname)),
! first_arg,
! location);
! if (retval)
! return retval;
! }
!
! /*
! * No function, and no column either. Since we're dealing with
! * function notation, report "function does not exist".
! */
! if (list_length(agg_order) > 1 && !agg_within_group)
{
/* It's agg(x, ORDER BY y,z) ... perhaps misplaced ORDER BY */
ereport(ERROR,
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 45cb6ff..30053d0 100644
*** a/src/test/regress/expected/rowtypes.out
--- b/src/test/regress/expected/rowtypes.out
*************** ERROR: could not identify column "text"
*** 798,803 ****
--- 798,847 ----
LINE 1: select (row('Jim', 'Beam')).text;
^
--
+ -- Check the equivalence of functional and column notation
+ --
+ insert into fullname values ('Joe', 'Blow');
+ select f.last from fullname f;
+ last
+ ------
+ Blow
+ (1 row)
+
+ select last(f) from fullname f;
+ last
+ ------
+ Blow
+ (1 row)
+
+ create function longname(fullname) returns text language sql
+ as $$select $1.first || ' ' || $1.last$$;
+ select f.longname from fullname f;
+ longname
+ ----------
+ Joe Blow
+ (1 row)
+
+ select longname(f) from fullname f;
+ longname
+ ----------
+ Joe Blow
+ (1 row)
+
+ -- Starting in v11, the notational form does matter if there's ambiguity
+ alter table fullname add column longname text;
+ select f.longname from fullname f;
+ longname
+ ----------
+
+ (1 row)
+
+ select longname(f) from fullname f;
+ longname
+ ----------
+ Joe Blow
+ (1 row)
+
+ --
-- Test that composite values are seen to have the correct column names
-- (bug #11210 and other reports)
--
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index 305639f..faf2e10 100644
*** a/src/test/regress/sql/rowtypes.sql
--- b/src/test/regress/sql/rowtypes.sql
*************** select text(row('Jim', 'Beam')); -- err
*** 346,351 ****
--- 346,371 ----
select (row('Jim', 'Beam')).text; -- error
--
+ -- Check the equivalence of functional and column notation
+ --
+ insert into fullname values ('Joe', 'Blow');
+
+ select f.last from fullname f;
+ select last(f) from fullname f;
+
+ create function longname(fullname) returns text language sql
+ as $$select $1.first || ' ' || $1.last$$;
+
+ select f.longname from fullname f;
+ select longname(f) from fullname f;
+
+ -- Starting in v11, the notational form does matter if there's ambiguity
+ alter table fullname add column longname text;
+
+ select f.longname from fullname f;
+ select longname(f) from fullname f;
+
+ --
-- Test that composite values are seen to have the correct column names
-- (bug #11210 and other reports)
--
I wrote:
> So I'm now pretty well convinced that this is a good change and we
> should slip it into v11.
I wrote a test case and was about ready to commit this, when I started
wondering about the behavior for fdresult == FUNCDETAIL_MULTIPLE.
That is, suppose that the notation f(x) matches more than one candidate
function f, and there's also a column named f. The old code would have
chosen the column interpretation, and so would the patch I posted
previously. But I don't really see how we can square that behavior with
documenting that "we prefer the function interpretation for functional
notation". I think you should get a "function is not unique" error in
such a case, not silently picking the column instead. If it's the
column you want, write it like a column.
So the attached updated patch rearranges the error handling logic to
make that happen.
BTW, while trying to make a test case I was reminded that CREATE FUNCTION
tries to prevent this situation from arising:
regression=# create table t1 (f1 int);
CREATE TABLE
regression=# create function f1(t1) returns int as 'select 42' language sql;
ERROR: "f1" is already an attribute of type t1
That's totally non-bulletproof, though, since you can still create the
situation by renaming, or by adding the conflicting column after you
create the function; not to mention Neil's original problem where the
conflicting function is declared to take record or some such.
Furthermore, this behavior itself creates a dump/reload hazard, since
once you've done one of those things pg_dump will produce output that
fails this check.
While I've not done anything about it here, I think we should just
remove that error check. With this patch, functions and columns can
coexist reasonably peacefully, so we shouldn't need bogus hacks that
try to prevent it.
regards, tom lane
diff --git a/doc/src/sgml/rowtypes.sgml b/doc/src/sgml/rowtypes.sgml
index 3f24293..2f924b1 100644
*** a/doc/src/sgml/rowtypes.sgml
--- b/doc/src/sgml/rowtypes.sgml
*************** SELECT c.somefunc FROM inventory_item c;
*** 441,449 ****
Because of this behavior, it's unwise to give a function that takes a
single composite-type argument the same name as any of the fields of
that composite type. If there is ambiguity, the field-name
! interpretation will be preferred, so that such a function could not be
! called without tricks. One way to force the function interpretation is
! to schema-qualify the function name, that is, write
<literal><replaceable>schema</replaceable>.<replaceable>func</replaceable>(<replaceable>compositevalue</replaceable>)</literal>.
</para>
</tip>
--- 441,452 ----
Because of this behavior, it's unwise to give a function that takes a
single composite-type argument the same name as any of the fields of
that composite type. If there is ambiguity, the field-name
! interpretation will be chosen if field-name syntax is used, while the
! function will be chosen if function-call syntax is used. However,
! <productname>PostgreSQL</productname> versions before 11 always chose the
! field-name interpretation, unless the syntax of the call required it to
! be a function call. One way to force the function interpretation in
! older versions is to schema-qualify the function name, that is, write
<literal><replaceable>schema</replaceable>.<replaceable>func</replaceable>(<replaceable>compositevalue</replaceable>)</literal>.
</para>
</tip>
diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c
index 21ddd5b..abe1dbc 100644
*** a/src/backend/parser/parse_func.c
--- b/src/backend/parser/parse_func.c
*************** static Node *ParseComplexProjection(Pars
*** 49,63 ****
* For historical reasons, Postgres tries to treat the notations tab.col
* and col(tab) as equivalent: if a single-argument function call has an
* argument of complex type and the (unqualified) function name matches
! * any attribute of the type, we take it as a column projection. Conversely
! * a function of a single complex-type argument can be written like a
! * column reference, allowing functions to act like computed columns.
*
* Hence, both cases come through here. If fn is null, we're dealing with
! * column syntax not function syntax, but in principle that should not
! * affect the lookup behavior, only which error messages we deliver.
! * The FuncCall struct is needed however to carry various decoration that
! * applies to aggregate and window functions.
*
* Also, when fn is null, we return NULL on failure rather than
* reporting a no-such-function error.
--- 49,65 ----
* For historical reasons, Postgres tries to treat the notations tab.col
* and col(tab) as equivalent: if a single-argument function call has an
* argument of complex type and the (unqualified) function name matches
! * any attribute of the type, we can interpret it as a column projection.
! * Conversely a function of a single complex-type argument can be written
! * like a column reference, allowing functions to act like computed columns.
! *
! * If both interpretations are possible, we prefer the one matching the
! * syntactic form, but otherwise the form does not matter.
*
* Hence, both cases come through here. If fn is null, we're dealing with
! * column syntax not function syntax. In the function-syntax case,
! * the FuncCall struct is needed to carry various decoration that applies
! * to aggregate and window functions.
*
* Also, when fn is null, we return NULL on failure rather than
* reporting a no-such-function error.
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 84,89 ****
--- 86,92 ----
bool agg_distinct = (fn ? fn->agg_distinct : false);
bool func_variadic = (fn ? fn->func_variadic : false);
WindowDef *over = (fn ? fn->over : NULL);
+ bool could_be_projection;
Oid rettype;
Oid funcid;
ListCell *l;
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 202,237 ****
}
/*
! * Check for column projection: if function has one argument, and that
! * argument is of complex type, and function name is not qualified, then
! * the "function call" could be a projection. We also check that there
! * wasn't any aggregate or variadic decoration, nor an argument name.
*/
! if (nargs == 1 && !proc_call &&
! agg_order == NIL && agg_filter == NULL && !agg_star &&
! !agg_distinct && over == NULL && !func_variadic && argnames == NIL &&
! list_length(funcname) == 1)
! {
! Oid argtype = actual_arg_types[0];
! if (argtype == RECORDOID || ISCOMPLEX(argtype))
! {
! retval = ParseComplexProjection(pstate,
! strVal(linitial(funcname)),
! first_arg,
! location);
! if (retval)
! return retval;
! /*
! * If ParseComplexProjection doesn't recognize it as a projection,
! * just press on.
! */
! }
}
/*
- * Okay, it's not a column projection, so it must really be a function.
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles inheritance, and
* returns the funcid and type and set or singleton status of the
--- 205,243 ----
}
/*
! * Decide whether it's legitimate to consider the construct to be a column
! * projection. For that, there has to be a single argument of complex
! * type, the function name must not be qualified, and there cannot be any
! * syntactic decoration that'd require it to be a function (such as
! * aggregate or variadic decoration, or named arguments).
*/
! could_be_projection = (nargs == 1 && !proc_call &&
! agg_order == NIL && agg_filter == NULL &&
! !agg_star && !agg_distinct && over == NULL &&
! !func_variadic && argnames == NIL &&
! list_length(funcname) == 1 &&
! (actual_arg_types[0] == RECORDOID ||
! ISCOMPLEX(actual_arg_types[0])));
! /*
! * If it's column syntax, check for column projection case first.
! */
! if (could_be_projection && is_column)
! {
! retval = ParseComplexProjection(pstate,
! strVal(linitial(funcname)),
! first_arg,
! location);
! if (retval)
! return retval;
! /*
! * If ParseComplexProjection doesn't recognize it as a projection,
! * just press on.
! */
}
/*
* func_get_detail looks up the function in the catalogs, does
* disambiguation for polymorphic functions, handles inheritance, and
* returns the funcid and type and set or singleton status of the
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 334,340 ****
}
/*
! * So far so good, so do some routine-type-specific processing.
*/
if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
{
--- 340,346 ----
}
/*
! * So far so good, so do some fdresult-type-specific processing.
*/
if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE)
{
*************** ParseFuncOrColumn(ParseState *pstate, Li
*** 524,553 ****
actual_arg_types[0], rettype, -1,
COERCION_EXPLICIT, COERCE_EXPLICIT_CALL, location);
}
else
{
/*
! * Oops. Time to die.
! *
! * If we are dealing with the attribute notation rel.function, let the
! * caller handle failure.
*/
if (is_column)
return NULL;
/*
! * Else generate a detailed complaint for a function
*/
! if (fdresult == FUNCDETAIL_MULTIPLE)
! ereport(ERROR,
! (errcode(ERRCODE_AMBIGUOUS_FUNCTION),
! errmsg("function %s is not unique",
! func_signature_string(funcname, nargs, argnames,
! actual_arg_types)),
! errhint("Could not choose a best candidate function. "
! "You might need to add explicit type casts."),
! parser_errposition(pstate, location)));
! else if (list_length(agg_order) > 1 && !agg_within_group)
{
/* It's agg(x, ORDER BY y,z) ... perhaps misplaced ORDER BY */
ereport(ERROR,
--- 530,584 ----
actual_arg_types[0], rettype, -1,
COERCION_EXPLICIT, COERCE_EXPLICIT_CALL, location);
}
+ else if (fdresult == FUNCDETAIL_MULTIPLE)
+ {
+ /*
+ * We found multiple possible functional matches. If we are dealing
+ * with attribute notation, return failure, letting the caller report
+ * "no such column" (we already determined there wasn't one). If
+ * dealing with function notation, report "ambiguous function",
+ * regardless of whether there's also a column by this name.
+ */
+ if (is_column)
+ return NULL;
+
+ ereport(ERROR,
+ (errcode(ERRCODE_AMBIGUOUS_FUNCTION),
+ errmsg("function %s is not unique",
+ func_signature_string(funcname, nargs, argnames,
+ actual_arg_types)),
+ errhint("Could not choose a best candidate function. "
+ "You might need to add explicit type casts."),
+ parser_errposition(pstate, location)));
+ }
else
{
/*
! * Not found as a function. If we are dealing with attribute
! * notation, return failure, letting the caller report "no such
! * column" (we already determined there wasn't one).
*/
if (is_column)
return NULL;
/*
! * Check for column projection interpretation, since we didn't before.
*/
! if (could_be_projection)
! {
! retval = ParseComplexProjection(pstate,
! strVal(linitial(funcname)),
! first_arg,
! location);
! if (retval)
! return retval;
! }
!
! /*
! * No function, and no column either. Since we're dealing with
! * function notation, report "function does not exist".
! */
! if (list_length(agg_order) > 1 && !agg_within_group)
{
/* It's agg(x, ORDER BY y,z) ... perhaps misplaced ORDER BY */
ereport(ERROR,
diff --git a/src/test/regress/expected/rowtypes.out b/src/test/regress/expected/rowtypes.out
index 45cb6ff..30053d0 100644
*** a/src/test/regress/expected/rowtypes.out
--- b/src/test/regress/expected/rowtypes.out
*************** ERROR: could not identify column "text"
*** 798,803 ****
--- 798,847 ----
LINE 1: select (row('Jim', 'Beam')).text;
^
--
+ -- Check the equivalence of functional and column notation
+ --
+ insert into fullname values ('Joe', 'Blow');
+ select f.last from fullname f;
+ last
+ ------
+ Blow
+ (1 row)
+
+ select last(f) from fullname f;
+ last
+ ------
+ Blow
+ (1 row)
+
+ create function longname(fullname) returns text language sql
+ as $$select $1.first || ' ' || $1.last$$;
+ select f.longname from fullname f;
+ longname
+ ----------
+ Joe Blow
+ (1 row)
+
+ select longname(f) from fullname f;
+ longname
+ ----------
+ Joe Blow
+ (1 row)
+
+ -- Starting in v11, the notational form does matter if there's ambiguity
+ alter table fullname add column longname text;
+ select f.longname from fullname f;
+ longname
+ ----------
+
+ (1 row)
+
+ select longname(f) from fullname f;
+ longname
+ ----------
+ Joe Blow
+ (1 row)
+
+ --
-- Test that composite values are seen to have the correct column names
-- (bug #11210 and other reports)
--
diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql
index 305639f..faf2e10 100644
*** a/src/test/regress/sql/rowtypes.sql
--- b/src/test/regress/sql/rowtypes.sql
*************** select text(row('Jim', 'Beam')); -- err
*** 346,351 ****
--- 346,371 ----
select (row('Jim', 'Beam')).text; -- error
--
+ -- Check the equivalence of functional and column notation
+ --
+ insert into fullname values ('Joe', 'Blow');
+
+ select f.last from fullname f;
+ select last(f) from fullname f;
+
+ create function longname(fullname) returns text language sql
+ as $$select $1.first || ' ' || $1.last$$;
+
+ select f.longname from fullname f;
+ select longname(f) from fullname f;
+
+ -- Starting in v11, the notational form does matter if there's ambiguity
+ alter table fullname add column longname text;
+
+ select f.longname from fullname f;
+ select longname(f) from fullname f;
+
+ --
-- Test that composite values are seen to have the correct column names
-- (bug #11210 and other reports)
--