Re: PostgreSQL 10 changes in exclusion constraints - did something change? CASE WHEN behavior oddity

On 2017-06-09 17:33:45 -0400, Tom Lane wrote:
> diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
> index cbcd6cf..98bcfa0 100644
> --- a/src/backend/catalog/information_schema.sql
> +++ b/src/backend/catalog/information_schema.sql
> @@ -2936,12 +2936,14 @@ CREATE VIEW user_mapping_options AS
> SELECT authorization_identifier,
> foreign_server_catalog,
> foreign_server_name,
> - CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name,
> + CAST(opts.option_name AS sql_identifier) AS option_name,
> CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user)
> OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE'))
> - OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value
> + OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user)
> + THEN opts.option_value
> ELSE NULL END AS character_data) AS option_value
> - FROM _pg_user_mappings um;
> + FROM _pg_user_mappings um,
> + pg_options_to_table(um.umoptions) opts;

This really is a lot better...

> GRANT SELECT ON user_mapping_options TO PUBLIC;
>
> diff --git a/src/backend/executor/functions.c b/sindex a35ba32..89aea2f 100644
> --- a/src/backend/executor/functions.c
> +++ b/src/backend/executor/functions.c
> @@ -388,6 +388,7 @@ sql_fn_post_column_ref(ParseState *pstat
> param = ParseFuncOrColumn(pstate,
> list_make1(subfield),
> list_make1(param),
> + pstate->p_last_srf,
> NULL,
> cref->location);
> }
> diff --git a/src/backend/parser/parse_aindex efe1c37..5241fd2 100644
> --- a/src/backend/parser/parse_agg.c
> +++ b/src/backend/parser/parse_agg.c
> @@ -705,6 +705,13 @@ check_agg_arguments_walker(Node *node,
> }
> /* Continue and descend into subtree */
> }
> + /* We can throw error on sight for a set-returning function */
> + if ((IsA(node, FuncExpr) &&((FuncExpr *) node)->funcretset) ||
> + (IsA(node, OpExpr) &&((OpExpr *) node)->opretset))
> + ereport(ERROR,
> + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
> + errmsg("aggregate function calls cannot contain set-returning function calls"),
> + parser_errposition(context->pstate, exprLocation(node))));

Possibly too hard to be precise enough in a hint, but a number of these
could benefit from one suggesting moving things into FROM, using
LATERAL.

I'm kinda positively surprised at how non-invasive this turned out, I'd
afraid there'd be a lot more verbosity to it. I think the improved
error messages (message & location), are quite worthwhile an their own.

Greetings,

Andres Freund