diff options
Diffstat (limited to 'src')
34 files changed, 1232 insertions, 195 deletions
diff --git a/src/backend/catalog/pg_aggregate.c b/src/backend/catalog/pg_aggregate.c index 89f23d0add8..5197076c760 100644 --- a/src/backend/catalog/pg_aggregate.c +++ b/src/backend/catalog/pg_aggregate.c @@ -622,6 +622,7 @@ AggregateCreate(const char *aggName, InvalidOid, /* no validator */ "aggregate_dummy", /* placeholder (no such proc) */ NULL, /* probin */ + NULL, /* prosqlbody */ PROKIND_AGGREGATE, false, /* security invoker (currently not * definable for agg) */ diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index e14eee5a19e..05de377ba9c 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -32,6 +32,7 @@ #include "mb/pg_wchar.h" #include "miscadmin.h" #include "nodes/nodeFuncs.h" +#include "parser/analyze.h" #include "parser/parse_coerce.h" #include "parser/parse_type.h" #include "tcop/pquery.h" @@ -76,6 +77,7 @@ ProcedureCreate(const char *procedureName, Oid languageValidator, const char *prosrc, const char *probin, + Node *prosqlbody, char prokind, bool security_definer, bool isLeakProof, @@ -119,7 +121,7 @@ ProcedureCreate(const char *procedureName, /* * sanity checks */ - Assert(PointerIsValid(prosrc)); + Assert(PointerIsValid(prosrc) || PointerIsValid(prosqlbody)); parameterCount = parameterTypes->dim1; if (parameterCount < 0 || parameterCount > FUNC_MAX_ARGS) @@ -334,11 +336,18 @@ ProcedureCreate(const char *procedureName, values[Anum_pg_proc_protrftypes - 1] = trftypes; else nulls[Anum_pg_proc_protrftypes - 1] = true; - values[Anum_pg_proc_prosrc - 1] = CStringGetTextDatum(prosrc); + if (prosrc) + values[Anum_pg_proc_prosrc - 1] = CStringGetTextDatum(prosrc); + else + nulls[Anum_pg_proc_prosrc - 1] = true; if (probin) values[Anum_pg_proc_probin - 1] = CStringGetTextDatum(probin); else nulls[Anum_pg_proc_probin - 1] = true; + if (prosqlbody) + values[Anum_pg_proc_prosqlbody - 1] = CStringGetTextDatum(nodeToString(prosqlbody)); + else + nulls[Anum_pg_proc_prosqlbody - 1] = true; if (proconfig != PointerGetDatum(NULL)) values[Anum_pg_proc_proconfig - 1] = proconfig; else @@ -638,6 +647,10 @@ ProcedureCreate(const char *procedureName, record_object_address_dependencies(&myself, addrs, DEPENDENCY_NORMAL); free_object_addresses(addrs); + /* dependency on SQL routine body */ + if (languageObjectId == SQLlanguageId && prosqlbody) + recordDependencyOnExpr(&myself, prosqlbody, NIL, DEPENDENCY_NORMAL); + /* dependency on parameter default expressions */ if (parameterDefaults) recordDependencyOnExpr(&myself, (Node *) parameterDefaults, @@ -861,61 +874,81 @@ fmgr_sql_validator(PG_FUNCTION_ARGS) /* Postpone body checks if !check_function_bodies */ if (check_function_bodies) { - tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosrc, &isnull); - if (isnull) - elog(ERROR, "null prosrc"); - - prosrc = TextDatumGetCString(tmp); - /* * Setup error traceback support for ereport(). */ callback_arg.proname = NameStr(proc->proname); - callback_arg.prosrc = prosrc; + callback_arg.prosrc = NULL; sqlerrcontext.callback = sql_function_parse_error_callback; sqlerrcontext.arg = (void *) &callback_arg; sqlerrcontext.previous = error_context_stack; error_context_stack = &sqlerrcontext; - /* - * We can't do full prechecking of the function definition if there - * are any polymorphic input types, because actual datatypes of - * expression results will be unresolvable. The check will be done at - * runtime instead. - * - * We can run the text through the raw parser though; this will at - * least catch silly syntactic errors. - */ - raw_parsetree_list = pg_parse_query(prosrc); + tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosrc, &isnull); + if (isnull) + { + Node *n; - if (!haspolyarg) + tmp = SysCacheGetAttr(PROCOID, tuple, Anum_pg_proc_prosqlbody, &isnull); + if (isnull) + elog(ERROR, "null prosrc and prosqlbody"); + + n = stringToNode(TextDatumGetCString(tmp)); + if (IsA(n, List)) + querytree_list = castNode(List, n); + else + querytree_list = list_make1(list_make1(n)); + } + else { + prosrc = TextDatumGetCString(tmp); + + callback_arg.prosrc = prosrc; + /* - * OK to do full precheck: analyze and rewrite the queries, then - * verify the result type. + * We can't do full prechecking of the function definition if there + * are any polymorphic input types, because actual datatypes of + * expression results will be unresolvable. The check will be done at + * runtime instead. + * + * We can run the text through the raw parser though; this will at + * least catch silly syntactic errors. */ - SQLFunctionParseInfoPtr pinfo; - Oid rettype; - TupleDesc rettupdesc; - - /* But first, set up parameter information */ - pinfo = prepare_sql_fn_parse_info(tuple, NULL, InvalidOid); + raw_parsetree_list = pg_parse_query(prosrc); - querytree_list = NIL; - foreach(lc, raw_parsetree_list) + if (!haspolyarg) { - RawStmt *parsetree = lfirst_node(RawStmt, lc); - List *querytree_sublist; - - querytree_sublist = pg_analyze_and_rewrite_params(parsetree, - prosrc, - (ParserSetupHook) sql_fn_parser_setup, - pinfo, - NULL); - querytree_list = lappend(querytree_list, - querytree_sublist); + /* + * OK to do full precheck: analyze and rewrite the queries, then + * verify the result type. + */ + SQLFunctionParseInfoPtr pinfo; + + /* But first, set up parameter information */ + pinfo = prepare_sql_fn_parse_info(tuple, NULL, InvalidOid); + + querytree_list = NIL; + foreach(lc, raw_parsetree_list) + { + RawStmt *parsetree = lfirst_node(RawStmt, lc); + List *querytree_sublist; + + querytree_sublist = pg_analyze_and_rewrite_params(parsetree, + prosrc, + (ParserSetupHook) sql_fn_parser_setup, + pinfo, + NULL); + querytree_list = lappend(querytree_list, + querytree_sublist); + } } + } + + if (!haspolyarg) + { + Oid rettype; + TupleDesc rettupdesc; check_sql_fn_statements(querytree_list); @@ -968,6 +1001,9 @@ function_parse_error_transpose(const char *prosrc) int newerrposition; const char *queryText; + if (!prosrc) + return false; + /* * Nothing to do unless we are dealing with a syntax error that has a * cursor position. diff --git a/src/backend/commands/aggregatecmds.c b/src/backend/commands/aggregatecmds.c index 69c50ac0877..046cf2df08f 100644 --- a/src/backend/commands/aggregatecmds.c +++ b/src/backend/commands/aggregatecmds.c @@ -312,9 +312,11 @@ DefineAggregate(ParseState *pstate, InvalidOid, OBJECT_AGGREGATE, ¶meterTypes, + NULL, &allParameterTypes, ¶meterModes, ¶meterNames, + NULL, ¶meterDefaults, &variadicArgType, &requiredResultType); diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 7a4e104623b..199029b7a85 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -53,15 +53,18 @@ #include "commands/proclang.h" #include "executor/execdesc.h" #include "executor/executor.h" +#include "executor/functions.h" #include "funcapi.h" #include "miscadmin.h" #include "optimizer/optimizer.h" +#include "parser/analyze.h" #include "parser/parse_coerce.h" #include "parser/parse_collate.h" #include "parser/parse_expr.h" #include "parser/parse_func.h" #include "parser/parse_type.h" #include "pgstat.h" +#include "tcop/utility.h" #include "utils/acl.h" #include "utils/builtins.h" #include "utils/fmgroids.h" @@ -186,9 +189,11 @@ interpret_function_parameter_list(ParseState *pstate, Oid languageOid, ObjectType objtype, oidvector **parameterTypes, + List **parameterTypes_list, ArrayType **allParameterTypes, ArrayType **parameterModes, ArrayType **parameterNames, + List **inParameterNames_list, List **parameterDefaults, Oid *variadicArgType, Oid *requiredResultType) @@ -283,7 +288,11 @@ interpret_function_parameter_list(ParseState *pstate, /* handle input parameters */ if (fp->mode != FUNC_PARAM_OUT && fp->mode != FUNC_PARAM_TABLE) + { isinput = true; + if (parameterTypes_list) + *parameterTypes_list = lappend_oid(*parameterTypes_list, toid); + } /* handle signature parameters */ if (fp->mode == FUNC_PARAM_IN || fp->mode == FUNC_PARAM_INOUT || @@ -372,6 +381,9 @@ interpret_function_parameter_list(ParseState *pstate, have_names = true; } + if (inParameterNames_list) + *inParameterNames_list = lappend(*inParameterNames_list, makeString(fp->name ? fp->name : pstrdup(""))); + if (fp->defexpr) { Node *def; @@ -786,28 +798,10 @@ compute_function_attributes(ParseState *pstate, defel->defname); } - /* process required items */ if (as_item) *as = (List *) as_item->arg; - else - { - ereport(ERROR, - (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), - errmsg("no function body specified"))); - *as = NIL; /* keep compiler quiet */ - } - if (language_item) *language = strVal(language_item->arg); - else - { - ereport(ERROR, - (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), - errmsg("no language specified"))); - *language = NULL; /* keep compiler quiet */ - } - - /* process optional items */ if (transform_item) *transform = transform_item->arg; if (windowfunc_item) @@ -856,10 +850,26 @@ compute_function_attributes(ParseState *pstate, */ static void interpret_AS_clause(Oid languageOid, const char *languageName, - char *funcname, List *as, - char **prosrc_str_p, char **probin_str_p) + char *funcname, List *as, Node *sql_body_in, + List *parameterTypes, List *inParameterNames, + char **prosrc_str_p, char **probin_str_p, Node **sql_body_out) { - Assert(as != NIL); + if (!sql_body_in && !as) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("no function body specified"))); + + if (sql_body_in && as) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("duplicate function body specified"))); + + if (sql_body_in && languageOid != SQLlanguageId) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("inline SQL function body only valid for language SQL"))); + + *sql_body_out = NULL; if (languageOid == ClanguageId) { @@ -881,6 +891,76 @@ interpret_AS_clause(Oid languageOid, const char *languageName, *prosrc_str_p = funcname; } } + else if (sql_body_in) + { + SQLFunctionParseInfoPtr pinfo; + + pinfo = (SQLFunctionParseInfoPtr) palloc0(sizeof(SQLFunctionParseInfo)); + + pinfo->fname = funcname; + pinfo->nargs = list_length(parameterTypes); + pinfo->argtypes = (Oid *) palloc(pinfo->nargs * sizeof(Oid)); + pinfo->argnames = (char **) palloc(pinfo->nargs * sizeof(char *)); + for (int i = 0; i < list_length(parameterTypes); i++) + { + char *s = strVal(list_nth(inParameterNames, i)); + + pinfo->argtypes[i] = list_nth_oid(parameterTypes, i); + if (IsPolymorphicType(pinfo->argtypes[i])) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("SQL function with unquoted function body cannot have polymorphic arguments"))); + + if (s[0] != '\0') + pinfo->argnames[i] = s; + else + pinfo->argnames[i] = NULL; + } + + if (IsA(sql_body_in, List)) + { + List *stmts = linitial_node(List, castNode(List, sql_body_in)); + ListCell *lc; + List *transformed_stmts = NIL; + + foreach(lc, stmts) + { + Node *stmt = lfirst(lc); + Query *q; + ParseState *pstate = make_parsestate(NULL); + + sql_fn_parser_setup(pstate, pinfo); + q = transformStmt(pstate, stmt); + if (q->commandType == CMD_UTILITY) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("%s is not yet supported in unquoted SQL function body", + GetCommandTagName(CreateCommandTag(q->utilityStmt)))); + transformed_stmts = lappend(transformed_stmts, q); + free_parsestate(pstate); + } + + *sql_body_out = (Node *) list_make1(transformed_stmts); + } + else + { + Query *q; + ParseState *pstate = make_parsestate(NULL); + + sql_fn_parser_setup(pstate, pinfo); + q = transformStmt(pstate, sql_body_in); + if (q->commandType == CMD_UTILITY) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("%s is not yet supported in unquoted SQL function body", + GetCommandTagName(CreateCommandTag(q->utilityStmt)))); + + *sql_body_out = (Node *) q; + } + + *probin_str_p = NULL; + *prosrc_str_p = NULL; + } else { /* Everything else wants the given string in prosrc. */ @@ -919,6 +999,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) { char *probin_str; char *prosrc_str; + Node *prosqlbody; Oid prorettype; bool returnsSet; char *language; @@ -929,9 +1010,11 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) Oid namespaceId; AclResult aclresult; oidvector *parameterTypes; + List *parameterTypes_list = NIL; ArrayType *allParameterTypes; ArrayType *parameterModes; ArrayType *parameterNames; + List *inParameterNames_list = NIL; List *parameterDefaults; Oid variadicArgType; List *trftypes_list = NIL; @@ -962,6 +1045,8 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) get_namespace_name(namespaceId)); /* Set default attributes */ + as_clause = NIL; + language = NULL; isWindowFunc = false; isStrict = false; security = false; @@ -983,6 +1068,16 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) &proconfig, &procost, &prorows, &prosupport, ¶llel); + if (!language) + { + if (stmt->sql_body) + language = "sql"; + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("no language specified"))); + } + /* Look up the language and validate permissions */ languageTuple = SearchSysCache1(LANGNAME, PointerGetDatum(language)); if (!HeapTupleIsValid(languageTuple)) @@ -1053,9 +1148,11 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) languageOid, stmt->is_procedure ? OBJECT_PROCEDURE : OBJECT_FUNCTION, ¶meterTypes, + ¶meterTypes_list, &allParameterTypes, ¶meterModes, ¶meterNames, + &inParameterNames_list, ¶meterDefaults, &variadicArgType, &requiredResultType); @@ -1112,8 +1209,9 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) trftypes = NULL; } - interpret_AS_clause(languageOid, language, funcname, as_clause, - &prosrc_str, &probin_str); + interpret_AS_clause(languageOid, language, funcname, as_clause, stmt->sql_body, + parameterTypes_list, inParameterNames_list, + &prosrc_str, &probin_str, &prosqlbody); /* * Set default values for COST and ROWS depending on other parameters; @@ -1155,6 +1253,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) languageValidator, prosrc_str, /* converted to text later */ probin_str, /* converted to text later */ + prosqlbody, stmt->is_procedure ? PROKIND_PROCEDURE : (isWindowFunc ? PROKIND_WINDOW : PROKIND_FUNCTION), security, isLeakProof, diff --git a/src/backend/commands/typecmds.c b/src/backend/commands/typecmds.c index 76218fb47ed..e975508ffa2 100644 --- a/src/backend/commands/typecmds.c +++ b/src/backend/commands/typecmds.c @@ -1775,6 +1775,7 @@ makeRangeConstructors(const char *name, Oid namespace, F_FMGR_INTERNAL_VALIDATOR, /* language validator */ prosrc[i], /* prosrc */ NULL, /* probin */ + NULL, /* prosqlbody */ PROKIND_FUNCTION, false, /* security_definer */ false, /* leakproof */ @@ -1839,6 +1840,7 @@ makeMultirangeConstructors(const char *name, Oid namespace, F_FMGR_INTERNAL_VALIDATOR, "multirange_constructor0", /* prosrc */ NULL, /* probin */ + NULL, /* prosqlbody */ PROKIND_FUNCTION, false, /* security_definer */ false, /* leakproof */ @@ -1882,6 +1884,7 @@ makeMultirangeConstructors(const char *name, Oid namespace, F_FMGR_INTERNAL_VALIDATOR, "multirange_constructor1", /* prosrc */ NULL, /* probin */ + NULL, /* prosqlbody */ PROKIND_FUNCTION, false, /* security_definer */ false, /* leakproof */ @@ -1922,6 +1925,7 @@ makeMultirangeConstructors(const char *name, Oid namespace, F_FMGR_INTERNAL_VALIDATOR, "multirange_constructor2", /* prosrc */ NULL, /* probin */ + NULL, /* prosqlbody */ PROKIND_FUNCTION, false, /* security_definer */ false, /* leakproof */ diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c index 7bb752ace3a..642683843ed 100644 --- a/src/backend/executor/functions.c +++ b/src/backend/executor/functions.c @@ -26,6 +26,7 @@ #include "parser/parse_coerce.h" #include "parser/parse_collate.h" #include "parser/parse_func.h" +#include "rewrite/rewriteHandler.h" #include "storage/proc.h" #include "tcop/utility.h" #include "utils/builtins.h" @@ -128,21 +129,6 @@ typedef struct typedef SQLFunctionCache *SQLFunctionCachePtr; -/* - * Data structure needed by the parser callback hooks to resolve parameter - * references during parsing of a SQL function's body. This is separate from - * SQLFunctionCache since we sometimes do parsing separately from execution. - */ -typedef struct SQLFunctionParseInfo -{ - char *fname; /* function's name */ - int nargs; /* number of input arguments */ - Oid *argtypes; /* resolved types of input arguments */ - char **argnames; /* names of input arguments; NULL if none */ - /* Note that argnames[i] can be NULL, if some args are unnamed */ - Oid collation; /* function's input collation, if known */ -} SQLFunctionParseInfo; - /* non-export function prototypes */ static Node *sql_fn_param_ref(ParseState *pstate, ParamRef *pref); @@ -607,7 +593,6 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK) HeapTuple procedureTuple; Form_pg_proc procedureStruct; SQLFunctionCachePtr fcache; - List *raw_parsetree_list; List *queryTree_list; List *resulttlist; ListCell *lc; @@ -682,9 +667,6 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK) procedureTuple, Anum_pg_proc_prosrc, &isNull); - if (isNull) - elog(ERROR, "null prosrc for function %u", foid); - fcache->src = TextDatumGetCString(tmp); /* * Parse and rewrite the queries in the function text. Use sublists to @@ -695,20 +677,55 @@ init_sql_fcache(FunctionCallInfo fcinfo, Oid collation, bool lazyEvalOK) * but we'll not worry about it until the module is rewritten to use * plancache.c. */ - raw_parsetree_list = pg_parse_query(fcache->src); - queryTree_list = NIL; - foreach(lc, raw_parsetree_list) + if (isNull) { - RawStmt *parsetree = lfirst_node(RawStmt, lc); - List *queryTree_sublist; - - queryTree_sublist = pg_analyze_and_rewrite_params(parsetree, - fcache->src, - (ParserSetupHook) sql_fn_parser_setup, - fcache->pinfo, - NULL); - queryTree_list = lappend(queryTree_list, queryTree_sublist); + Node *n; + List *stored_query_list; + + tmp = SysCacheGetAttr(PROCOID, + procedureTuple, + Anum_pg_proc_prosqlbody, + &isNull); + if (isNull) + elog(ERROR, "null prosrc and prosqlbody for function %u", foid); + + n = stringToNode(TextDatumGetCString(tmp)); + if (IsA(n, List)) + stored_query_list = linitial_node(List, castNode(List, n)); + else + stored_query_list = list_make1(n); + + foreach(lc, stored_query_list) + { + Query *parsetree = lfirst_node(Query, lc); + List *queryTree_sublist; + + AcquireRewriteLocks(parsetree, true, false); + queryTree_sublist = pg_rewrite_query(parsetree); + queryTree_list = lappend(queryTree_list, queryTree_sublist); + } + } + else + { + List *raw_parsetree_list; + + fcache->src = TextDatumGetCString(tmp); + + raw_parsetree_list = pg_parse_query(fcache->src); + + foreach(lc, raw_parsetree_list) + { + RawStmt *parsetree = lfirst_node(RawStmt, lc); + List *queryTree_sublist; + + queryTree_sublist = pg_analyze_and_rewrite_params(parsetree, + fcache->src, + (ParserSetupHook) sql_fn_parser_setup, + fcache->pinfo, + NULL); + queryTree_list = lappend(queryTree_list, queryTree_sublist); + } } /* diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index ad729d10a8d..fcc5ebb206f 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3171,6 +3171,7 @@ _copyQuery(const Query *from) COPY_SCALAR_FIELD(hasModifyingCTE); COPY_SCALAR_FIELD(hasForUpdate); COPY_SCALAR_FIELD(hasRowSecurity); + COPY_SCALAR_FIELD(isReturn); COPY_NODE_FIELD(cteList); COPY_NODE_FIELD(rtable); COPY_NODE_FIELD(jointree); @@ -3301,6 +3302,16 @@ _copySetOperationStmt(const SetOperationStmt *from) return newnode; } +static ReturnStmt * +_copyReturnStmt(const ReturnStmt *from) +{ + ReturnStmt *newnode = makeNode(ReturnStmt); + + COPY_NODE_FIELD(returnval); + + return newnode; +} + static PLAssignStmt * _copyPLAssignStmt(const PLAssignStmt *from) { @@ -3684,6 +3695,7 @@ _copyCreateFunctionStmt(const CreateFunctionStmt *from) COPY_NODE_FIELD(parameters); COPY_NODE_FIELD(returnType); COPY_NODE_FIELD(options); + COPY_NODE_FIELD(sql_body); return newnode; } @@ -5344,6 +5356,9 @@ copyObjectImpl(const void *from) case T_SetOperationStmt: retval = _copySetOperationStmt(from); break; + case T_ReturnStmt: + retval = _copyReturnStmt(from); + break; case T_PLAssignStmt: retval = _copyPLAssignStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index f6b37af0ecb..936365e09a8 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -970,6 +970,7 @@ _equalQuery(const Query *a, const Query *b) COMPARE_SCALAR_FIELD(hasModifyingCTE); COMPARE_SCALAR_FIELD(hasForUpdate); COMPARE_SCALAR_FIELD(hasRowSecurity); + COMPARE_SCALAR_FIELD(isReturn); COMPARE_NODE_FIELD(cteList); COMPARE_NODE_FIELD(rtable); COMPARE_NODE_FIELD(jointree); @@ -1089,6 +1090,14 @@ _equalSetOperationStmt(const SetOperationStmt *a, const SetOperationStmt *b) } static bool +_equalReturnStmt(const ReturnStmt *a, const ReturnStmt *b) +{ + COMPARE_NODE_FIELD(returnval); + + return true; +} + +static bool _equalPLAssignStmt(const PLAssignStmt *a, const PLAssignStmt *b) { COMPARE_STRING_FIELD(name); @@ -1406,6 +1415,7 @@ _equalCreateFunctionStmt(const CreateFunctionStmt *a, const CreateFunctionStmt * COMPARE_NODE_FIELD(parameters); COMPARE_NODE_FIELD(returnType); COMPARE_NODE_FIELD(options); + COMPARE_NODE_FIELD(sql_body); return true; } @@ -3334,6 +3344,9 @@ equal(const void *a, const void *b) case T_SetOperationStmt: retval = _equalSetOperationStmt(a, b); break; + case T_ReturnStmt: + retval = _equalReturnStmt(a, b); + break; case T_PLAssignStmt: retval = _equalPLAssignStmt(a, b); break; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index fa8f65fbc50..4a8dc2d86dc 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2836,6 +2836,14 @@ _outSelectStmt(StringInfo str, const SelectStmt *node) } static void +_outReturnStmt(StringInfo str, const ReturnStmt *node) +{ + WRITE_NODE_TYPE("RETURN"); + + WRITE_NODE_FIELD(returnval); +} + +static void _outPLAssignStmt(StringInfo str, const PLAssignStmt *node) { WRITE_NODE_TYPE("PLASSIGN"); @@ -3047,6 +3055,7 @@ _outQuery(StringInfo str, const Query *node) WRITE_BOOL_FIELD(hasModifyingCTE); WRITE_BOOL_FIELD(hasForUpdate); WRITE_BOOL_FIELD(hasRowSecurity); + WRITE_BOOL_FIELD(isReturn); WRITE_NODE_FIELD(cteList); WRITE_NODE_FIELD(rtable); WRITE_NODE_FIELD(jointree); @@ -4337,6 +4346,9 @@ outNode(StringInfo str, const void *obj) case T_SelectStmt: _outSelectStmt(str, obj); break; + case T_ReturnStmt: + _outReturnStmt(str, obj); + break; case T_PLAssignStmt: _outPLAssignStmt(str, obj); break; diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index ecce23b747b..99247278513 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -263,6 +263,7 @@ _readQuery(void) READ_BOOL_FIELD(hasModifyingCTE); READ_BOOL_FIELD(hasForUpdate); READ_BOOL_FIELD(hasRowSecurity); + READ_BOOL_FIELD(isReturn); READ_NODE_FIELD(cteList); READ_NODE_FIELD(rtable); READ_NODE_FIELD(jointree); diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index bea1cc4d67e..9a6e3dab834 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -4253,27 +4253,47 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid, ALLOCSET_DEFAULT_SIZES); oldcxt = MemoryContextSwitchTo(mycxt); - /* Fetch the function body */ - tmp = SysCacheGetAttr(PROCOID, - func_tuple, - Anum_pg_proc_prosrc, - &isNull); - if (isNull) - elog(ERROR, "null prosrc for function %u", funcid); - src = TextDatumGetCString(tmp); - /* * Setup error traceback support for ereport(). This is so that we can * finger the function that bad information came from. */ callback_arg.proname = NameStr(funcform->proname); - callback_arg.prosrc = src; + callback_arg.prosrc = NULL; sqlerrcontext.callback = sql_inline_error_callback; sqlerrcontext.arg = (void *) &callback_arg; sqlerrcontext.previous = error_context_stack; error_context_stack = &sqlerrcontext; + /* Fetch the function body */ + tmp = SysCacheGetAttr(PROCOID, + func_tuple, + Anum_pg_proc_prosrc, + &isNull); + if (isNull) + { + Node *n; + List *querytree_list; + + tmp = SysCacheGetAttr(PROCOID, func_tuple, Anum_pg_proc_prosqlbody, &isNull); + if (isNull) + elog(ERROR, "null prosrc and prosqlbody for function %u", funcid); + + n = stringToNode(TextDatumGetCString(tmp)); + if (IsA(n, List)) + querytree_list = linitial_node(List, castNode(List, n)); + else + querytree_list = list_make1(n); + if (list_length(querytree_list) != 1) + goto fail; + querytree = linitial(querytree_list); + } + else + { + src = TextDatumGetCString(tmp); + + callback_arg.prosrc = src; + /* * Set up to handle parameters while parsing the function body. We need a * dummy FuncExpr node containing the already-simplified arguments to pass @@ -4317,6 +4337,7 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid, querytree = transformTopLevelStmt(pstate, linitial(raw_parsetree_list)); free_parsestate(pstate); + } /* * The single command must be a simple "SELECT expression". @@ -4573,12 +4594,15 @@ sql_inline_error_callback(void *arg) int syntaxerrposition; /* If it's a syntax error, convert to internal syntax error report */ - syntaxerrposition = geterrposition(); - if (syntaxerrposition > 0) + if (callback_arg->prosrc) { - errposition(0); - internalerrposition(syntaxerrposition); - internalerrquery(callback_arg->prosrc); + syntaxerrposition = geterrposition(); + if (syntaxerrposition > 0) + { + errposition(0); + internalerrposition(syntaxerrposition); + internalerrquery(callback_arg->prosrc); + } } errcontext("SQL function \"%s\" during inlining", callback_arg->proname); @@ -4690,7 +4714,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) Oid func_oid; HeapTuple func_tuple; Form_pg_proc funcform; - char *src; Datum tmp; bool isNull; MemoryContext oldcxt; @@ -4799,27 +4822,53 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) ALLOCSET_DEFAULT_SIZES); oldcxt = MemoryContextSwitchTo(mycxt); - /* Fetch the function body */ - tmp = SysCacheGetAttr(PROCOID, - func_tuple, - Anum_pg_proc_prosrc, - &isNull); - if (isNull) - elog(ERROR, "null prosrc for function %u", func_oid); - src = TextDatumGetCString(tmp); - /* * Setup error traceback support for ereport(). This is so that we can * finger the function that bad information came from. */ callback_arg.proname = NameStr(funcform->proname); - callback_arg.prosrc = src; + callback_arg.prosrc = NULL; sqlerrcontext.callback = sql_inline_error_callback; sqlerrcontext.arg = (void *) &callback_arg; sqlerrcontext.previous = error_context_stack; error_context_stack = &sqlerrcontext; + /* Fetch the function body */ + tmp = SysCacheGetAttr(PROCOID, + func_tuple, + Anum_pg_proc_prosrc, + &isNull); + if (isNull) + { + Node *n; + + tmp = SysCacheGetAttr(PROCOID, func_tuple, Anum_pg_proc_prosqlbody, &isNull); + if (isNull) + elog(ERROR, "null prosrc and prosqlbody for function %u", func_oid); + + n = stringToNode(TextDatumGetCString(tmp)); + if (IsA(n, List)) + querytree_list = linitial_node(List, castNode(List, n)); + else + querytree_list = list_make1(n); + if (list_length(querytree_list) != 1) + goto fail; + querytree = linitial(querytree_list); + + querytree_list = pg_rewrite_query(querytree); + if (list_length(querytree_list) != 1) + goto fail; + querytree = linitial(querytree_list); + } + else + { + char *src; + + src = TextDatumGetCString(tmp); + + callback_arg.prosrc = src; + /* * Set up to handle parameters while parsing the function body. We can * use the FuncExpr just created as the input for @@ -4830,18 +4879,6 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) fexpr->inputcollid); /* - * Also resolve the actual function result tupdesc, if composite. If the - * function is just declared to return RECORD, dig the info out of the AS - * clause. - */ - functypclass = get_expr_result_type((Node *) fexpr, NULL, &rettupdesc); - if (functypclass == TYPEFUNC_RECORD) - rettupdesc = BuildDescFromLists(rtfunc->funccolnames, - rtfunc->funccoltypes, - rtfunc->funccoltypmods, - rtfunc->funccolcollations); - - /* * Parse, analyze, and rewrite (unlike inline_function(), we can't skip * rewriting here). We can fail as soon as we find more than one query, * though. @@ -4857,6 +4894,19 @@ inline_set_returning_function(PlannerInfo *root, RangeTblEntry *rte) if (list_length(querytree_list) != 1) goto fail; querytree = linitial(querytree_list); + } + + /* + * Also resolve the actual function result tupdesc, if composite. If the + * function is just declared to return RECORD, dig the info out of the AS + * clause. + */ + functypclass = get_expr_result_type((Node *) fexpr, NULL, &rettupdesc); + if (functypclass == TYPEFUNC_RECORD) + rettupdesc = BuildDescFromLists(rtfunc->funccolnames, + rtfunc->funccoltypes, + rtfunc->funccoltypmods, + rtfunc->funccolcollations); /* * The single command must be a plain SELECT. diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 9ddf78dccdb..9f13880d19a 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -71,6 +71,7 @@ static Node *transformSetOperationTree(ParseState *pstate, SelectStmt *stmt, bool isTopLevel, List **targetlist); static void determineRecursiveColTypes(ParseState *pstate, Node *larg, List *nrtargetlist); +static Query *transformReturnStmt(ParseState *pstate, ReturnStmt *stmt); static Query *transformUpdateStmt(ParseState *pstate, UpdateStmt *stmt); static List *transformReturningList(ParseState *pstate, List *returningList); static List *transformUpdateTargetList(ParseState *pstate, @@ -323,6 +324,10 @@ transformStmt(ParseState *pstate, Node *parseTree) } break; + case T_ReturnStmt: + result = transformReturnStmt(pstate, (ReturnStmt *) parseTree); + break; + case T_PLAssignStmt: result = transformPLAssignStmt(pstate, (PLAssignStmt *) parseTree); @@ -2245,6 +2250,36 @@ determineRecursiveColTypes(ParseState *pstate, Node *larg, List *nrtargetlist) /* + * transformReturnStmt - + * transforms a return statement + */ +static Query * +transformReturnStmt(ParseState *pstate, ReturnStmt *stmt) +{ + Query *qry = makeNode(Query); + + qry->commandType = CMD_SELECT; + qry->isReturn = true; + + qry->targetList = list_make1(makeTargetEntry((Expr *) transformExpr(pstate, stmt->returnval, EXPR_KIND_SELECT_TARGET), + 1, NULL, false)); + + if (pstate->p_resolve_unknowns) + resolveTargetListUnknowns(pstate, qry->targetList); + qry->rtable = pstate->p_rtable; + qry->jointree = makeFromExpr(pstate->p_joinlist, NULL); + qry->hasSubLinks = pstate->p_hasSubLinks; + qry->hasWindowFuncs = pstate->p_hasWindowFuncs; + qry->hasTargetSRFs = pstate->p_hasTargetSRFs; + qry->hasAggs = pstate->p_hasAggs; + + assign_query_collations(pstate, qry); + + return qry; +} + + +/* * transformUpdateStmt - * transforms an update statement */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 517bf723784..73494002ad3 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -262,7 +262,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); struct GroupClause *groupclause; } -%type <node> stmt schema_stmt +%type <node> stmt toplevel_stmt schema_stmt routine_body_stmt AlterEventTrigStmt AlterDatabaseStmt AlterDatabaseSetStmt AlterDomainStmt AlterEnumStmt AlterFdwStmt AlterForeignServerStmt AlterGroupStmt @@ -289,9 +289,9 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); GrantStmt GrantRoleStmt ImportForeignSchemaStmt IndexStmt InsertStmt ListenStmt LoadStmt LockStmt NotifyStmt ExplainableStmt PreparableStmt CreateFunctionStmt AlterFunctionStmt ReindexStmt RemoveAggrStmt - RemoveFuncStmt RemoveOperStmt RenameStmt RevokeStmt RevokeRoleStmt + RemoveFuncStmt RemoveOperStmt RenameStmt ReturnStmt RevokeStmt RevokeRoleStmt RuleActionStmt RuleActionStmtOrEmpty RuleStmt - SecLabelStmt SelectStmt TransactionStmt TruncateStmt + SecLabelStmt SelectStmt TransactionStmt TransactionStmtLegacy TruncateStmt UnlistenStmt UpdateStmt VacuumStmt VariableResetStmt VariableSetStmt VariableShowStmt ViewStmt CheckPointStmt CreateConversionStmt @@ -395,14 +395,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type <node> vacuum_relation %type <selectlimit> opt_select_limit select_limit limit_clause -%type <list> parse_toplevel stmtmulti +%type <list> parse_toplevel stmtmulti routine_body_stmt_list OptTableElementList TableElementList OptInherit definition OptTypedTableElementList TypedTableElementList reloptions opt_reloptions OptWith opt_definition func_args func_args_list func_args_with_defaults func_args_with_defaults_list aggr_args aggr_args_list - func_as createfunc_opt_list alterfunc_opt_list + func_as createfunc_opt_list opt_createfunc_opt_list alterfunc_opt_list old_aggr_definition old_aggr_list oper_argtypes RuleActionList RuleActionMulti opt_column_list columnList opt_name_list @@ -428,6 +428,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); vacuum_relation_list opt_vacuum_relation_list drop_option_list +%type <node> opt_routine_body %type <groupclause> group_clause %type <list> group_by_list %type <node> group_by_item empty_grouping_set rollup_clause cube_clause @@ -637,7 +638,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); /* ordinary key words in alphabetical order */ %token <keyword> ABORT_P ABSOLUTE_P ACCESS ACTION ADD_P ADMIN AFTER AGGREGATE ALL ALSO ALTER ALWAYS ANALYSE ANALYZE AND ANY ARRAY AS ASC - ASENSITIVE ASSERTION ASSIGNMENT ASYMMETRIC AT ATTACH ATTRIBUTE AUTHORIZATION + ASENSITIVE ASSERTION ASSIGNMENT ASYMMETRIC ATOMIC AT ATTACH ATTRIBUTE AUTHORIZATION BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT BOOLEAN_P BOTH BREADTH BY @@ -699,7 +700,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); RANGE READ REAL REASSIGN RECHECK RECURSIVE REF REFERENCES REFERENCING REFRESH REINDEX RELATIVE_P RELEASE RENAME REPEATABLE REPLACE REPLICA - RESET RESTART RESTRICT RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP + RESET RESTART RESTRICT RETURN RETURNING RETURNS REVOKE RIGHT ROLE ROLLBACK ROLLUP ROUTINE ROUTINES ROW ROWS RULE SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES @@ -869,7 +870,7 @@ parse_toplevel: * we'd get -1 for the location in such cases. * We also take care to discard empty statements entirely. */ -stmtmulti: stmtmulti ';' stmt +stmtmulti: stmtmulti ';' toplevel_stmt { if ($1 != NIL) { @@ -881,7 +882,7 @@ stmtmulti: stmtmulti ';' stmt else $$ = $1; } - | stmt + | toplevel_stmt { if ($1 != NULL) $$ = list_make1(makeRawStmt($1, 0)); @@ -890,7 +891,16 @@ stmtmulti: stmtmulti ';' stmt } ; -stmt : +/* + * toplevel_stmt includes BEGIN and END. stmt does not include them, because + * those words have different meanings in function bodys. + */ +toplevel_stmt: + stmt + | TransactionStmtLegacy + ; + +stmt: AlterEventTrigStmt | AlterDatabaseStmt | AlterDatabaseSetStmt @@ -7477,7 +7487,7 @@ opt_nulls_order: NULLS_LA FIRST_P { $$ = SORTBY_NULLS_FIRST; } CreateFunctionStmt: CREATE opt_or_replace FUNCTION func_name func_args_with_defaults - RETURNS func_return createfunc_opt_list + RETURNS func_return opt_createfunc_opt_list opt_routine_body { CreateFunctionStmt *n = makeNode(CreateFunctionStmt); n->is_procedure = false; @@ -7486,10 +7496,11 @@ CreateFunctionStmt: n->parameters = $5; n->returnType = $7; n->options = $8; + n->sql_body = $9; $$ = (Node *)n; } | CREATE opt_or_replace FUNCTION func_name func_args_with_defaults - RETURNS TABLE '(' table_func_column_list ')' createfunc_opt_list + RETURNS TABLE '(' table_func_column_list ')' opt_createfunc_opt_list opt_routine_body { CreateFunctionStmt *n = makeNode(CreateFunctionStmt); n->is_procedure = false; @@ -7499,10 +7510,11 @@ CreateFunctionStmt: n->returnType = TableFuncTypeName($9); n->returnType->location = @7; n->options = $11; + n->sql_body = $12; $$ = (Node *)n; } | CREATE opt_or_replace FUNCTION func_name func_args_with_defaults - createfunc_opt_list + opt_createfunc_opt_list opt_routine_body { CreateFunctionStmt *n = makeNode(CreateFunctionStmt); n->is_procedure = false; @@ -7511,10 +7523,11 @@ CreateFunctionStmt: n->parameters = $5; n->returnType = NULL; n->options = $6; + n->sql_body = $7; $$ = (Node *)n; } | CREATE opt_or_replace PROCEDURE func_name func_args_with_defaults - createfunc_opt_list + opt_createfunc_opt_list opt_routine_body { CreateFunctionStmt *n = makeNode(CreateFunctionStmt); n->is_procedure = true; @@ -7523,6 +7536,7 @@ CreateFunctionStmt: n->parameters = $5; n->returnType = NULL; n->options = $6; + n->sql_body = $7; $$ = (Node *)n; } ; @@ -7833,6 +7847,11 @@ aggregate_with_argtypes_list: { $$ = lappend($1, $3); } ; +opt_createfunc_opt_list: + createfunc_opt_list + | /*EMPTY*/ { $$ = NIL; } + ; + createfunc_opt_list: /* Must be at least one to prevent conflict */ createfunc_opt_item { $$ = list_make1($1); } @@ -7944,6 +7963,51 @@ func_as: Sconst { $$ = list_make1(makeString($1)); } } ; +ReturnStmt: RETURN a_expr + { + ReturnStmt *r = makeNode(ReturnStmt); + r->returnval = (Node *) $2; + $$ = (Node *) r; + } + ; + +opt_routine_body: + ReturnStmt + { + $$ = $1; + } + | BEGIN_P ATOMIC routine_body_stmt_list END_P + { + /* + * A compound statement is stored as a single-item list + * containing the list of statements as its member. That + * way, the parse analysis code can tell apart an empty + * body from no body at all. + */ + $$ = (Node *) list_make1($3); + } + | /*EMPTY*/ + { + $$ = NULL; + } + ; + +routine_body_stmt_list: + routine_body_stmt_list routine_body_stmt ';' + { + $$ = lappend($1, $2); + } + | /*EMPTY*/ + { + $$ = NIL; + } + ; + +routine_body_stmt: + stmt + | ReturnStmt + ; + transform_type_list: FOR TYPE_P Typename { $$ = list_make1($3); } | transform_type_list ',' FOR TYPE_P Typename { $$ = lappend($1, $5); } @@ -9897,13 +9961,6 @@ TransactionStmt: n->chain = $3; $$ = (Node *)n; } - | BEGIN_P opt_transaction transaction_mode_list_or_empty - { - TransactionStmt *n = makeNode(TransactionStmt); - n->kind = TRANS_STMT_BEGIN; - n->options = $3; - $$ = (Node *)n; - } | START TRANSACTION transaction_mode_list_or_empty { TransactionStmt *n = makeNode(TransactionStmt); @@ -9919,14 +9976,6 @@ TransactionStmt: n->chain = $3; $$ = (Node *)n; } - | END_P opt_transaction opt_transaction_chain - { - TransactionStmt *n = makeNode(TransactionStmt); - n->kind = TRANS_STMT_COMMIT; - n->options = NIL; - n->chain = $3; - $$ = (Node *)n; - } | ROLLBACK opt_transaction opt_transaction_chain { TransactionStmt *n = makeNode(TransactionStmt); @@ -9993,6 +10042,24 @@ TransactionStmt: } ; +TransactionStmtLegacy: + BEGIN_P opt_transaction transaction_mode_list_or_empty + { + TransactionStmt *n = makeNode(TransactionStmt); + n->kind = TRANS_STMT_BEGIN; + n->options = $3; + $$ = (Node *)n; + } + | END_P opt_transaction opt_transaction_chain + { + TransactionStmt *n = makeNode(TransactionStmt); + n->kind = TRANS_STMT_COMMIT; + n->options = NIL; + n->chain = $3; + $$ = (Node *)n; + } + ; + opt_transaction: WORK | TRANSACTION | /*EMPTY*/ @@ -15429,6 +15496,7 @@ unreserved_keyword: | ASSERTION | ASSIGNMENT | AT + | ATOMIC | ATTACH | ATTRIBUTE | BACKWARD @@ -15631,6 +15699,7 @@ unreserved_keyword: | RESET | RESTART | RESTRICT + | RETURN | RETURNS | REVOKE | ROLE @@ -15938,6 +16007,7 @@ bare_label_keyword: | ASSIGNMENT | ASYMMETRIC | AT + | ATOMIC | ATTACH | ATTRIBUTE | AUTHORIZATION @@ -16212,6 +16282,7 @@ bare_label_keyword: | RESET | RESTART | RESTRICT + | RETURN | RETURNS | REVOKE | RIGHT diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c index ef8fb20429c..825fd55107a 100644 --- a/src/backend/tcop/postgres.c +++ b/src/backend/tcop/postgres.c @@ -191,7 +191,6 @@ static int interactive_getc(void); static int SocketBackend(StringInfo inBuf); static int ReadCommand(StringInfo inBuf); static void forbidden_in_wal_sender(char firstchar); -static List *pg_rewrite_query(Query *query); static bool check_log_statement(List *stmt_list); static int errdetail_execute(List *raw_parsetree_list); static int errdetail_params(ParamListInfo params); @@ -716,7 +715,7 @@ pg_analyze_and_rewrite_params(RawStmt *parsetree, * Note: query must just have come from the parser, because we do not do * AcquireRewriteLocks() on it. */ -static List * +List * pg_rewrite_query(Query *query) { List *querytree_list; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 0b5314e49b3..0a4fa93d016 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -172,6 +172,10 @@ typedef struct List *outer_tlist; /* referent for OUTER_VAR Vars */ List *inner_tlist; /* referent for INNER_VAR Vars */ List *index_tlist; /* referent for INDEX_VAR Vars */ + /* Special namespace representing a function signature: */ + char *funcname; + int numargs; + char **argnames; } deparse_namespace; /* @@ -348,6 +352,7 @@ static int print_function_arguments(StringInfo buf, HeapTuple proctup, bool print_table_args, bool print_defaults); static void print_function_rettype(StringInfo buf, HeapTuple proctup); static void print_function_trftypes(StringInfo buf, HeapTuple proctup); +static void print_function_sqlbody(StringInfo buf, HeapTuple proctup); static void set_rtable_names(deparse_namespace *dpns, List *parent_namespaces, Bitmapset *rels_used); static void set_deparse_for_query(deparse_namespace *dpns, Query *query, @@ -2968,6 +2973,13 @@ pg_get_functiondef(PG_FUNCTION_ARGS) } /* And finally the function definition ... */ + tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosqlbody, &isnull); + if (proc->prolang == SQLlanguageId && !isnull) + { + print_function_sqlbody(&buf, proctup); + } + else + { appendStringInfoString(&buf, "AS "); tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_probin, &isnull); @@ -2999,6 +3011,7 @@ pg_get_functiondef(PG_FUNCTION_ARGS) appendBinaryStringInfo(&buf, dq.data, dq.len); appendStringInfoString(&buf, prosrc); appendBinaryStringInfo(&buf, dq.data, dq.len); + } appendStringInfoChar(&buf, '\n'); @@ -3382,6 +3395,83 @@ pg_get_function_arg_default(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(string_to_text(str)); } +static void +print_function_sqlbody(StringInfo buf, HeapTuple proctup) +{ + int numargs; + Oid *argtypes; + char **argnames; + char *argmodes; + deparse_namespace dpns = {0}; + Datum tmp; + bool isnull; + Node *n; + + dpns.funcname = pstrdup(NameStr(((Form_pg_proc) GETSTRUCT(proctup))->proname)); + numargs = get_func_arg_info(proctup, + &argtypes, &argnames, &argmodes); + dpns.numargs = numargs; + dpns.argnames = argnames; + + tmp = SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosqlbody, &isnull); + Assert(!isnull); + n = stringToNode(TextDatumGetCString(tmp)); + + if (IsA(n, List)) + { + List *stmts; + ListCell *lc; + + stmts = linitial(castNode(List, n)); + + appendStringInfoString(buf, "BEGIN ATOMIC\n"); + + foreach(lc, stmts) + { + Query *query = lfirst_node(Query, lc); + + get_query_def(query, buf, list_make1(&dpns), NULL, PRETTYFLAG_INDENT, WRAP_COLUMN_DEFAULT, 1); + appendStringInfoChar(buf, ';'); + appendStringInfoChar(buf, '\n'); + } + + appendStringInfoString(buf, "END"); + } + else + { + get_query_def(castNode(Query, n), buf, list_make1(&dpns), NULL, 0, WRAP_COLUMN_DEFAULT, 0); + } +} + +Datum +pg_get_function_sqlbody(PG_FUNCTION_ARGS) +{ + Oid funcid = PG_GETARG_OID(0); + StringInfoData buf; + HeapTuple proctup; + bool isnull; + + initStringInfo(&buf); + + /* Look up the function */ + proctup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid)); + if (!HeapTupleIsValid(proctup)) + PG_RETURN_NULL(); + + SysCacheGetAttr(PROCOID, proctup, Anum_pg_proc_prosqlbody, &isnull); + if (isnull) + { + ReleaseSysCache(proctup); + PG_RETURN_NULL(); + } + + print_function_sqlbody(&buf, proctup); + + ReleaseSysCache(proctup); + + PG_RETURN_TEXT_P(cstring_to_text(buf.data)); +} + /* * deparse_expression - General utility for deparsing expressions @@ -5637,7 +5727,10 @@ get_basic_select_query(Query *query, deparse_context *context, /* * Build up the query string - first we say SELECT */ - appendStringInfoString(buf, "SELECT"); + if (query->isReturn) + appendStringInfoString(buf, "RETURN"); + else + appendStringInfoString(buf, "SELECT"); /* Add the DISTINCT clause if given */ if (query->distinctClause != NIL) @@ -7772,6 +7865,50 @@ get_parameter(Param *param, deparse_context *context) } /* + * If it's an external parameter, see if the outermost namespace provides + * function argument names. + */ + if (param->paramkind == PARAM_EXTERN) + { + dpns = lfirst(list_tail(context->namespaces)); + if (dpns->argnames) + { + char *argname = dpns->argnames[param->paramid - 1]; + + if (argname) + { + bool should_qualify = false; + ListCell *lc; + + /* + * Qualify the parameter name if there are any other deparse + * namespaces with range tables. This avoids qualifying in + * trivial cases like "RETURN a + b", but makes it safe in all + * other cases. + */ + foreach(lc, context->namespaces) + { + deparse_namespace *dpns = lfirst(lc); + + if (list_length(dpns->rtable_names) > 0) + { + should_qualify = true; + break; + } + } + if (should_qualify) + { + appendStringInfoString(context->buf, quote_identifier(dpns->funcname)); + appendStringInfoChar(context->buf, '.'); + } + + appendStringInfoString(context->buf, quote_identifier(argname)); + return; + } + } + } + + /* * Not PARAM_EXEC, or couldn't find referent: just print $N. */ appendStringInfo(context->buf, "$%d", param->paramid); diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 25717ce0e68..d0ea4896142 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -12128,6 +12128,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) char *proretset; char *prosrc; char *probin; + char *prosqlbody; char *funcargs; char *funciargs; char *funcresult; @@ -12174,7 +12175,7 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) "provolatile,\n" "proisstrict,\n" "prosecdef,\n" - "(SELECT lanname FROM pg_catalog.pg_language WHERE oid = prolang) AS lanname,\n"); + "lanname,\n"); if (fout->remoteVersion >= 80300) appendPQExpBufferStr(query, @@ -12194,9 +12195,9 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) * pg_get_function_result instead of examining proallargtypes etc. */ appendPQExpBufferStr(query, - "pg_catalog.pg_get_function_arguments(oid) AS funcargs,\n" - "pg_catalog.pg_get_function_identity_arguments(oid) AS funciargs,\n" - "pg_catalog.pg_get_function_result(oid) AS funcresult,\n"); + "pg_catalog.pg_get_function_arguments(p.oid) AS funcargs,\n" + "pg_catalog.pg_get_function_identity_arguments(p.oid) AS funciargs,\n" + "pg_catalog.pg_get_function_result(p.oid) AS funcresult,\n"); } else if (fout->remoteVersion >= 80100) appendPQExpBufferStr(query, @@ -12239,21 +12240,39 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) if (fout->remoteVersion >= 120000) appendPQExpBufferStr(query, - "prosupport\n"); + "prosupport,\n"); else appendPQExpBufferStr(query, - "'-' AS prosupport\n"); + "'-' AS prosupport,\n"); + + if (fout->remoteVersion >= 140000) + appendPQExpBufferStr(query, + "CASE WHEN prosrc IS NULL AND lanname = 'sql' THEN pg_get_function_sqlbody(p.oid) END AS prosqlbody\n"); + else + appendPQExpBufferStr(query, + "NULL AS prosqlbody\n"); appendPQExpBuffer(query, - "FROM pg_catalog.pg_proc " - "WHERE oid = '%u'::pg_catalog.oid", + "FROM pg_catalog.pg_proc p, pg_catalog.pg_language l\n" + "WHERE p.oid = '%u'::pg_catalog.oid " + "AND l.oid = p.prolang", finfo->dobj.catId.oid); res = ExecuteSqlQueryForSingleRow(fout, query->data); proretset = PQgetvalue(res, 0, PQfnumber(res, "proretset")); - prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc")); - probin = PQgetvalue(res, 0, PQfnumber(res, "probin")); + if (PQgetisnull(res, 0, PQfnumber(res, "prosqlbody"))) + { + prosrc = PQgetvalue(res, 0, PQfnumber(res, "prosrc")); + probin = PQgetvalue(res, 0, PQfnumber(res, "probin")); + prosqlbody = NULL; + } + else + { + prosrc = NULL; + probin = NULL; + prosqlbody = PQgetvalue(res, 0, PQfnumber(res, "prosqlbody")); + } if (fout->remoteVersion >= 80400) { funcargs = PQgetvalue(res, 0, PQfnumber(res, "funcargs")); @@ -12290,7 +12309,11 @@ dumpFunc(Archive *fout, const FuncInfo *finfo) * versions would set it to "-". There are no known cases in which prosrc * is unused, so the tests below for "-" are probably useless. */ - if (probin[0] != '\0' && strcmp(probin, "-") != 0) + if (prosqlbody) + { + appendPQExpBufferStr(asPart, prosqlbody); + } + else if (probin[0] != '\0' && strcmp(probin, "-") != 0) { appendPQExpBufferStr(asPart, "AS "); appendStringLiteralAH(asPart, probin, fout); diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 440249ff69d..52f7b2ce78c 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -505,11 +505,18 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool appendPQExpBufferStr(&buf, ",\n "); printACLColumn(&buf, "p.proacl"); appendPQExpBuffer(&buf, - ",\n l.lanname as \"%s\"" - ",\n p.prosrc as \"%s\"" + ",\n l.lanname as \"%s\"", + gettext_noop("Language")); + if (pset.sversion >= 140000) + appendPQExpBuffer(&buf, + ",\n COALESCE(p.prosrc, pg_catalog.pg_get_function_sqlbody(p.oid)) as \"%s\"", + gettext_noop("Source code")); + else + appendPQExpBuffer(&buf, + ",\n p.prosrc as \"%s\"", + gettext_noop("Source code")); + appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(p.oid, 'pg_proc') as \"%s\"", - gettext_noop("Language"), - gettext_noop("Source code"), gettext_noop("Description")); } diff --git a/src/fe_utils/psqlscan.l b/src/fe_utils/psqlscan.l index 216ff30993f..4ec57e96a9d 100644 --- a/src/fe_utils/psqlscan.l +++ b/src/fe_utils/psqlscan.l @@ -645,10 +645,11 @@ other . ";" { ECHO; - if (cur_state->paren_depth == 0) + if (cur_state->paren_depth == 0 && cur_state->begin_depth == 0) { /* Terminate lexing temporarily */ cur_state->start_state = YY_START; + cur_state->identifier_count = 0; return LEXRES_SEMI; } } @@ -661,6 +662,8 @@ other . "\\"[;:] { /* Force a semi-colon or colon into the query buffer */ psqlscan_emit(cur_state, yytext + 1, 1); + if (yytext[1] == ';') + cur_state->identifier_count = 0; } "\\" { @@ -867,6 +870,18 @@ other . {identifier} { + cur_state->identifier_count++; + if (pg_strcasecmp(yytext, "begin") == 0 + || pg_strcasecmp(yytext, "case") == 0) + { + if (cur_state->identifier_count > 1) + cur_state->begin_depth++; + } + else if (pg_strcasecmp(yytext, "end") == 0) + { + if (cur_state->begin_depth > 0) + cur_state->begin_depth--; + } ECHO; } @@ -1054,6 +1069,11 @@ psql_scan(PsqlScanState state, result = PSCAN_INCOMPLETE; *prompt = PROMPT_PAREN; } + if (state->begin_depth > 0) + { + result = PSCAN_INCOMPLETE; + *prompt = PROMPT_CONTINUE; + } else if (query_buf->len > 0) { result = PSCAN_EOL; @@ -1170,6 +1190,8 @@ psql_scan_reset(PsqlScanState state) if (state->dolqstart) free(state->dolqstart); state->dolqstart = NULL; + state->identifier_count = 0; + state->begin_depth = 0; } /* diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 53fd6c08b33..abd4bffff5f 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 202104071 +#define CATALOG_VERSION_NO 202104072 #endif diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 97d8238a997..6feaaa44597 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -3718,6 +3718,10 @@ proname => 'pg_get_function_arg_default', provolatile => 's', prorettype => 'text', proargtypes => 'oid int4', prosrc => 'pg_get_function_arg_default' }, +{ oid => '9704', descr => 'function SQL body', + proname => 'pg_get_function_sqlbody', provolatile => 's', + prorettype => 'text', proargtypes => 'oid', + prosrc => 'pg_get_function_sqlbody' }, { oid => '1686', descr => 'list of SQL keywords', proname => 'pg_get_keywords', procost => '10', prorows => '500', diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 78f230894bd..8d58067d034 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -112,11 +112,14 @@ CATALOG(pg_proc,1255,ProcedureRelationId) BKI_BOOTSTRAP BKI_ROWTYPE_OID(81,Proce Oid protrftypes[1] BKI_DEFAULT(_null_) BKI_LOOKUP(pg_type); /* procedure source text */ - text prosrc BKI_FORCE_NOT_NULL; + text prosrc; /* secondary procedure info (can be NULL) */ text probin BKI_DEFAULT(_null_); + /* pre-parsed SQL function body */ + pg_node_tree prosqlbody BKI_DEFAULT(_null_); + /* procedure-local GUC settings */ text proconfig[1] BKI_DEFAULT(_null_); @@ -194,6 +197,7 @@ extern ObjectAddress ProcedureCreate(const char *procedureName, Oid languageValidator, const char *prosrc, const char *probin, + Node *prosqlbody, char prokind, bool security_definer, bool isLeakProof, diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index 339f29f4c85..6bce4d76fe5 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -65,9 +65,11 @@ extern void interpret_function_parameter_list(ParseState *pstate, Oid languageOid, ObjectType objtype, oidvector **parameterTypes, + List **parameterTypes_list, ArrayType **allParameterTypes, ArrayType **parameterModes, ArrayType **parameterNames, + List **inParameterNames_list, List **parameterDefaults, Oid *variadicArgType, Oid *requiredResultType); diff --git a/src/include/executor/functions.h b/src/include/executor/functions.h index c975a93661e..dcb8e18437f 100644 --- a/src/include/executor/functions.h +++ b/src/include/executor/functions.h @@ -20,6 +20,21 @@ /* This struct is known only within executor/functions.c */ typedef struct SQLFunctionParseInfo *SQLFunctionParseInfoPtr; +/* + * Data structure needed by the parser callback hooks to resolve parameter + * references during parsing of a SQL function's body. This is separate from + * SQLFunctionCache since we sometimes do parsing separately from execution. + */ +typedef struct SQLFunctionParseInfo +{ + char *fname; /* function's name */ + int nargs; /* number of input arguments */ + Oid *argtypes; /* resolved types of input arguments */ + char **argnames; /* names of input arguments; NULL if none */ + /* Note that argnames[i] can be NULL, if some args are unnamed */ + Oid collation; /* function's input collation, if known */ +} SQLFunctionParseInfo; + extern Datum fmgr_sql(PG_FUNCTION_ARGS); extern SQLFunctionParseInfoPtr prepare_sql_fn_parse_info(HeapTuple procedureTuple, diff --git a/src/include/fe_utils/psqlscan_int.h b/src/include/fe_utils/psqlscan_int.h index 2bc3cc4ae73..91d7d4d5c6c 100644 --- a/src/include/fe_utils/psqlscan_int.h +++ b/src/include/fe_utils/psqlscan_int.h @@ -114,6 +114,8 @@ typedef struct PsqlScanStateData int paren_depth; /* depth of nesting in parentheses */ int xcdepth; /* depth of nesting in slash-star comments */ char *dolqstart; /* current $foo$ quote start string */ + int identifier_count; /* identifiers since start of statement */ + int begin_depth; /* depth of begin/end routine body blocks */ /* * Callback functions provided by the program making use of the lexer, diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 2051abbbf92..d9e417bcd70 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -321,6 +321,7 @@ typedef enum NodeTag T_DeleteStmt, T_UpdateStmt, T_SelectStmt, + T_ReturnStmt, T_PLAssignStmt, T_AlterTableStmt, T_AlterTableCmd, diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index acc093d6e0f..7a44bccdd3b 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -140,6 +140,8 @@ typedef struct Query bool hasForUpdate; /* FOR [KEY] UPDATE/SHARE was specified */ bool hasRowSecurity; /* rewriter has applied some RLS policy */ + bool isReturn; /* is a RETURN statement */ + List *cteList; /* WITH list (of CommonTableExpr's) */ List *rtable; /* list of range table entries */ @@ -1721,6 +1723,16 @@ typedef struct SetOperationStmt } SetOperationStmt; +/* + * RETURN statement (inside SQL function body) + */ +typedef struct ReturnStmt +{ + NodeTag type; + Node *returnval; +} ReturnStmt; + + /* ---------------------- * PL/pgSQL Assignment Statement * @@ -2924,6 +2936,7 @@ typedef struct CreateFunctionStmt List *parameters; /* a list of FunctionParameter */ TypeName *returnType; /* the return type */ List *options; /* a list of DefElem */ + Node *sql_body; } CreateFunctionStmt; typedef enum FunctionParameterMode diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index d3ecd72e72e..f836acf876a 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -49,6 +49,7 @@ PG_KEYWORD("assertion", ASSERTION, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("assignment", ASSIGNMENT, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("asymmetric", ASYMMETRIC, RESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("at", AT, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("atomic", ATOMIC, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("attach", ATTACH, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("attribute", ATTRIBUTE, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("authorization", AUTHORIZATION, TYPE_FUNC_NAME_KEYWORD, BARE_LABEL) @@ -349,6 +350,7 @@ PG_KEYWORD("replica", REPLICA, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("reset", RESET, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restart", RESTART, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("restrict", RESTRICT, UNRESERVED_KEYWORD, BARE_LABEL) +PG_KEYWORD("return", RETURN, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("returning", RETURNING, RESERVED_KEYWORD, AS_LABEL) PG_KEYWORD("returns", RETURNS, UNRESERVED_KEYWORD, BARE_LABEL) PG_KEYWORD("revoke", REVOKE, UNRESERVED_KEYWORD, BARE_LABEL) diff --git a/src/include/tcop/tcopprot.h b/src/include/tcop/tcopprot.h index 241e7c99614..968345404e5 100644 --- a/src/include/tcop/tcopprot.h +++ b/src/include/tcop/tcopprot.h @@ -44,6 +44,7 @@ typedef enum extern PGDLLIMPORT int log_statement; extern List *pg_parse_query(const char *query_string); +extern List *pg_rewrite_query(Query *query); extern List *pg_analyze_and_rewrite(RawStmt *parsetree, const char *query_string, Oid *paramTypes, int numParams, diff --git a/src/interfaces/ecpg/preproc/ecpg.addons b/src/interfaces/ecpg/preproc/ecpg.addons index 974ce05664f..b6e3412cef5 100644 --- a/src/interfaces/ecpg/preproc/ecpg.addons +++ b/src/interfaces/ecpg/preproc/ecpg.addons @@ -89,6 +89,12 @@ ECPG: stmtTransactionStmt block whenever_action(2); free($1); } +ECPG: toplevel_stmtTransactionStmtLegacy block + { + fprintf(base_yyout, "{ ECPGtrans(__LINE__, %s, \"%s\");", connection ? connection : "NULL", $1); + whenever_action(2); + free($1); + } ECPG: stmtViewStmt rule | ECPGAllocateDescr { diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer index d699e0abbfc..3600d7c6054 100644 --- a/src/interfaces/ecpg/preproc/ecpg.trailer +++ b/src/interfaces/ecpg/preproc/ecpg.trailer @@ -4,8 +4,8 @@ statements: /*EMPTY*/ | statements statement ; -statement: ecpgstart at stmt ';' { connection = NULL; } - | ecpgstart stmt ';' +statement: ecpgstart at toplevel_stmt ';' { connection = NULL; } + | ecpgstart toplevel_stmt ';' | ecpgstart ECPGVarDeclaration { fprintf(base_yyout, "%s", $2); diff --git a/src/test/regress/expected/create_function_3.out b/src/test/regress/expected/create_function_3.out index f25a407fddc..1fbaebcc728 100644 --- a/src/test/regress/expected/create_function_3.out +++ b/src/test/regress/expected/create_function_3.out @@ -255,6 +255,176 @@ SELECT pg_get_functiondef('functest_F_2'::regproc); (1 row) +-- +-- SQL-standard body +-- +CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean + LANGUAGE SQL + RETURN a = 'abcd' AND b > '2001-01-01'; +CREATE FUNCTION functest_S_2(a text[]) RETURNS int + RETURN a[1]::int; +CREATE FUNCTION functest_S_3() RETURNS boolean + RETURN false; +CREATE FUNCTION functest_S_3a() RETURNS boolean + BEGIN ATOMIC + RETURN false; + END; +CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean + LANGUAGE SQL + BEGIN ATOMIC + SELECT a = 'abcd' AND b > '2001-01-01'; + END; +CREATE FUNCTION functest_S_13() RETURNS boolean + BEGIN ATOMIC + SELECT 1; + SELECT false; + END; +-- error: duplicate function body +CREATE FUNCTION functest_S_xxx(x int) RETURNS int + LANGUAGE SQL + AS $$ SELECT x * 2 $$ + RETURN x * 3; +ERROR: duplicate function body specified +-- polymorphic arguments not allowed in this form +CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement + LANGUAGE SQL + RETURN x[1]; +ERROR: SQL function with unquoted function body cannot have polymorphic arguments +-- tricky parsing +CREATE FUNCTION functest_S_15(x int) RETURNS boolean +LANGUAGE SQL +BEGIN ATOMIC + select case when x % 2 = 0 then true else false end; +END; +SELECT functest_S_1('abcd', '2020-01-01'); + functest_s_1 +-------------- + t +(1 row) + +SELECT functest_S_2(ARRAY['1', '2', '3']); + functest_s_2 +-------------- + 1 +(1 row) + +SELECT functest_S_3(); + functest_s_3 +-------------- + f +(1 row) + +SELECT functest_S_10('abcd', '2020-01-01'); + functest_s_10 +--------------- + t +(1 row) + +SELECT functest_S_13(); + functest_s_13 +--------------- + f +(1 row) + +SELECT pg_get_functiondef('functest_S_1'::regproc); + pg_get_functiondef +------------------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_1(a text, b date)+ + RETURNS boolean + + LANGUAGE sql + + RETURN ((a = 'abcd'::text) AND (b > '01-01-2001'::date)) + + +(1 row) + +SELECT pg_get_functiondef('functest_S_2'::regproc); + pg_get_functiondef +------------------------------------------------------------------ + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_2(a text[])+ + RETURNS integer + + LANGUAGE sql + + RETURN ((a)[1])::integer + + +(1 row) + +SELECT pg_get_functiondef('functest_S_3'::regproc); + pg_get_functiondef +---------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3()+ + RETURNS boolean + + LANGUAGE sql + + RETURN false + + +(1 row) + +SELECT pg_get_functiondef('functest_S_3a'::regproc); + pg_get_functiondef +----------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_3a()+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + RETURN false; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_S_10'::regproc); + pg_get_functiondef +------------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_10(a text, b date)+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT ((a = 'abcd'::text) AND (b > '01-01-2001'::date)); + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_S_13'::regproc); + pg_get_functiondef +----------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_13()+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT 1; + + SELECT false AS bool; + + END + + +(1 row) + +SELECT pg_get_functiondef('functest_S_15'::regproc); + pg_get_functiondef +-------------------------------------------------------------------- + CREATE OR REPLACE FUNCTION temp_func_test.functest_s_15(x integer)+ + RETURNS boolean + + LANGUAGE sql + + BEGIN ATOMIC + + SELECT + + CASE + + WHEN ((x % 2) = 0) THEN true + + ELSE false + + END AS "case"; + + END + + +(1 row) + +-- test with views +CREATE TABLE functest3 (a int); +INSERT INTO functest3 VALUES (1), (2); +CREATE VIEW functestv3 AS SELECT * FROM functest3; +CREATE FUNCTION functest_S_14() RETURNS bigint + RETURN (SELECT count(*) FROM functestv3); +SELECT functest_S_14(); + functest_s_14 +--------------- + 2 +(1 row) + +DROP TABLE functest3 CASCADE; +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to view functestv3 +drop cascades to function functest_s_14() -- information_schema tests CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo') RETURNS int @@ -292,6 +462,15 @@ CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1')) RETURNS int LANGUAGE SQL AS 'SELECT x'; +CREATE FUNCTION functest_IS_6() + RETURNS int + LANGUAGE SQL + RETURN nextval('functest1'); +CREATE TABLE functest2 (a int, b int); +CREATE FUNCTION functest_IS_7() + RETURNS int + LANGUAGE SQL + RETURN (SELECT count(a) FROM functest2); SELECT r0.routine_name, r1.routine_name FROM information_schema.routine_routine_usage rru JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name @@ -305,23 +484,29 @@ SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usag routine_name | sequence_name ---------------+--------------- functest_is_5 | functest1 -(1 row) + functest_is_6 | functest1 +(2 rows) --- currently empty SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage; - routine_name | table_name | column_name ---------------+------------+------------- -(0 rows) + routine_name | table_name | column_name +---------------+------------+------------- + functest_is_7 | functest2 | a +(1 row) SELECT routine_name, table_name FROM information_schema.routine_table_usage; - routine_name | table_name ---------------+------------ -(0 rows) + routine_name | table_name +---------------+------------ + functest_is_7 | functest2 +(1 row) DROP FUNCTION functest_IS_4a CASCADE; NOTICE: drop cascades to function functest_is_4b(integer) DROP SEQUENCE functest1 CASCADE; -NOTICE: drop cascades to function functest_is_5(integer) +NOTICE: drop cascades to 2 other objects +DETAIL: drop cascades to function functest_is_5(integer) +drop cascades to function functest_is_6() +DROP TABLE functest2 CASCADE; +NOTICE: drop cascades to function functest_is_7() -- overload CREATE FUNCTION functest_B_2(bigint) RETURNS bool LANGUAGE 'sql' IMMUTABLE AS 'SELECT $1 > 0'; @@ -340,6 +525,49 @@ CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1'; ERROR: cannot change routine kind DETAIL: "functest1" is a function. DROP FUNCTION functest1(a int); +-- inlining of set-returning functions +CREATE FUNCTION functest_sri1() RETURNS SETOF int +LANGUAGE SQL +STABLE +AS ' + VALUES (1), (2), (3); +'; +SELECT * FROM functest_sri1(); + functest_sri1 +--------------- + 1 + 2 + 3 +(3 rows) + +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1(); + QUERY PLAN +------------------------------ + Values Scan on "*VALUES*" + Output: "*VALUES*".column1 +(2 rows) + +CREATE FUNCTION functest_sri2() RETURNS SETOF int +LANGUAGE SQL +STABLE +BEGIN ATOMIC + VALUES (1), (2), (3); +END; +SELECT * FROM functest_sri2(); + functest_sri2 +--------------- + 1 + 2 + 3 +(3 rows) + +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2(); + QUERY PLAN +------------------------------ + Values Scan on "*VALUES*" + Output: "*VALUES*".column1 +(2 rows) + -- Check behavior of VOID-returning SQL functions CREATE FUNCTION voidtest1(a int) RETURNS VOID LANGUAGE SQL AS $$ SELECT a + 1 $$; @@ -398,7 +626,7 @@ SELECT * FROM voidtest5(3); -- Cleanup DROP SCHEMA temp_func_test CASCADE; -NOTICE: drop cascades to 21 other objects +NOTICE: drop cascades to 30 other objects DETAIL: drop cascades to function functest_a_1(text,date) drop cascades to function functest_a_2(text[]) drop cascades to function functest_a_3() @@ -414,7 +642,16 @@ drop cascades to function functest_f_1(integer) drop cascades to function functest_f_2(integer) drop cascades to function functest_f_3(integer) drop cascades to function functest_f_4(integer) +drop cascades to function functest_s_1(text,date) +drop cascades to function functest_s_2(text[]) +drop cascades to function functest_s_3() +drop cascades to function functest_s_3a() +drop cascades to function functest_s_10(text,date) +drop cascades to function functest_s_13() +drop cascades to function functest_s_15(integer) drop cascades to function functest_b_2(bigint) +drop cascades to function functest_sri1() +drop cascades to function functest_sri2() drop cascades to function voidtest1(integer) drop cascades to function voidtest2(integer,integer) drop cascades to function voidtest3(integer) diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out index 3838fa2324d..d45575561e4 100644 --- a/src/test/regress/expected/create_procedure.out +++ b/src/test/regress/expected/create_procedure.out @@ -65,6 +65,48 @@ SELECT * FROM cp_test ORDER BY b COLLATE "C"; 1 | xyzzy (3 rows) +-- SQL-standard body +CREATE PROCEDURE ptest1s(x text) +LANGUAGE SQL +BEGIN ATOMIC + INSERT INTO cp_test VALUES (1, x); +END; +\df ptest1s + List of functions + Schema | Name | Result data type | Argument data types | Type +--------+---------+------------------+---------------------+------ + public | ptest1s | | x text | proc +(1 row) + +SELECT pg_get_functiondef('ptest1s'::regproc); + pg_get_functiondef +---------------------------------------------------- + CREATE OR REPLACE PROCEDURE public.ptest1s(x text)+ + LANGUAGE sql + + BEGIN ATOMIC + + INSERT INTO cp_test (a, b) + + VALUES (1, ptest1s.x); + + END + + +(1 row) + +CALL ptest1s('b'); +SELECT * FROM cp_test ORDER BY b COLLATE "C"; + a | b +---+------- + 1 | 0 + 1 | a + 1 | b + 1 | xyzzy +(4 rows) + +-- utitlity functions currently not supported here +CREATE PROCEDURE ptestx() +LANGUAGE SQL +BEGIN ATOMIC + CREATE TABLE x (a int); +END; +ERROR: CREATE TABLE is not yet supported in unquoted SQL function body CREATE PROCEDURE ptest2() LANGUAGE SQL AS $$ @@ -146,6 +188,28 @@ AS $$ SELECT a = b; $$; CALL ptest7(least('a', 'b'), 'a'); +-- empty body +CREATE PROCEDURE ptest8(x text) +BEGIN ATOMIC +END; +\df ptest8 + List of functions + Schema | Name | Result data type | Argument data types | Type +--------+--------+------------------+---------------------+------ + public | ptest8 | | x text | proc +(1 row) + +SELECT pg_get_functiondef('ptest8'::regproc); + pg_get_functiondef +--------------------------------------------------- + CREATE OR REPLACE PROCEDURE public.ptest8(x text)+ + LANGUAGE sql + + BEGIN ATOMIC + + END + + +(1 row) + +CALL ptest8(''); -- OUT parameters CREATE PROCEDURE ptest9(OUT a int) LANGUAGE SQL @@ -214,6 +278,7 @@ ALTER ROUTINE ptest1a RENAME TO ptest1; DROP ROUTINE cp_testfunc1(int); -- cleanup DROP PROCEDURE ptest1; +DROP PROCEDURE ptest1s; DROP PROCEDURE ptest2; DROP TABLE cp_test; DROP USER regress_cp_user1; diff --git a/src/test/regress/sql/create_function_3.sql b/src/test/regress/sql/create_function_3.sql index 549b34b4b2a..695ee3413f4 100644 --- a/src/test/regress/sql/create_function_3.sql +++ b/src/test/regress/sql/create_function_3.sql @@ -153,6 +153,79 @@ SELECT pg_get_functiondef('functest_C_3'::regproc); SELECT pg_get_functiondef('functest_F_2'::regproc); +-- +-- SQL-standard body +-- +CREATE FUNCTION functest_S_1(a text, b date) RETURNS boolean + LANGUAGE SQL + RETURN a = 'abcd' AND b > '2001-01-01'; +CREATE FUNCTION functest_S_2(a text[]) RETURNS int + RETURN a[1]::int; +CREATE FUNCTION functest_S_3() RETURNS boolean + RETURN false; +CREATE FUNCTION functest_S_3a() RETURNS boolean + BEGIN ATOMIC + RETURN false; + END; + +CREATE FUNCTION functest_S_10(a text, b date) RETURNS boolean + LANGUAGE SQL + BEGIN ATOMIC + SELECT a = 'abcd' AND b > '2001-01-01'; + END; + +CREATE FUNCTION functest_S_13() RETURNS boolean + BEGIN ATOMIC + SELECT 1; + SELECT false; + END; + +-- error: duplicate function body +CREATE FUNCTION functest_S_xxx(x int) RETURNS int + LANGUAGE SQL + AS $$ SELECT x * 2 $$ + RETURN x * 3; + +-- polymorphic arguments not allowed in this form +CREATE FUNCTION functest_S_xx(x anyarray) RETURNS anyelement + LANGUAGE SQL + RETURN x[1]; + +-- tricky parsing +CREATE FUNCTION functest_S_15(x int) RETURNS boolean +LANGUAGE SQL +BEGIN ATOMIC + select case when x % 2 = 0 then true else false end; +END; + +SELECT functest_S_1('abcd', '2020-01-01'); +SELECT functest_S_2(ARRAY['1', '2', '3']); +SELECT functest_S_3(); + +SELECT functest_S_10('abcd', '2020-01-01'); +SELECT functest_S_13(); + +SELECT pg_get_functiondef('functest_S_1'::regproc); +SELECT pg_get_functiondef('functest_S_2'::regproc); +SELECT pg_get_functiondef('functest_S_3'::regproc); +SELECT pg_get_functiondef('functest_S_3a'::regproc); +SELECT pg_get_functiondef('functest_S_10'::regproc); +SELECT pg_get_functiondef('functest_S_13'::regproc); +SELECT pg_get_functiondef('functest_S_15'::regproc); + +-- test with views +CREATE TABLE functest3 (a int); +INSERT INTO functest3 VALUES (1), (2); +CREATE VIEW functestv3 AS SELECT * FROM functest3; + +CREATE FUNCTION functest_S_14() RETURNS bigint + RETURN (SELECT count(*) FROM functestv3); + +SELECT functest_S_14(); + +DROP TABLE functest3 CASCADE; + + -- information_schema tests CREATE FUNCTION functest_IS_1(a int, b int default 1, c text default 'foo') @@ -188,17 +261,29 @@ CREATE FUNCTION functest_IS_5(x int DEFAULT nextval('functest1')) LANGUAGE SQL AS 'SELECT x'; +CREATE FUNCTION functest_IS_6() + RETURNS int + LANGUAGE SQL + RETURN nextval('functest1'); + +CREATE TABLE functest2 (a int, b int); + +CREATE FUNCTION functest_IS_7() + RETURNS int + LANGUAGE SQL + RETURN (SELECT count(a) FROM functest2); + SELECT r0.routine_name, r1.routine_name FROM information_schema.routine_routine_usage rru JOIN information_schema.routines r0 ON r0.specific_name = rru.specific_name JOIN information_schema.routines r1 ON r1.specific_name = rru.routine_name; SELECT routine_name, sequence_name FROM information_schema.routine_sequence_usage; --- currently empty SELECT routine_name, table_name, column_name FROM information_schema.routine_column_usage; SELECT routine_name, table_name FROM information_schema.routine_table_usage; DROP FUNCTION functest_IS_4a CASCADE; DROP SEQUENCE functest1 CASCADE; +DROP TABLE functest2 CASCADE; -- overload @@ -218,6 +303,29 @@ CREATE OR REPLACE PROCEDURE functest1(a int) LANGUAGE SQL AS 'SELECT $1'; DROP FUNCTION functest1(a int); +-- inlining of set-returning functions + +CREATE FUNCTION functest_sri1() RETURNS SETOF int +LANGUAGE SQL +STABLE +AS ' + VALUES (1), (2), (3); +'; + +SELECT * FROM functest_sri1(); +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri1(); + +CREATE FUNCTION functest_sri2() RETURNS SETOF int +LANGUAGE SQL +STABLE +BEGIN ATOMIC + VALUES (1), (2), (3); +END; + +SELECT * FROM functest_sri2(); +EXPLAIN (verbose, costs off) SELECT * FROM functest_sri2(); + + -- Check behavior of VOID-returning SQL functions CREATE FUNCTION voidtest1(a int) RETURNS VOID LANGUAGE SQL AS diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql index 2ef1c82ceab..76f781c0b90 100644 --- a/src/test/regress/sql/create_procedure.sql +++ b/src/test/regress/sql/create_procedure.sql @@ -28,6 +28,28 @@ CALL ptest1(substring(random()::numeric(20,15)::text, 1, 1)); -- ok, volatile a SELECT * FROM cp_test ORDER BY b COLLATE "C"; +-- SQL-standard body +CREATE PROCEDURE ptest1s(x text) +LANGUAGE SQL +BEGIN ATOMIC + INSERT INTO cp_test VALUES (1, x); +END; + +\df ptest1s +SELECT pg_get_functiondef('ptest1s'::regproc); + +CALL ptest1s('b'); + +SELECT * FROM cp_test ORDER BY b COLLATE "C"; + +-- utitlity functions currently not supported here +CREATE PROCEDURE ptestx() +LANGUAGE SQL +BEGIN ATOMIC + CREATE TABLE x (a int); +END; + + CREATE PROCEDURE ptest2() LANGUAGE SQL AS $$ @@ -112,6 +134,16 @@ $$; CALL ptest7(least('a', 'b'), 'a'); +-- empty body +CREATE PROCEDURE ptest8(x text) +BEGIN ATOMIC +END; + +\df ptest8 +SELECT pg_get_functiondef('ptest8'::regproc); +CALL ptest8(''); + + -- OUT parameters CREATE PROCEDURE ptest9(OUT a int) @@ -170,6 +202,7 @@ DROP ROUTINE cp_testfunc1(int); -- cleanup DROP PROCEDURE ptest1; +DROP PROCEDURE ptest1s; DROP PROCEDURE ptest2; DROP TABLE cp_test; |