diff options
Diffstat (limited to 'src')
66 files changed, 1729 insertions, 257 deletions
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index ccde66a7dd7..e481cf3d118 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -482,6 +482,14 @@ ExecuteGrantStmt(GrantStmt *stmt) all_privileges = ACL_ALL_RIGHTS_NAMESPACE; errormsg = gettext_noop("invalid privilege type %s for schema"); break; + case ACL_OBJECT_PROCEDURE: + all_privileges = ACL_ALL_RIGHTS_FUNCTION; + errormsg = gettext_noop("invalid privilege type %s for procedure"); + break; + case ACL_OBJECT_ROUTINE: + all_privileges = ACL_ALL_RIGHTS_FUNCTION; + errormsg = gettext_noop("invalid privilege type %s for routine"); + break; case ACL_OBJECT_TABLESPACE: all_privileges = ACL_ALL_RIGHTS_TABLESPACE; errormsg = gettext_noop("invalid privilege type %s for tablespace"); @@ -584,6 +592,8 @@ ExecGrantStmt_oids(InternalGrant *istmt) ExecGrant_ForeignServer(istmt); break; case ACL_OBJECT_FUNCTION: + case ACL_OBJECT_PROCEDURE: + case ACL_OBJECT_ROUTINE: ExecGrant_Function(istmt); break; case ACL_OBJECT_LANGUAGE: @@ -671,7 +681,7 @@ objectNamesToOids(GrantObjectType objtype, List *objnames) ObjectWithArgs *func = (ObjectWithArgs *) lfirst(cell); Oid funcid; - funcid = LookupFuncWithArgs(func, false); + funcid = LookupFuncWithArgs(OBJECT_FUNCTION, func, false); objects = lappend_oid(objects, funcid); } break; @@ -709,6 +719,26 @@ objectNamesToOids(GrantObjectType objtype, List *objnames) objects = lappend_oid(objects, oid); } break; + case ACL_OBJECT_PROCEDURE: + foreach(cell, objnames) + { + ObjectWithArgs *func = (ObjectWithArgs *) lfirst(cell); + Oid procid; + + procid = LookupFuncWithArgs(OBJECT_PROCEDURE, func, false); + objects = lappend_oid(objects, procid); + } + break; + case ACL_OBJECT_ROUTINE: + foreach(cell, objnames) + { + ObjectWithArgs *func = (ObjectWithArgs *) lfirst(cell); + Oid routid; + + routid = LookupFuncWithArgs(OBJECT_ROUTINE, func, false); + objects = lappend_oid(objects, routid); + } + break; case ACL_OBJECT_TABLESPACE: foreach(cell, objnames) { @@ -785,19 +815,39 @@ objectsInSchemaToOids(GrantObjectType objtype, List *nspnames) objects = list_concat(objects, objs); break; case ACL_OBJECT_FUNCTION: + case ACL_OBJECT_PROCEDURE: + case ACL_OBJECT_ROUTINE: { - ScanKeyData key[1]; + ScanKeyData key[2]; + int keycount; Relation rel; HeapScanDesc scan; HeapTuple tuple; - ScanKeyInit(&key[0], + keycount = 0; + ScanKeyInit(&key[keycount++], Anum_pg_proc_pronamespace, BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(namespaceId)); + /* + * When looking for functions, check for return type <>0. + * When looking for procedures, check for return type ==0. + * When looking for routines, don't check the return type. + */ + if (objtype == ACL_OBJECT_FUNCTION) + ScanKeyInit(&key[keycount++], + Anum_pg_proc_prorettype, + BTEqualStrategyNumber, F_OIDNE, + InvalidOid); + else if (objtype == ACL_OBJECT_PROCEDURE) + ScanKeyInit(&key[keycount++], + Anum_pg_proc_prorettype, + BTEqualStrategyNumber, F_OIDEQ, + InvalidOid); + rel = heap_open(ProcedureRelationId, AccessShareLock); - scan = heap_beginscan_catalog(rel, 1, key); + scan = heap_beginscan_catalog(rel, keycount, key); while ((tuple = heap_getnext(scan, ForwardScanDirection)) != NULL) { @@ -955,6 +1005,14 @@ ExecAlterDefaultPrivilegesStmt(ParseState *pstate, AlterDefaultPrivilegesStmt *s all_privileges = ACL_ALL_RIGHTS_FUNCTION; errormsg = gettext_noop("invalid privilege type %s for function"); break; + case ACL_OBJECT_PROCEDURE: + all_privileges = ACL_ALL_RIGHTS_FUNCTION; + errormsg = gettext_noop("invalid privilege type %s for procedure"); + break; + case ACL_OBJECT_ROUTINE: + all_privileges = ACL_ALL_RIGHTS_FUNCTION; + errormsg = gettext_noop("invalid privilege type %s for routine"); + break; case ACL_OBJECT_TYPE: all_privileges = ACL_ALL_RIGHTS_TYPE; errormsg = gettext_noop("invalid privilege type %s for type"); @@ -1423,7 +1481,7 @@ RemoveRoleFromObjectACL(Oid roleid, Oid classid, Oid objid) istmt.objtype = ACL_OBJECT_TYPE; break; case ProcedureRelationId: - istmt.objtype = ACL_OBJECT_FUNCTION; + istmt.objtype = ACL_OBJECT_ROUTINE; break; case LanguageRelationId: istmt.objtype = ACL_OBJECT_LANGUAGE; diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 236f6be37e8..360725d59a3 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1413,7 +1413,8 @@ CREATE VIEW routines AS CAST(current_database() AS sql_identifier) AS routine_catalog, CAST(n.nspname AS sql_identifier) AS routine_schema, CAST(p.proname AS sql_identifier) AS routine_name, - CAST('FUNCTION' AS character_data) AS routine_type, + CAST(CASE WHEN p.prorettype <> 0 THEN 'FUNCTION' ELSE 'PROCEDURE' END + AS character_data) AS routine_type, CAST(null AS sql_identifier) AS module_catalog, CAST(null AS sql_identifier) AS module_schema, CAST(null AS sql_identifier) AS module_name, @@ -1422,7 +1423,8 @@ CREATE VIEW routines AS CAST(null AS sql_identifier) AS udt_name, CAST( - CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' + CASE WHEN p.prorettype = 0 THEN NULL + WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null) ELSE 'USER-DEFINED' END AS character_data) AS data_type, @@ -1440,7 +1442,7 @@ CREATE VIEW routines AS CAST(null AS cardinal_number) AS datetime_precision, CAST(null AS character_data) AS interval_type, CAST(null AS cardinal_number) AS interval_precision, - CAST(current_database() AS sql_identifier) AS type_udt_catalog, + CAST(CASE WHEN p.prorettype <> 0 THEN current_database() END AS sql_identifier) AS type_udt_catalog, CAST(nt.nspname AS sql_identifier) AS type_udt_schema, CAST(t.typname AS sql_identifier) AS type_udt_name, CAST(null AS sql_identifier) AS scope_catalog, @@ -1462,7 +1464,8 @@ CREATE VIEW routines AS CAST('GENERAL' AS character_data) AS parameter_style, CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic, CAST('MODIFIES' AS character_data) AS sql_data_access, - CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call, + CAST(CASE WHEN p.prorettype <> 0 THEN + CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END END AS yes_or_no) AS is_null_call, CAST(null AS character_data) AS sql_path, CAST('YES' AS yes_or_no) AS schema_level_routine, CAST(0 AS cardinal_number) AS max_dynamic_result_sets, @@ -1503,13 +1506,15 @@ CREATE VIEW routines AS CAST(null AS cardinal_number) AS result_cast_maximum_cardinality, CAST(null AS sql_identifier) AS result_cast_dtd_identifier - FROM pg_namespace n, pg_proc p, pg_language l, - pg_type t, pg_namespace nt + FROM (pg_namespace n + JOIN pg_proc p ON n.oid = p.pronamespace + JOIN pg_language l ON p.prolang = l.oid) + LEFT JOIN + (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) + ON p.prorettype = t.oid - WHERE n.oid = p.pronamespace AND p.prolang = l.oid - AND p.prorettype = t.oid AND t.typnamespace = nt.oid - AND (pg_has_role(p.proowner, 'USAGE') - OR has_function_privilege(p.oid, 'EXECUTE')); + WHERE (pg_has_role(p.proowner, 'USAGE') + OR has_function_privilege(p.oid, 'EXECUTE')); GRANT SELECT ON routines TO PUBLIC; diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c index 8d55c76fc4a..9553675975d 100644 --- a/src/backend/catalog/objectaddress.c +++ b/src/backend/catalog/objectaddress.c @@ -566,6 +566,9 @@ static const struct object_type_map { "function", OBJECT_FUNCTION }, + { + "procedure", OBJECT_PROCEDURE + }, /* OCLASS_TYPE */ { "type", OBJECT_TYPE @@ -884,13 +887,11 @@ get_object_address(ObjectType objtype, Node *object, address = get_object_address_type(objtype, castNode(TypeName, object), missing_ok); break; case OBJECT_AGGREGATE: - address.classId = ProcedureRelationId; - address.objectId = LookupAggWithArgs(castNode(ObjectWithArgs, object), missing_ok); - address.objectSubId = 0; - break; case OBJECT_FUNCTION: + case OBJECT_PROCEDURE: + case OBJECT_ROUTINE: address.classId = ProcedureRelationId; - address.objectId = LookupFuncWithArgs(castNode(ObjectWithArgs, object), missing_ok); + address.objectId = LookupFuncWithArgs(objtype, castNode(ObjectWithArgs, object), missing_ok); address.objectSubId = 0; break; case OBJECT_OPERATOR: @@ -2025,6 +2026,8 @@ pg_get_object_address(PG_FUNCTION_ARGS) */ if (type == OBJECT_AGGREGATE || type == OBJECT_FUNCTION || + type == OBJECT_PROCEDURE || + type == OBJECT_ROUTINE || type == OBJECT_OPERATOR || type == OBJECT_CAST || type == OBJECT_AMOP || @@ -2168,6 +2171,8 @@ pg_get_object_address(PG_FUNCTION_ARGS) objnode = (Node *) list_make2(name, args); break; case OBJECT_FUNCTION: + case OBJECT_PROCEDURE: + case OBJECT_ROUTINE: case OBJECT_AGGREGATE: case OBJECT_OPERATOR: { @@ -2253,6 +2258,8 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address, break; case OBJECT_AGGREGATE: case OBJECT_FUNCTION: + case OBJECT_PROCEDURE: + case OBJECT_ROUTINE: if (!pg_proc_ownercheck(address.objectId, roleid)) aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC, NameListToString((castNode(ObjectWithArgs, object))->objname)); @@ -4026,6 +4033,8 @@ getProcedureTypeDescription(StringInfo buffer, Oid procid) if (procForm->proisagg) appendStringInfoString(buffer, "aggregate"); + else if (procForm->prorettype == InvalidOid) + appendStringInfoString(buffer, "procedure"); else appendStringInfoString(buffer, "function"); diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c index 47916cfb544..7d05e4bdb22 100644 --- a/src/backend/catalog/pg_proc.c +++ b/src/backend/catalog/pg_proc.c @@ -857,7 +857,8 @@ fmgr_sql_validator(PG_FUNCTION_ARGS) /* Disallow pseudotype result */ /* except for RECORD, VOID, or polymorphic */ - if (get_typtype(proc->prorettype) == TYPTYPE_PSEUDO && + if (proc->prorettype && + get_typtype(proc->prorettype) == TYPTYPE_PSEUDO && proc->prorettype != RECORDOID && proc->prorettype != VOIDOID && !IsPolymorphicType(proc->prorettype)) diff --git a/src/backend/commands/aggregatecmds.c b/src/backend/commands/aggregatecmds.c index adc9877e79e..2e2ee883e26 100644 --- a/src/backend/commands/aggregatecmds.c +++ b/src/backend/commands/aggregatecmds.c @@ -307,7 +307,7 @@ DefineAggregate(ParseState *pstate, List *name, List *args, bool oldstyle, List interpret_function_parameter_list(pstate, args, InvalidOid, - true, /* is an aggregate */ + OBJECT_AGGREGATE, ¶meterTypes, &allParameterTypes, ¶meterModes, diff --git a/src/backend/commands/alter.c b/src/backend/commands/alter.c index 4f8147907c4..21e3f1efe10 100644 --- a/src/backend/commands/alter.c +++ b/src/backend/commands/alter.c @@ -378,6 +378,8 @@ ExecRenameStmt(RenameStmt *stmt) case OBJECT_OPCLASS: case OBJECT_OPFAMILY: case OBJECT_LANGUAGE: + case OBJECT_PROCEDURE: + case OBJECT_ROUTINE: case OBJECT_STATISTIC_EXT: case OBJECT_TSCONFIGURATION: case OBJECT_TSDICTIONARY: @@ -495,6 +497,8 @@ ExecAlterObjectSchemaStmt(AlterObjectSchemaStmt *stmt, case OBJECT_OPERATOR: case OBJECT_OPCLASS: case OBJECT_OPFAMILY: + case OBJECT_PROCEDURE: + case OBJECT_ROUTINE: case OBJECT_STATISTIC_EXT: case OBJECT_TSCONFIGURATION: case OBJECT_TSDICTIONARY: @@ -842,6 +846,8 @@ ExecAlterOwnerStmt(AlterOwnerStmt *stmt) case OBJECT_OPERATOR: case OBJECT_OPCLASS: case OBJECT_OPFAMILY: + case OBJECT_PROCEDURE: + case OBJECT_ROUTINE: case OBJECT_STATISTIC_EXT: case OBJECT_TABLESPACE: case OBJECT_TSDICTIONARY: diff --git a/src/backend/commands/dropcmds.c b/src/backend/commands/dropcmds.c index 2b30677d6f9..7e6baa1928d 100644 --- a/src/backend/commands/dropcmds.c +++ b/src/backend/commands/dropcmds.c @@ -26,6 +26,7 @@ #include "nodes/makefuncs.h" #include "parser/parse_type.h" #include "utils/builtins.h" +#include "utils/lsyscache.h" #include "utils/syscache.h" @@ -91,21 +92,12 @@ RemoveObjects(DropStmt *stmt) */ if (stmt->removeType == OBJECT_FUNCTION) { - Oid funcOid = address.objectId; - HeapTuple tup; - - tup = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcOid)); - if (!HeapTupleIsValid(tup)) /* should not happen */ - elog(ERROR, "cache lookup failed for function %u", funcOid); - - if (((Form_pg_proc) GETSTRUCT(tup))->proisagg) + if (get_func_isagg(address.objectId)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("\"%s\" is an aggregate function", NameListToString(castNode(ObjectWithArgs, object)->objname)), errhint("Use DROP AGGREGATE to drop aggregate functions."))); - - ReleaseSysCache(tup); } /* Check permissions. */ @@ -338,6 +330,32 @@ does_not_exist_skipping(ObjectType objtype, Node *object) } break; } + case OBJECT_PROCEDURE: + { + ObjectWithArgs *owa = castNode(ObjectWithArgs, object); + + if (!schema_does_not_exist_skipping(owa->objname, &msg, &name) && + !type_in_list_does_not_exist_skipping(owa->objargs, &msg, &name)) + { + msg = gettext_noop("procedure %s(%s) does not exist, skipping"); + name = NameListToString(owa->objname); + args = TypeNameListToString(owa->objargs); + } + break; + } + case OBJECT_ROUTINE: + { + ObjectWithArgs *owa = castNode(ObjectWithArgs, object); + + if (!schema_does_not_exist_skipping(owa->objname, &msg, &name) && + !type_in_list_does_not_exist_skipping(owa->objargs, &msg, &name)) + { + msg = gettext_noop("routine %s(%s) does not exist, skipping"); + name = NameListToString(owa->objname); + args = TypeNameListToString(owa->objargs); + } + break; + } case OBJECT_AGGREGATE: { ObjectWithArgs *owa = castNode(ObjectWithArgs, object); diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c index fa7d0d015aa..a602c20b416 100644 --- a/src/backend/commands/event_trigger.c +++ b/src/backend/commands/event_trigger.c @@ -106,8 +106,10 @@ static event_trigger_support_data event_trigger_support[] = { {"OPERATOR CLASS", true}, {"OPERATOR FAMILY", true}, {"POLICY", true}, + {"PROCEDURE", true}, {"PUBLICATION", true}, {"ROLE", false}, + {"ROUTINE", true}, {"RULE", true}, {"SCHEMA", true}, {"SEQUENCE", true}, @@ -1103,8 +1105,10 @@ EventTriggerSupportsObjectType(ObjectType obtype) case OBJECT_OPERATOR: case OBJECT_OPFAMILY: case OBJECT_POLICY: + case OBJECT_PROCEDURE: case OBJECT_PUBLICATION: case OBJECT_PUBLICATION_REL: + case OBJECT_ROUTINE: case OBJECT_RULE: case OBJECT_SCHEMA: case OBJECT_SEQUENCE: @@ -1215,6 +1219,8 @@ EventTriggerSupportsGrantObjectType(GrantObjectType objtype) case ACL_OBJECT_LANGUAGE: case ACL_OBJECT_LARGEOBJECT: case ACL_OBJECT_NAMESPACE: + case ACL_OBJECT_PROCEDURE: + case ACL_OBJECT_ROUTINE: case ACL_OBJECT_TYPE: return true; @@ -2243,6 +2249,10 @@ stringify_grantobjtype(GrantObjectType objtype) return "LARGE OBJECT"; case ACL_OBJECT_NAMESPACE: return "SCHEMA"; + case ACL_OBJECT_PROCEDURE: + return "PROCEDURE"; + case ACL_OBJECT_ROUTINE: + return "ROUTINE"; case ACL_OBJECT_TABLESPACE: return "TABLESPACE"; case ACL_OBJECT_TYPE: @@ -2285,6 +2295,10 @@ stringify_adefprivs_objtype(GrantObjectType objtype) return "LARGE OBJECTS"; case ACL_OBJECT_NAMESPACE: return "SCHEMAS"; + case ACL_OBJECT_PROCEDURE: + return "PROCEDURES"; + case ACL_OBJECT_ROUTINE: + return "ROUTINES"; case ACL_OBJECT_TABLESPACE: return "TABLESPACES"; case ACL_OBJECT_TYPE: diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c index 7de844b2cad..2a9c90133d1 100644 --- a/src/backend/commands/functioncmds.c +++ b/src/backend/commands/functioncmds.c @@ -51,6 +51,8 @@ #include "commands/alter.h" #include "commands/defrem.h" #include "commands/proclang.h" +#include "executor/execdesc.h" +#include "executor/executor.h" #include "miscadmin.h" #include "optimizer/var.h" #include "parser/parse_coerce.h" @@ -179,7 +181,7 @@ void interpret_function_parameter_list(ParseState *pstate, List *parameters, Oid languageOid, - bool is_aggregate, + ObjectType objtype, oidvector **parameterTypes, ArrayType **allParameterTypes, ArrayType **parameterModes, @@ -233,7 +235,7 @@ interpret_function_parameter_list(ParseState *pstate, errmsg("SQL function cannot accept shell type %s", TypeNameToString(t)))); /* We don't allow creating aggregates on shell types either */ - else if (is_aggregate) + else if (objtype == OBJECT_AGGREGATE) ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), errmsg("aggregate cannot accept shell type %s", @@ -262,16 +264,28 @@ interpret_function_parameter_list(ParseState *pstate, if (t->setof) { - if (is_aggregate) + if (objtype == OBJECT_AGGREGATE) ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), errmsg("aggregates cannot accept set arguments"))); + else if (objtype == OBJECT_PROCEDURE) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("procedures cannot accept set arguments"))); else ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), errmsg("functions cannot accept set arguments"))); } + if (objtype == OBJECT_PROCEDURE) + { + if (fp->mode == FUNC_PARAM_OUT || fp->mode == FUNC_PARAM_INOUT) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + (errmsg("procedures cannot have OUT parameters")))); + } + /* handle input parameters */ if (fp->mode != FUNC_PARAM_OUT && fp->mode != FUNC_PARAM_TABLE) { @@ -451,6 +465,7 @@ interpret_function_parameter_list(ParseState *pstate, */ static bool compute_common_attribute(ParseState *pstate, + bool is_procedure, DefElem *defel, DefElem **volatility_item, DefElem **strict_item, @@ -463,6 +478,8 @@ compute_common_attribute(ParseState *pstate, { if (strcmp(defel->defname, "volatility") == 0) { + if (is_procedure) + goto procedure_error; if (*volatility_item) goto duplicate_error; @@ -470,6 +487,8 @@ compute_common_attribute(ParseState *pstate, } else if (strcmp(defel->defname, "strict") == 0) { + if (is_procedure) + goto procedure_error; if (*strict_item) goto duplicate_error; @@ -484,6 +503,8 @@ compute_common_attribute(ParseState *pstate, } else if (strcmp(defel->defname, "leakproof") == 0) { + if (is_procedure) + goto procedure_error; if (*leakproof_item) goto duplicate_error; @@ -495,6 +516,8 @@ compute_common_attribute(ParseState *pstate, } else if (strcmp(defel->defname, "cost") == 0) { + if (is_procedure) + goto procedure_error; if (*cost_item) goto duplicate_error; @@ -502,6 +525,8 @@ compute_common_attribute(ParseState *pstate, } else if (strcmp(defel->defname, "rows") == 0) { + if (is_procedure) + goto procedure_error; if (*rows_item) goto duplicate_error; @@ -509,6 +534,8 @@ compute_common_attribute(ParseState *pstate, } else if (strcmp(defel->defname, "parallel") == 0) { + if (is_procedure) + goto procedure_error; if (*parallel_item) goto duplicate_error; @@ -526,6 +553,13 @@ duplicate_error: errmsg("conflicting or redundant options"), parser_errposition(pstate, defel->location))); return false; /* keep compiler quiet */ + +procedure_error: + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in procedure definition"), + parser_errposition(pstate, defel->location))); + return false; } static char @@ -603,6 +637,7 @@ update_proconfig_value(ArrayType *a, List *set_items) */ static void compute_attributes_sql_style(ParseState *pstate, + bool is_procedure, List *options, List **as, char **language, @@ -669,9 +704,15 @@ compute_attributes_sql_style(ParseState *pstate, (errcode(ERRCODE_SYNTAX_ERROR), errmsg("conflicting or redundant options"), parser_errposition(pstate, defel->location))); + if (is_procedure) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in procedure definition"), + parser_errposition(pstate, defel->location))); windowfunc_item = defel; } else if (compute_common_attribute(pstate, + is_procedure, defel, &volatility_item, &strict_item, @@ -762,7 +803,7 @@ compute_attributes_sql_style(ParseState *pstate, *------------ */ static void -compute_attributes_with_style(ParseState *pstate, List *parameters, bool *isStrict_p, char *volatility_p) +compute_attributes_with_style(ParseState *pstate, bool is_procedure, List *parameters, bool *isStrict_p, char *volatility_p) { ListCell *pl; @@ -771,10 +812,22 @@ compute_attributes_with_style(ParseState *pstate, List *parameters, bool *isStri DefElem *param = (DefElem *) lfirst(pl); if (pg_strcasecmp(param->defname, "isstrict") == 0) + { + if (is_procedure) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in procedure definition"), + parser_errposition(pstate, param->location))); *isStrict_p = defGetBoolean(param); + } else if (pg_strcasecmp(param->defname, "iscachable") == 0) { /* obsolete spelling of isImmutable */ + if (is_procedure) + ereport(ERROR, + (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), + errmsg("invalid attribute in procedure definition"), + parser_errposition(pstate, param->location))); if (defGetBoolean(param)) *volatility_p = PROVOLATILE_IMMUTABLE; } @@ -916,6 +969,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) /* override attributes from explicit list */ compute_attributes_sql_style(pstate, + stmt->is_procedure, stmt->options, &as_clause, &language, &transformDefElem, &isWindowFunc, &volatility, @@ -990,7 +1044,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) interpret_function_parameter_list(pstate, stmt->parameters, languageOid, - false, /* not an aggregate */ + stmt->is_procedure ? OBJECT_PROCEDURE : OBJECT_FUNCTION, ¶meterTypes, &allParameterTypes, ¶meterModes, @@ -999,7 +1053,14 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) &variadicArgType, &requiredResultType); - if (stmt->returnType) + if (stmt->is_procedure) + { + Assert(!stmt->returnType); + + prorettype = InvalidOid; + returnsSet = false; + } + else if (stmt->returnType) { /* explicit RETURNS clause */ compute_return_type(stmt->returnType, languageOid, @@ -1045,7 +1106,7 @@ CreateFunction(ParseState *pstate, CreateFunctionStmt *stmt) trftypes = NULL; } - compute_attributes_with_style(pstate, stmt->withClause, &isStrict, &volatility); + compute_attributes_with_style(pstate, stmt->is_procedure, stmt->withClause, &isStrict, &volatility); interpret_AS_clause(languageOid, language, funcname, as_clause, &prosrc_str, &probin_str); @@ -1168,6 +1229,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) HeapTuple tup; Oid funcOid; Form_pg_proc procForm; + bool is_procedure; Relation rel; ListCell *l; DefElem *volatility_item = NULL; @@ -1182,7 +1244,7 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) rel = heap_open(ProcedureRelationId, RowExclusiveLock); - funcOid = LookupFuncWithArgs(stmt->func, false); + funcOid = LookupFuncWithArgs(stmt->objtype, stmt->func, false); tup = SearchSysCacheCopy1(PROCOID, ObjectIdGetDatum(funcOid)); if (!HeapTupleIsValid(tup)) /* should not happen */ @@ -1201,12 +1263,15 @@ AlterFunction(ParseState *pstate, AlterFunctionStmt *stmt) errmsg("\"%s\" is an aggregate function", NameListToString(stmt->func->objname)))); + is_procedure = (procForm->prorettype == InvalidOid); + /* Examine requested actions. */ foreach(l, stmt->actions) { DefElem *defel = (DefElem *) lfirst(l); if (compute_common_attribute(pstate, + is_procedure, defel, &volatility_item, &strict_item, @@ -1472,7 +1537,7 @@ CreateCast(CreateCastStmt *stmt) { Form_pg_proc procstruct; - funcid = LookupFuncWithArgs(stmt->func, false); + funcid = LookupFuncWithArgs(OBJECT_FUNCTION, stmt->func, false); tuple = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid)); if (!HeapTupleIsValid(tuple)) @@ -1853,7 +1918,7 @@ CreateTransform(CreateTransformStmt *stmt) */ if (stmt->fromsql) { - fromsqlfuncid = LookupFuncWithArgs(stmt->fromsql, false); + fromsqlfuncid = LookupFuncWithArgs(OBJECT_FUNCTION, stmt->fromsql, false); if (!pg_proc_ownercheck(fromsqlfuncid, GetUserId())) aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC, NameListToString(stmt->fromsql->objname)); @@ -1879,7 +1944,7 @@ CreateTransform(CreateTransformStmt *stmt) if (stmt->tosql) { - tosqlfuncid = LookupFuncWithArgs(stmt->tosql, false); + tosqlfuncid = LookupFuncWithArgs(OBJECT_FUNCTION, stmt->tosql, false); if (!pg_proc_ownercheck(tosqlfuncid, GetUserId())) aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PROC, NameListToString(stmt->tosql->objname)); @@ -2168,3 +2233,80 @@ ExecuteDoStmt(DoStmt *stmt) /* execute the inline handler */ OidFunctionCall1(laninline, PointerGetDatum(codeblock)); } + +/* + * Execute CALL statement + */ +void +ExecuteCallStmt(ParseState *pstate, CallStmt *stmt) +{ + List *targs; + ListCell *lc; + Node *node; + FuncExpr *fexpr; + int nargs; + int i; + AclResult aclresult; + FmgrInfo flinfo; + FunctionCallInfoData fcinfo; + + targs = NIL; + foreach(lc, stmt->funccall->args) + { + targs = lappend(targs, transformExpr(pstate, + (Node *) lfirst(lc), + EXPR_KIND_CALL)); + } + + node = ParseFuncOrColumn(pstate, + stmt->funccall->funcname, + targs, + pstate->p_last_srf, + stmt->funccall, + true, + stmt->funccall->location); + + fexpr = castNode(FuncExpr, node); + + aclresult = pg_proc_aclcheck(fexpr->funcid, GetUserId(), ACL_EXECUTE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_PROC, get_func_name(fexpr->funcid)); + InvokeFunctionExecuteHook(fexpr->funcid); + + nargs = list_length(fexpr->args); + + /* safety check; see ExecInitFunc() */ + if (nargs > FUNC_MAX_ARGS) + ereport(ERROR, + (errcode(ERRCODE_TOO_MANY_ARGUMENTS), + errmsg_plural("cannot pass more than %d argument to a procedure", + "cannot pass more than %d arguments to a procedure", + FUNC_MAX_ARGS, + FUNC_MAX_ARGS))); + + fmgr_info(fexpr->funcid, &flinfo); + InitFunctionCallInfoData(fcinfo, &flinfo, nargs, fexpr->inputcollid, NULL, NULL); + + i = 0; + foreach (lc, fexpr->args) + { + EState *estate; + ExprState *exprstate; + ExprContext *econtext; + Datum val; + bool isnull; + + estate = CreateExecutorState(); + exprstate = ExecPrepareExpr(lfirst(lc), estate); + econtext = CreateStandaloneExprContext(); + val = ExecEvalExprSwitchContext(exprstate, econtext, &isnull); + FreeExecutorState(estate); + + fcinfo.arg[i] = val; + fcinfo.argnull[i] = isnull; + + i++; + } + + FunctionCallInvoke(&fcinfo); +} diff --git a/src/backend/commands/opclasscmds.c b/src/backend/commands/opclasscmds.c index 1641e68abec..35c7c67bf51 100644 --- a/src/backend/commands/opclasscmds.c +++ b/src/backend/commands/opclasscmds.c @@ -520,7 +520,7 @@ DefineOpClass(CreateOpClassStmt *stmt) errmsg("invalid procedure number %d," " must be between 1 and %d", item->number, maxProcNumber))); - funcOid = LookupFuncWithArgs(item->name, false); + funcOid = LookupFuncWithArgs(OBJECT_FUNCTION, item->name, false); #ifdef NOT_USED /* XXX this is unnecessary given the superuser check above */ /* Caller must own function */ @@ -894,7 +894,7 @@ AlterOpFamilyAdd(AlterOpFamilyStmt *stmt, Oid amoid, Oid opfamilyoid, errmsg("invalid procedure number %d," " must be between 1 and %d", item->number, maxProcNumber))); - funcOid = LookupFuncWithArgs(item->name, false); + funcOid = LookupFuncWithArgs(OBJECT_FUNCTION, item->name, false); #ifdef NOT_USED /* XXX this is unnecessary given the superuser check above */ /* Caller must own function */ diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c index 98eb777421b..3caa3437235 100644 --- a/src/backend/executor/functions.c +++ b/src/backend/executor/functions.c @@ -390,6 +390,7 @@ sql_fn_post_column_ref(ParseState *pstate, ColumnRef *cref, Node *var) list_make1(param), pstate->p_last_srf, NULL, + false, cref->location); } @@ -658,7 +659,8 @@ init_sql_fcache(FmgrInfo *finfo, Oid collation, bool lazyEvalOK) fcache->rettype = rettype; /* Fetch the typlen and byval info for the result type */ - get_typlenbyval(rettype, &fcache->typlen, &fcache->typbyval); + if (rettype) + get_typlenbyval(rettype, &fcache->typlen, &fcache->typbyval); /* Remember whether we're returning setof something */ fcache->returnsSet = procedureStruct->proretset; @@ -1321,8 +1323,8 @@ fmgr_sql(PG_FUNCTION_ARGS) } else { - /* Should only get here for VOID functions */ - Assert(fcache->rettype == VOIDOID); + /* Should only get here for procedures and VOID functions */ + Assert(fcache->rettype == InvalidOid || fcache->rettype == VOIDOID); fcinfo->isnull = true; result = (Datum) 0; } @@ -1546,7 +1548,10 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList, if (modifyTargetList) *modifyTargetList = false; /* initialize for no change */ if (junkFilter) - *junkFilter = NULL; /* initialize in case of VOID result */ + *junkFilter = NULL; /* initialize in case of procedure/VOID result */ + + if (!rettype) + return false; /* * Find the last canSetTag query in the list. This isn't necessarily the @@ -1591,7 +1596,7 @@ check_sql_fn_retval(Oid func_id, Oid rettype, List *queryTreeList, else { /* Empty function body, or last statement is a utility command */ - if (rettype != VOIDOID) + if (rettype && rettype != VOIDOID) ereport(ERROR, (errcode(ERRCODE_INVALID_FUNCTION_DEFINITION), errmsg("return type mismatch in function declared to return %s", diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index d9ff8a7e510..aff9a62106d 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -3210,6 +3210,16 @@ _copyClosePortalStmt(const ClosePortalStmt *from) return newnode; } +static CallStmt * +_copyCallStmt(const CallStmt *from) +{ + CallStmt *newnode = makeNode(CallStmt); + + COPY_NODE_FIELD(funccall); + + return newnode; +} + static ClusterStmt * _copyClusterStmt(const ClusterStmt *from) { @@ -3411,6 +3421,7 @@ _copyCreateFunctionStmt(const CreateFunctionStmt *from) COPY_NODE_FIELD(funcname); COPY_NODE_FIELD(parameters); COPY_NODE_FIELD(returnType); + COPY_SCALAR_FIELD(is_procedure); COPY_NODE_FIELD(options); COPY_NODE_FIELD(withClause); @@ -3435,6 +3446,7 @@ _copyAlterFunctionStmt(const AlterFunctionStmt *from) { AlterFunctionStmt *newnode = makeNode(AlterFunctionStmt); + COPY_SCALAR_FIELD(objtype); COPY_NODE_FIELD(func); COPY_NODE_FIELD(actions); @@ -5104,6 +5116,9 @@ copyObjectImpl(const void *from) case T_ClosePortalStmt: retval = _copyClosePortalStmt(from); break; + case T_CallStmt: + retval = _copyCallStmt(from); + break; case T_ClusterStmt: retval = _copyClusterStmt(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 2866fd7b4af..2e869a9d5d8 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -1202,6 +1202,14 @@ _equalClosePortalStmt(const ClosePortalStmt *a, const ClosePortalStmt *b) } static bool +_equalCallStmt(const CallStmt *a, const CallStmt *b) +{ + COMPARE_NODE_FIELD(funccall); + + return true; +} + +static bool _equalClusterStmt(const ClusterStmt *a, const ClusterStmt *b) { COMPARE_NODE_FIELD(relation); @@ -1364,6 +1372,7 @@ _equalCreateFunctionStmt(const CreateFunctionStmt *a, const CreateFunctionStmt * COMPARE_NODE_FIELD(funcname); COMPARE_NODE_FIELD(parameters); COMPARE_NODE_FIELD(returnType); + COMPARE_SCALAR_FIELD(is_procedure); COMPARE_NODE_FIELD(options); COMPARE_NODE_FIELD(withClause); @@ -1384,6 +1393,7 @@ _equalFunctionParameter(const FunctionParameter *a, const FunctionParameter *b) static bool _equalAlterFunctionStmt(const AlterFunctionStmt *a, const AlterFunctionStmt *b) { + COMPARE_SCALAR_FIELD(objtype); COMPARE_NODE_FIELD(func); COMPARE_NODE_FIELD(actions); @@ -3246,6 +3256,9 @@ equal(const void *a, const void *b) case T_ClosePortalStmt: retval = _equalClosePortalStmt(a, b); break; + case T_CallStmt: + retval = _equalCallStmt(a, b); + break; case T_ClusterStmt: retval = _equalClusterStmt(a, b); break; diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index e5e29565646..6a2d5ad760f 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -4401,6 +4401,7 @@ inline_function(Oid funcid, Oid result_type, Oid result_collid, if (funcform->prolang != SQLlanguageId || funcform->prosecdef || funcform->proretset || + funcform->prorettype == InvalidOid || funcform->prorettype == RECORDOID || !heap_attisnull(func_tuple, Anum_pg_proc_proconfig) || funcform->pronargs != list_length(args)) diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index c301ca465d4..ebfc94f8969 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -253,7 +253,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); AlterCompositeTypeStmt AlterUserMappingStmt AlterRoleStmt AlterRoleSetStmt AlterPolicyStmt AlterDefaultPrivilegesStmt DefACLAction - AnalyzeStmt ClosePortalStmt ClusterStmt CommentStmt + AnalyzeStmt CallStmt ClosePortalStmt ClusterStmt CommentStmt ConstraintsSetStmt CopyStmt CreateAsStmt CreateCastStmt CreateDomainStmt CreateExtensionStmt CreateGroupStmt CreateOpClassStmt CreateOpFamilyStmt AlterOpFamilyStmt CreatePLangStmt @@ -611,7 +611,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); BACKWARD BEFORE BEGIN_P BETWEEN BIGINT BINARY BIT BOOLEAN_P BOTH BY - CACHE CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P + CACHE CALL CALLED CASCADE CASCADED CASE CAST CATALOG_P CHAIN CHAR_P CHARACTER CHARACTERISTICS CHECK CHECKPOINT CLASS CLOSE CLUSTER COALESCE COLLATE COLLATION COLUMN COLUMNS COMMENT COMMENTS COMMIT COMMITTED CONCURRENTLY CONFIGURATION CONFLICT CONNECTION CONSTRAINT @@ -660,14 +660,14 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY - PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROGRAM PUBLICATION + PRIOR PRIVILEGES PROCEDURAL PROCEDURE PROCEDURES PROGRAM PUBLICATION QUOTE 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 - ROW ROWS RULE + ROUTINE ROUTINES ROW ROWS RULE SAVEPOINT SCHEMA SCHEMAS SCROLL SEARCH SECOND_P SECURITY SELECT SEQUENCE SEQUENCES SERIALIZABLE SERVER SESSION SESSION_USER SET SETS SETOF SHARE SHOW @@ -845,6 +845,7 @@ stmt : | AlterTSDictionaryStmt | AlterUserMappingStmt | AnalyzeStmt + | CallStmt | CheckPointStmt | ClosePortalStmt | ClusterStmt @@ -942,6 +943,20 @@ stmt : /***************************************************************************** * + * CALL statement + * + *****************************************************************************/ + +CallStmt: CALL func_application + { + CallStmt *n = makeNode(CallStmt); + n->funccall = castNode(FuncCall, $2); + $$ = (Node *)n; + } + ; + +/***************************************************************************** + * * Create a new Postgres DBMS role * *****************************************************************************/ @@ -4554,6 +4569,24 @@ AlterExtensionContentsStmt: n->object = (Node *) lcons(makeString($9), $7); $$ = (Node *)n; } + | ALTER EXTENSION name add_drop PROCEDURE function_with_argtypes + { + AlterExtensionContentsStmt *n = makeNode(AlterExtensionContentsStmt); + n->extname = $3; + n->action = $4; + n->objtype = OBJECT_PROCEDURE; + n->object = (Node *) $6; + $$ = (Node *)n; + } + | ALTER EXTENSION name add_drop ROUTINE function_with_argtypes + { + AlterExtensionContentsStmt *n = makeNode(AlterExtensionContentsStmt); + n->extname = $3; + n->action = $4; + n->objtype = OBJECT_ROUTINE; + n->object = (Node *) $6; + $$ = (Node *)n; + } | ALTER EXTENSION name add_drop SCHEMA name { AlterExtensionContentsStmt *n = makeNode(AlterExtensionContentsStmt); @@ -6436,6 +6469,22 @@ CommentStmt: n->comment = $8; $$ = (Node *) n; } + | COMMENT ON PROCEDURE function_with_argtypes IS comment_text + { + CommentStmt *n = makeNode(CommentStmt); + n->objtype = OBJECT_PROCEDURE; + n->object = (Node *) $4; + n->comment = $6; + $$ = (Node *) n; + } + | COMMENT ON ROUTINE function_with_argtypes IS comment_text + { + CommentStmt *n = makeNode(CommentStmt); + n->objtype = OBJECT_ROUTINE; + n->object = (Node *) $4; + n->comment = $6; + $$ = (Node *) n; + } | COMMENT ON RULE name ON any_name IS comment_text { CommentStmt *n = makeNode(CommentStmt); @@ -6614,6 +6663,26 @@ SecLabelStmt: n->label = $9; $$ = (Node *) n; } + | SECURITY LABEL opt_provider ON PROCEDURE function_with_argtypes + IS security_label + { + SecLabelStmt *n = makeNode(SecLabelStmt); + n->provider = $3; + n->objtype = OBJECT_PROCEDURE; + n->object = (Node *) $6; + n->label = $8; + $$ = (Node *) n; + } + | SECURITY LABEL opt_provider ON ROUTINE function_with_argtypes + IS security_label + { + SecLabelStmt *n = makeNode(SecLabelStmt); + n->provider = $3; + n->objtype = OBJECT_ROUTINE; + n->object = (Node *) $6; + n->label = $8; + $$ = (Node *) n; + } ; opt_provider: FOR NonReservedWord_or_Sconst { $$ = $2; } @@ -6977,6 +7046,22 @@ privilege_target: n->objs = $2; $$ = n; } + | PROCEDURE function_with_argtypes_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; + n->objtype = ACL_OBJECT_PROCEDURE; + n->objs = $2; + $$ = n; + } + | ROUTINE function_with_argtypes_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_OBJECT; + n->objtype = ACL_OBJECT_ROUTINE; + n->objs = $2; + $$ = n; + } | DATABASE name_list { PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); @@ -7057,6 +7142,22 @@ privilege_target: n->objs = $5; $$ = n; } + | ALL PROCEDURES IN_P SCHEMA name_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_ALL_IN_SCHEMA; + n->objtype = ACL_OBJECT_PROCEDURE; + n->objs = $5; + $$ = n; + } + | ALL ROUTINES IN_P SCHEMA name_list + { + PrivTarget *n = (PrivTarget *) palloc(sizeof(PrivTarget)); + n->targtype = ACL_TARGET_ALL_IN_SCHEMA; + n->objtype = ACL_OBJECT_ROUTINE; + n->objs = $5; + $$ = n; + } ; @@ -7213,6 +7314,7 @@ DefACLAction: defacl_privilege_target: TABLES { $$ = ACL_OBJECT_RELATION; } | FUNCTIONS { $$ = ACL_OBJECT_FUNCTION; } + | ROUTINES { $$ = ACL_OBJECT_FUNCTION; } | SEQUENCES { $$ = ACL_OBJECT_SEQUENCE; } | TYPES_P { $$ = ACL_OBJECT_TYPE; } | SCHEMAS { $$ = ACL_OBJECT_NAMESPACE; } @@ -7413,6 +7515,18 @@ CreateFunctionStmt: n->withClause = $7; $$ = (Node *)n; } + | CREATE opt_or_replace PROCEDURE func_name func_args_with_defaults + createfunc_opt_list + { + CreateFunctionStmt *n = makeNode(CreateFunctionStmt); + n->replace = $2; + n->funcname = $4; + n->parameters = $5; + n->returnType = NULL; + n->is_procedure = true; + n->options = $6; + $$ = (Node *)n; + } ; opt_or_replace: @@ -7830,7 +7944,7 @@ table_func_column_list: ; /***************************************************************************** - * ALTER FUNCTION + * ALTER FUNCTION / ALTER PROCEDURE / ALTER ROUTINE * * RENAME and OWNER subcommands are already provided by the generic * ALTER infrastructure, here we just specify alterations that can @@ -7841,6 +7955,23 @@ AlterFunctionStmt: ALTER FUNCTION function_with_argtypes alterfunc_opt_list opt_restrict { AlterFunctionStmt *n = makeNode(AlterFunctionStmt); + n->objtype = OBJECT_FUNCTION; + n->func = $3; + n->actions = $4; + $$ = (Node *) n; + } + | ALTER PROCEDURE function_with_argtypes alterfunc_opt_list opt_restrict + { + AlterFunctionStmt *n = makeNode(AlterFunctionStmt); + n->objtype = OBJECT_PROCEDURE; + n->func = $3; + n->actions = $4; + $$ = (Node *) n; + } + | ALTER ROUTINE function_with_argtypes alterfunc_opt_list opt_restrict + { + AlterFunctionStmt *n = makeNode(AlterFunctionStmt); + n->objtype = OBJECT_ROUTINE; n->func = $3; n->actions = $4; $$ = (Node *) n; @@ -7865,6 +7996,8 @@ opt_restrict: * QUERY: * * DROP FUNCTION funcname (arg1, arg2, ...) [ RESTRICT | CASCADE ] + * DROP PROCEDURE procname (arg1, arg2, ...) [ RESTRICT | CASCADE ] + * DROP ROUTINE routname (arg1, arg2, ...) [ RESTRICT | CASCADE ] * DROP AGGREGATE aggname (arg1, ...) [ RESTRICT | CASCADE ] * DROP OPERATOR opname (leftoperand_typ, rightoperand_typ) [ RESTRICT | CASCADE ] * @@ -7891,6 +8024,46 @@ RemoveFuncStmt: n->concurrent = false; $$ = (Node *)n; } + | DROP PROCEDURE function_with_argtypes_list opt_drop_behavior + { + DropStmt *n = makeNode(DropStmt); + n->removeType = OBJECT_PROCEDURE; + n->objects = $3; + n->behavior = $4; + n->missing_ok = false; + n->concurrent = false; + $$ = (Node *)n; + } + | DROP PROCEDURE IF_P EXISTS function_with_argtypes_list opt_drop_behavior + { + DropStmt *n = makeNode(DropStmt); + n->removeType = OBJECT_PROCEDURE; + n->objects = $5; + n->behavior = $6; + n->missing_ok = true; + n->concurrent = false; + $$ = (Node *)n; + } + | DROP ROUTINE function_with_argtypes_list opt_drop_behavior + { + DropStmt *n = makeNode(DropStmt); + n->removeType = OBJECT_ROUTINE; + n->objects = $3; + n->behavior = $4; + n->missing_ok = false; + n->concurrent = false; + $$ = (Node *)n; + } + | DROP ROUTINE IF_P EXISTS function_with_argtypes_list opt_drop_behavior + { + DropStmt *n = makeNode(DropStmt); + n->removeType = OBJECT_ROUTINE; + n->objects = $5; + n->behavior = $6; + n->missing_ok = true; + n->concurrent = false; + $$ = (Node *)n; + } ; RemoveAggrStmt: @@ -8348,6 +8521,15 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes RENAME TO name n->missing_ok = true; $$ = (Node *)n; } + | ALTER PROCEDURE function_with_argtypes RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_PROCEDURE; + n->object = (Node *) $3; + n->newname = $6; + n->missing_ok = false; + $$ = (Node *)n; + } | ALTER PUBLICATION name RENAME TO name { RenameStmt *n = makeNode(RenameStmt); @@ -8357,6 +8539,15 @@ RenameStmt: ALTER AGGREGATE aggregate_with_argtypes RENAME TO name n->missing_ok = false; $$ = (Node *)n; } + | ALTER ROUTINE function_with_argtypes RENAME TO name + { + RenameStmt *n = makeNode(RenameStmt); + n->renameType = OBJECT_ROUTINE; + n->object = (Node *) $3; + n->newname = $6; + n->missing_ok = false; + $$ = (Node *)n; + } | ALTER SCHEMA name RENAME TO name { RenameStmt *n = makeNode(RenameStmt); @@ -8736,6 +8927,22 @@ AlterObjectDependsStmt: n->extname = makeString($7); $$ = (Node *)n; } + | ALTER PROCEDURE function_with_argtypes DEPENDS ON EXTENSION name + { + AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); + n->objectType = OBJECT_PROCEDURE; + n->object = (Node *) $3; + n->extname = makeString($7); + $$ = (Node *)n; + } + | ALTER ROUTINE function_with_argtypes DEPENDS ON EXTENSION name + { + AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); + n->objectType = OBJECT_ROUTINE; + n->object = (Node *) $3; + n->extname = makeString($7); + $$ = (Node *)n; + } | ALTER TRIGGER name ON qualified_name DEPENDS ON EXTENSION name { AlterObjectDependsStmt *n = makeNode(AlterObjectDependsStmt); @@ -8851,6 +9058,24 @@ AlterObjectSchemaStmt: n->missing_ok = false; $$ = (Node *)n; } + | ALTER PROCEDURE function_with_argtypes SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_PROCEDURE; + n->object = (Node *) $3; + n->newschema = $6; + n->missing_ok = false; + $$ = (Node *)n; + } + | ALTER ROUTINE function_with_argtypes SET SCHEMA name + { + AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); + n->objectType = OBJECT_ROUTINE; + n->object = (Node *) $3; + n->newschema = $6; + n->missing_ok = false; + $$ = (Node *)n; + } | ALTER TABLE relation_expr SET SCHEMA name { AlterObjectSchemaStmt *n = makeNode(AlterObjectSchemaStmt); @@ -9126,6 +9351,22 @@ AlterOwnerStmt: ALTER AGGREGATE aggregate_with_argtypes OWNER TO RoleSpec n->newowner = $9; $$ = (Node *)n; } + | ALTER PROCEDURE function_with_argtypes OWNER TO RoleSpec + { + AlterOwnerStmt *n = makeNode(AlterOwnerStmt); + n->objectType = OBJECT_PROCEDURE; + n->object = (Node *) $3; + n->newowner = $6; + $$ = (Node *)n; + } + | ALTER ROUTINE function_with_argtypes OWNER TO RoleSpec + { + AlterOwnerStmt *n = makeNode(AlterOwnerStmt); + n->objectType = OBJECT_ROUTINE; + n->object = (Node *) $3; + n->newowner = $6; + $$ = (Node *)n; + } | ALTER SCHEMA name OWNER TO RoleSpec { AlterOwnerStmt *n = makeNode(AlterOwnerStmt); @@ -14689,6 +14930,7 @@ unreserved_keyword: | BEGIN_P | BY | CACHE + | CALL | CALLED | CASCADE | CASCADED @@ -14848,6 +15090,7 @@ unreserved_keyword: | PRIVILEGES | PROCEDURAL | PROCEDURE + | PROCEDURES | PROGRAM | PUBLICATION | QUOTE @@ -14874,6 +15117,8 @@ unreserved_keyword: | ROLE | ROLLBACK | ROLLUP + | ROUTINE + | ROUTINES | ROWS | RULE | SAVEPOINT diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 64111f315e3..4c4f4cdc3d7 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -508,6 +508,14 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) break; + case EXPR_KIND_CALL: + if (isAgg) + err = _("aggregate functions are not allowed in CALL arguments"); + else + err = _("grouping operations are not allowed in CALL arguments"); + + break; + /* * There is intentionally no default: case here, so that the * compiler will warn if we add a new ParseExprKind without @@ -883,6 +891,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_PARTITION_EXPRESSION: err = _("window functions are not allowed in partition key expression"); break; + case EXPR_KIND_CALL: + err = _("window functions are not allowed in CALL arguments"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 86d1da06775..29f9da796fc 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -480,6 +480,7 @@ transformIndirection(ParseState *pstate, A_Indirection *ind) list_make1(result), last_srf, NULL, + false, location); if (newresult == NULL) unknown_attribute(pstate, result, strVal(n), location); @@ -629,6 +630,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) list_make1(node), pstate->p_last_srf, NULL, + false, cref->location); } break; @@ -676,6 +678,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) list_make1(node), pstate->p_last_srf, NULL, + false, cref->location); } break; @@ -736,6 +739,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) list_make1(node), pstate->p_last_srf, NULL, + false, cref->location); } break; @@ -1477,6 +1481,7 @@ transformFuncCall(ParseState *pstate, FuncCall *fn) targs, last_srf, fn, + false, fn->location); } @@ -1812,6 +1817,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_RETURNING: case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: + case EXPR_KIND_CALL: /* okay */ break; case EXPR_KIND_CHECK_CONSTRAINT: @@ -3462,6 +3468,8 @@ ParseExprKindName(ParseExprKind exprKind) return "WHEN"; case EXPR_KIND_PARTITION_EXPRESSION: return "PARTITION BY"; + case EXPR_KIND_CALL: + return "CALL"; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index a11843332b0..2f20516e766 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -71,7 +71,7 @@ static Node *ParseComplexProjection(ParseState *pstate, const char *funcname, */ Node * ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, - Node *last_srf, FuncCall *fn, int location) + Node *last_srf, FuncCall *fn, bool proc_call, int location) { bool is_column = (fn == NULL); List *agg_order = (fn ? fn->agg_order : NIL); @@ -263,7 +263,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, actual_arg_types[0], rettype, -1, COERCION_EXPLICIT, COERCE_EXPLICIT_CALL, location); } - else if (fdresult == FUNCDETAIL_NORMAL) + else if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE) { /* * Normal function found; was there anything indicating it must be an @@ -306,6 +306,26 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, errmsg("OVER specified, but %s is not a window function nor an aggregate function", NameListToString(funcname)), parser_errposition(pstate, location))); + + if (fdresult == FUNCDETAIL_NORMAL && proc_call) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_FUNCTION), + errmsg("%s is not a procedure", + func_signature_string(funcname, nargs, + argnames, + actual_arg_types)), + errhint("To call a function, use SELECT."), + parser_errposition(pstate, location))); + + if (fdresult == FUNCDETAIL_PROCEDURE && !proc_call) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_FUNCTION), + errmsg("%s is a procedure", + func_signature_string(funcname, nargs, + argnames, + actual_arg_types)), + errhint("To call a procedure, use CALL."), + parser_errposition(pstate, location))); } else if (fdresult == FUNCDETAIL_AGGREGATE) { @@ -635,7 +655,7 @@ ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, check_srf_call_placement(pstate, last_srf, location); /* build the appropriate output structure */ - if (fdresult == FUNCDETAIL_NORMAL) + if (fdresult == FUNCDETAIL_NORMAL || fdresult == FUNCDETAIL_PROCEDURE) { FuncExpr *funcexpr = makeNode(FuncExpr); @@ -1589,6 +1609,8 @@ func_get_detail(List *funcname, result = FUNCDETAIL_AGGREGATE; else if (pform->proiswindow) result = FUNCDETAIL_WINDOWFUNC; + else if (pform->prorettype == InvalidOid) + result = FUNCDETAIL_PROCEDURE; else result = FUNCDETAIL_NORMAL; ReleaseSysCache(ftup); @@ -1984,16 +2006,28 @@ LookupFuncName(List *funcname, int nargs, const Oid *argtypes, bool noError) /* * LookupFuncWithArgs - * Like LookupFuncName, but the argument types are specified by a - * ObjectWithArgs node. + * + * Like LookupFuncName, but the argument types are specified by a + * ObjectWithArgs node. Also, this function can check whether the result is a + * function, procedure, or aggregate, based on the objtype argument. Pass + * OBJECT_ROUTINE to accept any of them. + * + * For historical reasons, we also accept aggregates when looking for a + * function. */ Oid -LookupFuncWithArgs(ObjectWithArgs *func, bool noError) +LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs *func, bool noError) { Oid argoids[FUNC_MAX_ARGS]; int argcount; int i; ListCell *args_item; + Oid oid; + + Assert(objtype == OBJECT_AGGREGATE || + objtype == OBJECT_FUNCTION || + objtype == OBJECT_PROCEDURE || + objtype == OBJECT_ROUTINE); argcount = list_length(func->objargs); if (argcount > FUNC_MAX_ARGS) @@ -2013,90 +2047,100 @@ LookupFuncWithArgs(ObjectWithArgs *func, bool noError) args_item = lnext(args_item); } - return LookupFuncName(func->objname, func->args_unspecified ? -1 : argcount, argoids, noError); -} - -/* - * LookupAggWithArgs - * Find an aggregate function from a given ObjectWithArgs node. - * - * This is almost like LookupFuncWithArgs, but the error messages refer - * to aggregates rather than plain functions, and we verify that the found - * function really is an aggregate. - */ -Oid -LookupAggWithArgs(ObjectWithArgs *agg, bool noError) -{ - Oid argoids[FUNC_MAX_ARGS]; - int argcount; - int i; - ListCell *lc; - Oid oid; - HeapTuple ftup; - Form_pg_proc pform; - - argcount = list_length(agg->objargs); - if (argcount > FUNC_MAX_ARGS) - ereport(ERROR, - (errcode(ERRCODE_TOO_MANY_ARGUMENTS), - errmsg_plural("functions cannot have more than %d argument", - "functions cannot have more than %d arguments", - FUNC_MAX_ARGS, - FUNC_MAX_ARGS))); + /* + * When looking for a function or routine, we pass noError through to + * LookupFuncName and let it make any error messages. Otherwise, we make + * our own errors for the aggregate and procedure cases. + */ + oid = LookupFuncName(func->objname, func->args_unspecified ? -1 : argcount, argoids, + (objtype == OBJECT_FUNCTION || objtype == OBJECT_ROUTINE) ? noError : true); - i = 0; - foreach(lc, agg->objargs) + if (objtype == OBJECT_FUNCTION) { - TypeName *t = (TypeName *) lfirst(lc); - - argoids[i] = LookupTypeNameOid(NULL, t, noError); - i++; + /* Make sure it's a function, not a procedure */ + if (oid && get_func_rettype(oid) == InvalidOid) + { + if (noError) + return InvalidOid; + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("%s is not a function", + func_signature_string(func->objname, argcount, + NIL, argoids)))); + } } - - oid = LookupFuncName(agg->objname, argcount, argoids, true); - - if (!OidIsValid(oid)) + else if (objtype == OBJECT_PROCEDURE) { - if (noError) - return InvalidOid; - if (argcount == 0) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_FUNCTION), - errmsg("aggregate %s(*) does not exist", - NameListToString(agg->objname)))); - else + if (!OidIsValid(oid)) + { + if (noError) + return InvalidOid; + else if (func->args_unspecified) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_FUNCTION), + errmsg("could not find a procedure named \"%s\"", + NameListToString(func->objname)))); + else + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_FUNCTION), + errmsg("procedure %s does not exist", + func_signature_string(func->objname, argcount, + NIL, argoids)))); + } + + /* Make sure it's a procedure */ + if (get_func_rettype(oid) != InvalidOid) + { + if (noError) + return InvalidOid; ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_FUNCTION), - errmsg("aggregate %s does not exist", - func_signature_string(agg->objname, argcount, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("%s is not a procedure", + func_signature_string(func->objname, argcount, NIL, argoids)))); + } } - - /* Make sure it's an aggregate */ - ftup = SearchSysCache1(PROCOID, ObjectIdGetDatum(oid)); - if (!HeapTupleIsValid(ftup)) /* should not happen */ - elog(ERROR, "cache lookup failed for function %u", oid); - pform = (Form_pg_proc) GETSTRUCT(ftup); - - if (!pform->proisagg) + else if (objtype == OBJECT_AGGREGATE) { - ReleaseSysCache(ftup); - if (noError) - return InvalidOid; - /* we do not use the (*) notation for functions... */ - ereport(ERROR, - (errcode(ERRCODE_WRONG_OBJECT_TYPE), - errmsg("function %s is not an aggregate", - func_signature_string(agg->objname, argcount, - NIL, argoids)))); - } + if (!OidIsValid(oid)) + { + if (noError) + return InvalidOid; + else if (func->args_unspecified) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_FUNCTION), + errmsg("could not find a aggregate named \"%s\"", + NameListToString(func->objname)))); + else if (argcount == 0) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_FUNCTION), + errmsg("aggregate %s(*) does not exist", + NameListToString(func->objname)))); + else + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_FUNCTION), + errmsg("aggregate %s does not exist", + func_signature_string(func->objname, argcount, + NIL, argoids)))); + } - ReleaseSysCache(ftup); + /* Make sure it's an aggregate */ + if (!get_func_isagg(oid)) + { + if (noError) + return InvalidOid; + /* we do not use the (*) notation for functions... */ + ereport(ERROR, + (errcode(ERRCODE_WRONG_OBJECT_TYPE), + errmsg("function %s is not an aggregate", + func_signature_string(func->objname, argcount, + NIL, argoids)))); + } + } return oid; } - /* * check_srf_call_placement * Verify that a set-returning function is called in a valid place, @@ -2236,6 +2280,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_PARTITION_EXPRESSION: err = _("set-returning functions are not allowed in partition key expressions"); break; + case EXPR_KIND_CALL: + err = _("set-returning functions are not allowed in CALL arguments"); + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/tcop/utility.c b/src/backend/tcop/utility.c index 82a707af7b8..4da1f8f6430 100644 --- a/src/backend/tcop/utility.c +++ b/src/backend/tcop/utility.c @@ -657,6 +657,10 @@ standard_ProcessUtility(PlannedStmt *pstmt, } break; + case T_CallStmt: + ExecuteCallStmt(pstate, castNode(CallStmt, parsetree)); + break; + case T_ClusterStmt: /* we choose to allow this during "read only" transactions */ PreventCommandDuringRecovery("CLUSTER"); @@ -1957,9 +1961,15 @@ AlterObjectTypeCommandTag(ObjectType objtype) case OBJECT_POLICY: tag = "ALTER POLICY"; break; + case OBJECT_PROCEDURE: + tag = "ALTER PROCEDURE"; + break; case OBJECT_ROLE: tag = "ALTER ROLE"; break; + case OBJECT_ROUTINE: + tag = "ALTER ROUTINE"; + break; case OBJECT_RULE: tag = "ALTER RULE"; break; @@ -2261,6 +2271,12 @@ CreateCommandTag(Node *parsetree) case OBJECT_FUNCTION: tag = "DROP FUNCTION"; break; + case OBJECT_PROCEDURE: + tag = "DROP PROCEDURE"; + break; + case OBJECT_ROUTINE: + tag = "DROP ROUTINE"; + break; case OBJECT_AGGREGATE: tag = "DROP AGGREGATE"; break; @@ -2359,7 +2375,20 @@ CreateCommandTag(Node *parsetree) break; case T_AlterFunctionStmt: - tag = "ALTER FUNCTION"; + switch (((AlterFunctionStmt *) parsetree)->objtype) + { + case OBJECT_FUNCTION: + tag = "ALTER FUNCTION"; + break; + case OBJECT_PROCEDURE: + tag = "ALTER PROCEDURE"; + break; + case OBJECT_ROUTINE: + tag = "ALTER ROUTINE"; + break; + default: + tag = "???"; + } break; case T_GrantStmt: @@ -2438,7 +2467,10 @@ CreateCommandTag(Node *parsetree) break; case T_CreateFunctionStmt: - tag = "CREATE FUNCTION"; + if (((CreateFunctionStmt *) parsetree)->is_procedure) + tag = "CREATE PROCEDURE"; + else + tag = "CREATE FUNCTION"; break; case T_IndexStmt: @@ -2493,6 +2525,10 @@ CreateCommandTag(Node *parsetree) tag = "LOAD"; break; + case T_CallStmt: + tag = "CALL"; + break; + case T_ClusterStmt: tag = "CLUSTER"; break; @@ -3116,6 +3152,10 @@ GetCommandLogLevel(Node *parsetree) lev = LOGSTMT_ALL; break; + case T_CallStmt: + lev = LOGSTMT_ALL; + break; + case T_ClusterStmt: lev = LOGSTMT_DDL; break; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 06cf32f5d75..8514c21c407 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -2691,6 +2691,12 @@ pg_get_function_result(PG_FUNCTION_ARGS) if (!HeapTupleIsValid(proctup)) PG_RETURN_NULL(); + if (((Form_pg_proc) GETSTRUCT(proctup))->prorettype == InvalidOid) + { + ReleaseSysCache(proctup); + PG_RETURN_NULL(); + } + initStringInfo(&buf); print_function_rettype(&buf, proctup); diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index 0ea2f2bc54c..52113607775 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -1615,6 +1615,25 @@ func_parallel(Oid funcid) } /* + * get_func_isagg + * Given procedure id, return the function's proisagg field. + */ +bool +get_func_isagg(Oid funcid) +{ + HeapTuple tp; + bool result; + + tp = SearchSysCache1(PROCOID, ObjectIdGetDatum(funcid)); + if (!HeapTupleIsValid(tp)) + elog(ERROR, "cache lookup failed for function %u", funcid); + + result = ((Form_pg_proc) GETSTRUCT(tp))->proisagg; + ReleaseSysCache(tp); + return result; +} + +/* * get_func_leakproof * Given procedure id, return the function's leakproof field. */ diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c index 70d8f24d173..12290a1aae6 100644 --- a/src/bin/pg_dump/dumputils.c +++ b/src/bin/pg_dump/dumputils.c @@ -33,7 +33,7 @@ static void AddAcl(PQExpBuffer aclbuf, const char *keyword, * name: the object name, in the form to use in the commands (already quoted) * subname: the sub-object name, if any (already quoted); NULL if none * type: the object type (as seen in GRANT command: must be one of - * TABLE, SEQUENCE, FUNCTION, LANGUAGE, SCHEMA, DATABASE, TABLESPACE, + * TABLE, SEQUENCE, FUNCTION, PROCEDURE, LANGUAGE, SCHEMA, DATABASE, TABLESPACE, * FOREIGN DATA WRAPPER, SERVER, or LARGE OBJECT) * acls: the ACL string fetched from the database * racls: the ACL string of any initial-but-now-revoked privileges @@ -524,6 +524,9 @@ do { \ else if (strcmp(type, "FUNCTION") == 0 || strcmp(type, "FUNCTIONS") == 0) CONVERT_PRIV('X', "EXECUTE"); + else if (strcmp(type, "PROCEDURE") == 0 || + strcmp(type, "PROCEDURES") == 0) + CONVERT_PRIV('X', "EXECUTE"); else if (strcmp(type, "LANGUAGE") == 0) CONVERT_PRIV('U', "USAGE"); else if (strcmp(type, "SCHEMA") == 0 || diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c index ec2fa8b9b9a..41741aefbc5 100644 --- a/src/bin/pg_dump/pg_backup_archiver.c +++ b/src/bin/pg_dump/pg_backup_archiver.c @@ -2889,7 +2889,8 @@ _tocEntryRequired(TocEntry *te, teSection curSection, RestoreOptions *ropt) if (ropt->indexNames.head != NULL && (!(simple_string_list_member(&ropt->indexNames, te->tag)))) return 0; } - else if (strcmp(te->desc, "FUNCTION") == 0) + else if (strcmp(te->desc, "FUNCTION") == 0 || + strcmp(te->desc, "PROCEDURE") == 0) { if (!ropt->selFunction) return 0; @@ -3388,7 +3389,8 @@ _getObjectDescription(PQExpBuffer buf, TocEntry *te, ArchiveHandle *AH) strcmp(type, "FUNCTION") == 0 || strcmp(type, "OPERATOR") == 0 || strcmp(type, "OPERATOR CLASS") == 0 || - strcmp(type, "OPERATOR FAMILY") == 0) + strcmp(type, "OPERATOR FAMILY") == 0 || + strcmp(type, "PROCEDURE") == 0) { /* Chop "DROP " off the front and make a modifiable copy */ char *first = pg_strdup(te->dropStmt + 5); @@ -3560,6 +3562,7 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData) strcmp(te->desc, "OPERATOR") == 0 || strcmp(te->desc, "OPERATOR CLASS") == 0 || strcmp(te->desc, "OPERATOR FAMILY") == 0 || + strcmp(te->desc, "PROCEDURE") == 0 || strcmp(te->desc, "PROCEDURAL LANGUAGE") == 0 || strcmp(te->desc, "SCHEMA") == 0 || strcmp(te->desc, "EVENT TRIGGER") == 0 || diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index d8fb3561302..e6701aaa782 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -11349,6 +11349,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo) char *funcargs; char *funciargs; char *funcresult; + bool is_procedure; char *proallargtypes; char *proargmodes; char *proargnames; @@ -11370,6 +11371,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo) char **argnames = NULL; char **configitems = NULL; int nconfigitems = 0; + const char *keyword; int i; /* Skip if not to be dumped */ @@ -11513,7 +11515,11 @@ dumpFunc(Archive *fout, FuncInfo *finfo) { funcargs = PQgetvalue(res, 0, PQfnumber(res, "funcargs")); funciargs = PQgetvalue(res, 0, PQfnumber(res, "funciargs")); - funcresult = PQgetvalue(res, 0, PQfnumber(res, "funcresult")); + is_procedure = PQgetisnull(res, 0, PQfnumber(res, "funcresult")); + if (is_procedure) + funcresult = NULL; + else + funcresult = PQgetvalue(res, 0, PQfnumber(res, "funcresult")); proallargtypes = proargmodes = proargnames = NULL; } else @@ -11522,6 +11528,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo) proargmodes = PQgetvalue(res, 0, PQfnumber(res, "proargmodes")); proargnames = PQgetvalue(res, 0, PQfnumber(res, "proargnames")); funcargs = funciargs = funcresult = NULL; + is_procedure = false; } if (PQfnumber(res, "protrftypes") != -1) protrftypes = PQgetvalue(res, 0, PQfnumber(res, "protrftypes")); @@ -11653,22 +11660,29 @@ dumpFunc(Archive *fout, FuncInfo *finfo) funcsig_tag = format_function_signature(fout, finfo, false); + keyword = is_procedure ? "PROCEDURE" : "FUNCTION"; + /* * DROP must be fully qualified in case same name appears in pg_catalog */ - appendPQExpBuffer(delqry, "DROP FUNCTION %s.%s;\n", + appendPQExpBuffer(delqry, "DROP %s %s.%s;\n", + keyword, fmtId(finfo->dobj.namespace->dobj.name), funcsig); - appendPQExpBuffer(q, "CREATE FUNCTION %s ", funcfullsig ? funcfullsig : + appendPQExpBuffer(q, "CREATE %s %s", + keyword, + funcfullsig ? funcfullsig : funcsig); - if (funcresult) - appendPQExpBuffer(q, "RETURNS %s", funcresult); + if (is_procedure) + ; + else if (funcresult) + appendPQExpBuffer(q, " RETURNS %s", funcresult); else { rettypename = getFormattedTypeName(fout, finfo->prorettype, zeroAsOpaque); - appendPQExpBuffer(q, "RETURNS %s%s", + appendPQExpBuffer(q, " RETURNS %s%s", (proretset[0] == 't') ? "SETOF " : "", rettypename); free(rettypename); @@ -11775,7 +11789,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo) appendPQExpBuffer(q, "\n %s;\n", asPart->data); - appendPQExpBuffer(labelq, "FUNCTION %s", funcsig); + appendPQExpBuffer(labelq, "%s %s", keyword, funcsig); if (dopt->binary_upgrade) binary_upgrade_extension_member(q, &finfo->dobj, labelq->data); @@ -11786,7 +11800,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo) finfo->dobj.namespace->dobj.name, NULL, finfo->rolname, false, - "FUNCTION", SECTION_PRE_DATA, + keyword, SECTION_PRE_DATA, q->data, delqry->data, NULL, NULL, 0, NULL, NULL); @@ -11803,7 +11817,7 @@ dumpFunc(Archive *fout, FuncInfo *finfo) finfo->dobj.catId, 0, finfo->dobj.dumpId); if (finfo->dobj.dump & DUMP_COMPONENT_ACL) - dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, "FUNCTION", + dumpACL(fout, finfo->dobj.catId, finfo->dobj.dumpId, keyword, funcsig, NULL, funcsig_tag, finfo->dobj.namespace->dobj.name, finfo->rolname, finfo->proacl, finfo->rproacl, diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index fa3b56a426f..7cf9bdadb2e 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -3654,6 +3654,44 @@ qr/^\QCREATE DEFAULT CONVERSION test_conversion FOR 'LATIN1' TO 'UTF8' FROM iso8 section_data => 1, section_post_data => 1, }, }, + 'CREATE PROCEDURE dump_test.ptest1' => { + all_runs => 1, + create_order => 41, + create_sql => 'CREATE PROCEDURE dump_test.ptest1(a int) + LANGUAGE SQL AS $$ INSERT INTO dump_test.test_table (col1) VALUES (a) $$;', + regexp => qr/^ + \QCREATE PROCEDURE ptest1(a integer)\E + \n\s+\QLANGUAGE sql\E + \n\s+AS\ \$\$\Q INSERT INTO dump_test.test_table (col1) VALUES (a) \E\$\$; + /xm, + like => { + binary_upgrade => 1, + clean => 1, + clean_if_exists => 1, + createdb => 1, + defaults => 1, + exclude_test_table => 1, + exclude_test_table_data => 1, + no_blobs => 1, + no_privs => 1, + no_owner => 1, + only_dump_test_schema => 1, + pg_dumpall_dbprivs => 1, + schema_only => 1, + section_pre_data => 1, + test_schema_plus_blobs => 1, + with_oids => 1, }, + unlike => { + column_inserts => 1, + data_only => 1, + exclude_dump_test_schema => 1, + only_dump_test_table => 1, + pg_dumpall_globals => 1, + pg_dumpall_globals_clean => 1, + role => 1, + section_data => 1, + section_post_data => 1, }, }, + 'CREATE TYPE dump_test.int42 populated' => { all_runs => 1, create_order => 42, diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 804a84a0c91..3fc69c46c06 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -353,6 +353,7 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool " CASE\n" " WHEN p.proisagg THEN '%s'\n" " WHEN p.proiswindow THEN '%s'\n" + " WHEN p.prorettype = 0 THEN '%s'\n" " WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN '%s'\n" " ELSE '%s'\n" " END as \"%s\"", @@ -361,8 +362,9 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool /* translator: "agg" is short for "aggregate" */ gettext_noop("agg"), gettext_noop("window"), + gettext_noop("proc"), gettext_noop("trigger"), - gettext_noop("normal"), + gettext_noop("func"), gettext_noop("Type")); else if (pset.sversion >= 80100) appendPQExpBuffer(&buf, @@ -407,7 +409,7 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool /* translator: "agg" is short for "aggregate" */ gettext_noop("agg"), gettext_noop("trigger"), - gettext_noop("normal"), + gettext_noop("func"), gettext_noop("Type")); else appendPQExpBuffer(&buf, @@ -424,7 +426,7 @@ describeFunctions(const char *functypes, const char *pattern, bool verbose, bool /* translator: "agg" is short for "aggregate" */ gettext_noop("agg"), gettext_noop("trigger"), - gettext_noop("normal"), + gettext_noop("func"), gettext_noop("Type")); if (verbose) diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index b3e3799c132..468e50aa317 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -397,7 +397,7 @@ static const SchemaQuery Query_for_list_of_functions = { /* catname */ "pg_catalog.pg_proc p", /* selcondition */ - NULL, + "p.prorettype <> 0", /* viscondition */ "pg_catalog.pg_function_is_visible(p.oid)", /* namespace */ @@ -423,6 +423,36 @@ static const SchemaQuery Query_for_list_of_indexes = { NULL }; +static const SchemaQuery Query_for_list_of_procedures = { + /* catname */ + "pg_catalog.pg_proc p", + /* selcondition */ + "p.prorettype = 0", + /* viscondition */ + "pg_catalog.pg_function_is_visible(p.oid)", + /* namespace */ + "p.pronamespace", + /* result */ + "pg_catalog.quote_ident(p.proname)", + /* qualresult */ + NULL +}; + +static const SchemaQuery Query_for_list_of_routines = { + /* catname */ + "pg_catalog.pg_proc p", + /* selcondition */ + NULL, + /* viscondition */ + "pg_catalog.pg_function_is_visible(p.oid)", + /* namespace */ + "p.pronamespace", + /* result */ + "pg_catalog.quote_ident(p.proname)", + /* qualresult */ + NULL +}; + static const SchemaQuery Query_for_list_of_sequences = { /* catname */ "pg_catalog.pg_class c", @@ -1032,8 +1062,10 @@ static const pgsql_thing_t words_after_create[] = { {"OWNED", NULL, NULL, THING_NO_CREATE | THING_NO_ALTER}, /* for DROP OWNED BY ... */ {"PARSER", Query_for_list_of_ts_parsers, NULL, THING_NO_SHOW}, {"POLICY", NULL, NULL}, + {"PROCEDURE", NULL, &Query_for_list_of_procedures}, {"PUBLICATION", Query_for_list_of_publications}, {"ROLE", Query_for_list_of_roles}, + {"ROUTINE", NULL, &Query_for_list_of_routines, THING_NO_CREATE}, {"RULE", "SELECT pg_catalog.quote_ident(rulename) FROM pg_catalog.pg_rules WHERE substring(pg_catalog.quote_ident(rulename),1,%d)='%s'"}, {"SCHEMA", Query_for_list_of_schemas}, {"SEQUENCE", NULL, &Query_for_list_of_sequences}, @@ -1407,7 +1439,7 @@ psql_completion(const char *text, int start, int end) /* Known command-starting keywords. */ static const char *const sql_commands[] = { - "ABORT", "ALTER", "ANALYZE", "BEGIN", "CHECKPOINT", "CLOSE", "CLUSTER", + "ABORT", "ALTER", "ANALYZE", "BEGIN", "CALL", "CHECKPOINT", "CLOSE", "CLUSTER", "COMMENT", "COMMIT", "COPY", "CREATE", "DEALLOCATE", "DECLARE", "DELETE FROM", "DISCARD", "DO", "DROP", "END", "EXECUTE", "EXPLAIN", "FETCH", "GRANT", "IMPORT", "INSERT", "LISTEN", "LOAD", "LOCK", @@ -1520,11 +1552,11 @@ psql_completion(const char *text, int start, int end) /* ALTER TABLE,INDEX,MATERIALIZED VIEW ALL IN TABLESPACE xxx OWNED BY xxx */ else if (TailMatches7("ALL", "IN", "TABLESPACE", MatchAny, "OWNED", "BY", MatchAny)) COMPLETE_WITH_CONST("SET TABLESPACE"); - /* ALTER AGGREGATE,FUNCTION <name> */ - else if (Matches3("ALTER", "AGGREGATE|FUNCTION", MatchAny)) + /* ALTER AGGREGATE,FUNCTION,PROCEDURE,ROUTINE <name> */ + else if (Matches3("ALTER", "AGGREGATE|FUNCTION|PROCEDURE|ROUTINE", MatchAny)) COMPLETE_WITH_CONST("("); - /* ALTER AGGREGATE,FUNCTION <name> (...) */ - else if (Matches4("ALTER", "AGGREGATE|FUNCTION", MatchAny, MatchAny)) + /* ALTER AGGREGATE,FUNCTION,PROCEDURE,ROUTINE <name> (...) */ + else if (Matches4("ALTER", "AGGREGATE|FUNCTION|PROCEDURE|ROUTINE", MatchAny, MatchAny)) { if (ends_with(prev_wd, ')')) COMPLETE_WITH_LIST3("OWNER TO", "RENAME TO", "SET SCHEMA"); @@ -2145,6 +2177,11 @@ psql_completion(const char *text, int start, int end) /* ROLLBACK */ else if (Matches1("ROLLBACK")) COMPLETE_WITH_LIST4("WORK", "TRANSACTION", "TO SAVEPOINT", "PREPARED"); +/* CALL */ + else if (Matches1("CALL")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_procedures, NULL); + else if (Matches2("CALL", MatchAny)) + COMPLETE_WITH_CONST("("); /* CLUSTER */ else if (Matches1("CLUSTER")) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tm, "UNION SELECT 'VERBOSE'"); @@ -2176,6 +2213,7 @@ psql_completion(const char *text, int start, int end) "SERVER", "INDEX", "LANGUAGE", "POLICY", "PUBLICATION", "RULE", "SCHEMA", "SEQUENCE", "STATISTICS", "SUBSCRIPTION", "TABLE", "TYPE", "VIEW", "MATERIALIZED VIEW", "COLUMN", "AGGREGATE", "FUNCTION", + "PROCEDURE", "ROUTINE", "OPERATOR", "TRIGGER", "CONSTRAINT", "DOMAIN", "LARGE OBJECT", "TABLESPACE", "TEXT SEARCH", "ROLE", NULL}; @@ -2685,7 +2723,7 @@ psql_completion(const char *text, int start, int end) "COLLATION|CONVERSION|DOMAIN|EXTENSION|LANGUAGE|PUBLICATION|SCHEMA|SEQUENCE|SERVER|SUBSCRIPTION|STATISTICS|TABLE|TYPE|VIEW", MatchAny) || Matches4("DROP", "ACCESS", "METHOD", MatchAny) || - (Matches4("DROP", "AGGREGATE|FUNCTION", MatchAny, MatchAny) && + (Matches4("DROP", "AGGREGATE|FUNCTION|PROCEDURE|ROUTINE", MatchAny, MatchAny) && ends_with(prev_wd, ')')) || Matches4("DROP", "EVENT", "TRIGGER", MatchAny) || Matches5("DROP", "FOREIGN", "DATA", "WRAPPER", MatchAny) || @@ -2694,9 +2732,9 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH_LIST2("CASCADE", "RESTRICT"); /* help completing some of the variants */ - else if (Matches3("DROP", "AGGREGATE|FUNCTION", MatchAny)) + else if (Matches3("DROP", "AGGREGATE|FUNCTION|PROCEDURE|ROUTINE", MatchAny)) COMPLETE_WITH_CONST("("); - else if (Matches4("DROP", "AGGREGATE|FUNCTION", MatchAny, "(")) + else if (Matches4("DROP", "AGGREGATE|FUNCTION|PROCEDURE|ROUTINE", MatchAny, "(")) COMPLETE_WITH_FUNCTION_ARG(prev2_wd); else if (Matches2("DROP", "FOREIGN")) COMPLETE_WITH_LIST2("DATA WRAPPER", "TABLE"); @@ -2893,10 +2931,12 @@ psql_completion(const char *text, int start, int end) * objects supported. */ if (HeadMatches3("ALTER", "DEFAULT", "PRIVILEGES")) - COMPLETE_WITH_LIST5("TABLES", "SEQUENCES", "FUNCTIONS", "TYPES", "SCHEMAS"); + COMPLETE_WITH_LIST7("TABLES", "SEQUENCES", "FUNCTIONS", "PROCEDURES", "ROUTINES", "TYPES", "SCHEMAS"); else COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tsvmf, " UNION SELECT 'ALL FUNCTIONS IN SCHEMA'" + " UNION SELECT 'ALL PROCEDURES IN SCHEMA'" + " UNION SELECT 'ALL ROUTINES IN SCHEMA'" " UNION SELECT 'ALL SEQUENCES IN SCHEMA'" " UNION SELECT 'ALL TABLES IN SCHEMA'" " UNION SELECT 'DATABASE'" @@ -2906,6 +2946,8 @@ psql_completion(const char *text, int start, int end) " UNION SELECT 'FUNCTION'" " UNION SELECT 'LANGUAGE'" " UNION SELECT 'LARGE OBJECT'" + " UNION SELECT 'PROCEDURE'" + " UNION SELECT 'ROUTINE'" " UNION SELECT 'SCHEMA'" " UNION SELECT 'SEQUENCE'" " UNION SELECT 'TABLE'" @@ -2913,7 +2955,10 @@ psql_completion(const char *text, int start, int end) " UNION SELECT 'TYPE'"); } else if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "ALL")) - COMPLETE_WITH_LIST3("FUNCTIONS IN SCHEMA", "SEQUENCES IN SCHEMA", + COMPLETE_WITH_LIST5("FUNCTIONS IN SCHEMA", + "PROCEDURES IN SCHEMA", + "ROUTINES IN SCHEMA", + "SEQUENCES IN SCHEMA", "TABLES IN SCHEMA"); else if (TailMatches4("GRANT|REVOKE", MatchAny, "ON", "FOREIGN")) COMPLETE_WITH_LIST2("DATA WRAPPER", "SERVER"); @@ -2934,6 +2979,10 @@ psql_completion(const char *text, int start, int end) COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_functions, NULL); else if (TailMatches1("LANGUAGE")) COMPLETE_WITH_QUERY(Query_for_list_of_languages); + else if (TailMatches1("PROCEDURE")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_procedures, NULL); + else if (TailMatches1("ROUTINE")) + COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_routines, NULL); else if (TailMatches1("SCHEMA")) COMPLETE_WITH_QUERY(Query_for_list_of_schemas); else if (TailMatches1("SEQUENCE")) @@ -3163,7 +3212,7 @@ psql_completion(const char *text, int start, int end) static const char *const list_SECURITY_LABEL[] = {"TABLE", "COLUMN", "AGGREGATE", "DATABASE", "DOMAIN", "EVENT TRIGGER", "FOREIGN TABLE", "FUNCTION", "LARGE OBJECT", - "MATERIALIZED VIEW", "LANGUAGE", "PUBLICATION", "ROLE", "SCHEMA", + "MATERIALIZED VIEW", "LANGUAGE", "PUBLICATION", "PROCEDURE", "ROLE", "ROUTINE", "SCHEMA", "SEQUENCE", "SUBSCRIPTION", "TABLESPACE", "TYPE", "VIEW", NULL}; COMPLETE_WITH_LIST(list_SECURITY_LABEL); @@ -3233,8 +3282,8 @@ psql_completion(const char *text, int start, int end) /* Complete SET <var> with "TO" */ else if (Matches2("SET", MatchAny)) COMPLETE_WITH_CONST("TO"); - /* Complete ALTER DATABASE|FUNCTION|ROLE|USER ... SET <name> */ - else if (HeadMatches2("ALTER", "DATABASE|FUNCTION|ROLE|USER") && + /* Complete ALTER DATABASE|FUNCTION||PROCEDURE|ROLE|ROUTINE|USER ... SET <name> */ + else if (HeadMatches2("ALTER", "DATABASE|FUNCTION|PROCEDURE|ROLE|ROUTINE|USER") && TailMatches2("SET", MatchAny)) COMPLETE_WITH_LIST2("FROM CURRENT", "TO"); /* Suggest possible variable values */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index a30ce6b81d8..b13cf62beca 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201711171 +#define CATALOG_VERSION_NO 201711301 #endif diff --git a/src/include/commands/defrem.h b/src/include/commands/defrem.h index bfead9af3d1..52cbf61ccb0 100644 --- a/src/include/commands/defrem.h +++ b/src/include/commands/defrem.h @@ -59,12 +59,13 @@ extern void DropTransformById(Oid transformOid); extern void IsThereFunctionInNamespace(const char *proname, int pronargs, oidvector *proargtypes, Oid nspOid); extern void ExecuteDoStmt(DoStmt *stmt); +extern void ExecuteCallStmt(ParseState *pstate, CallStmt *stmt); extern Oid get_cast_oid(Oid sourcetypeid, Oid targettypeid, bool missing_ok); extern Oid get_transform_oid(Oid type_id, Oid lang_id, bool missing_ok); extern void interpret_function_parameter_list(ParseState *pstate, List *parameters, Oid languageOid, - bool is_aggregate, + ObjectType objtype, oidvector **parameterTypes, ArrayType **allParameterTypes, ArrayType **parameterModes, diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 03dc5307e8a..c5b5115f5b4 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -414,6 +414,7 @@ typedef enum NodeTag T_DropSubscriptionStmt, T_CreateStatsStmt, T_AlterCollationStmt, + T_CallStmt, /* * TAGS FOR PARSE TREE NODES (parsenodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 34d6afc80f4..2eaa6b27740 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -1642,9 +1642,11 @@ typedef enum ObjectType OBJECT_OPERATOR, OBJECT_OPFAMILY, OBJECT_POLICY, + OBJECT_PROCEDURE, OBJECT_PUBLICATION, OBJECT_PUBLICATION_REL, OBJECT_ROLE, + OBJECT_ROUTINE, OBJECT_RULE, OBJECT_SCHEMA, OBJECT_SEQUENCE, @@ -1856,6 +1858,8 @@ typedef enum GrantObjectType ACL_OBJECT_LANGUAGE, /* procedural language */ ACL_OBJECT_LARGEOBJECT, /* largeobject */ ACL_OBJECT_NAMESPACE, /* namespace */ + ACL_OBJECT_PROCEDURE, /* procedure */ + ACL_OBJECT_ROUTINE, /* routine */ ACL_OBJECT_TABLESPACE, /* tablespace */ ACL_OBJECT_TYPE /* type */ } GrantObjectType; @@ -2749,6 +2753,7 @@ typedef struct CreateFunctionStmt List *funcname; /* qualified name of function to create */ List *parameters; /* a list of FunctionParameter */ TypeName *returnType; /* the return type */ + bool is_procedure; List *options; /* a list of DefElem */ List *withClause; /* a list of DefElem */ } CreateFunctionStmt; @@ -2775,6 +2780,7 @@ typedef struct FunctionParameter typedef struct AlterFunctionStmt { NodeTag type; + ObjectType objtype; ObjectWithArgs *func; /* name and args of function */ List *actions; /* list of DefElem */ } AlterFunctionStmt; @@ -2800,6 +2806,16 @@ typedef struct InlineCodeBlock } InlineCodeBlock; /* ---------------------- + * CALL statement + * ---------------------- + */ +typedef struct CallStmt +{ + NodeTag type; + FuncCall *funccall; +} CallStmt; + +/* ---------------------- * Alter Object Rename Statement * ---------------------- */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index f50e45e886d..a9324000588 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -63,6 +63,7 @@ PG_KEYWORD("boolean", BOOLEAN_P, COL_NAME_KEYWORD) PG_KEYWORD("both", BOTH, RESERVED_KEYWORD) PG_KEYWORD("by", BY, UNRESERVED_KEYWORD) PG_KEYWORD("cache", CACHE, UNRESERVED_KEYWORD) +PG_KEYWORD("call", CALL, UNRESERVED_KEYWORD) PG_KEYWORD("called", CALLED, UNRESERVED_KEYWORD) PG_KEYWORD("cascade", CASCADE, UNRESERVED_KEYWORD) PG_KEYWORD("cascaded", CASCADED, UNRESERVED_KEYWORD) @@ -310,6 +311,7 @@ PG_KEYWORD("prior", PRIOR, UNRESERVED_KEYWORD) PG_KEYWORD("privileges", PRIVILEGES, UNRESERVED_KEYWORD) PG_KEYWORD("procedural", PROCEDURAL, UNRESERVED_KEYWORD) PG_KEYWORD("procedure", PROCEDURE, UNRESERVED_KEYWORD) +PG_KEYWORD("procedures", PROCEDURES, UNRESERVED_KEYWORD) PG_KEYWORD("program", PROGRAM, UNRESERVED_KEYWORD) PG_KEYWORD("publication", PUBLICATION, UNRESERVED_KEYWORD) PG_KEYWORD("quote", QUOTE, UNRESERVED_KEYWORD) @@ -340,6 +342,8 @@ PG_KEYWORD("right", RIGHT, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("role", ROLE, UNRESERVED_KEYWORD) PG_KEYWORD("rollback", ROLLBACK, UNRESERVED_KEYWORD) PG_KEYWORD("rollup", ROLLUP, UNRESERVED_KEYWORD) +PG_KEYWORD("routine", ROUTINE, UNRESERVED_KEYWORD) +PG_KEYWORD("routines", ROUTINES, UNRESERVED_KEYWORD) PG_KEYWORD("row", ROW, COL_NAME_KEYWORD) PG_KEYWORD("rows", ROWS, UNRESERVED_KEYWORD) PG_KEYWORD("rule", RULE, UNRESERVED_KEYWORD) diff --git a/src/include/parser/parse_func.h b/src/include/parser/parse_func.h index b4b6084b1bf..fccccd21ed4 100644 --- a/src/include/parser/parse_func.h +++ b/src/include/parser/parse_func.h @@ -24,6 +24,7 @@ typedef enum FUNCDETAIL_NOTFOUND, /* no matching function */ FUNCDETAIL_MULTIPLE, /* too many matching functions */ FUNCDETAIL_NORMAL, /* found a matching regular function */ + FUNCDETAIL_PROCEDURE, /* found a matching procedure */ FUNCDETAIL_AGGREGATE, /* found a matching aggregate function */ FUNCDETAIL_WINDOWFUNC, /* found a matching window function */ FUNCDETAIL_COERCION /* it's a type coercion request */ @@ -31,7 +32,8 @@ typedef enum extern Node *ParseFuncOrColumn(ParseState *pstate, List *funcname, List *fargs, - Node *last_srf, FuncCall *fn, int location); + Node *last_srf, FuncCall *fn, bool proc_call, + int location); extern FuncDetailCode func_get_detail(List *funcname, List *fargs, List *fargnames, @@ -62,10 +64,8 @@ extern const char *func_signature_string(List *funcname, int nargs, extern Oid LookupFuncName(List *funcname, int nargs, const Oid *argtypes, bool noError); -extern Oid LookupFuncWithArgs(ObjectWithArgs *func, +extern Oid LookupFuncWithArgs(ObjectType objtype, ObjectWithArgs *func, bool noError); -extern Oid LookupAggWithArgs(ObjectWithArgs *agg, - bool noError); extern void check_srf_call_placement(ParseState *pstate, Node *last_srf, int location); diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h index f0e210ad8d8..565bb3dc6cf 100644 --- a/src/include/parser/parse_node.h +++ b/src/include/parser/parse_node.h @@ -67,7 +67,8 @@ typedef enum ParseExprKind EXPR_KIND_EXECUTE_PARAMETER, /* parameter value in EXECUTE */ EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */ EXPR_KIND_POLICY, /* USING or WITH CHECK expr in policy */ - EXPR_KIND_PARTITION_EXPRESSION /* PARTITION BY expression */ + EXPR_KIND_PARTITION_EXPRESSION, /* PARTITION BY expression */ + EXPR_KIND_CALL /* CALL argument */ } ParseExprKind; diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index 07208b56cef..b316cc594c6 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -118,6 +118,7 @@ extern bool get_func_retset(Oid funcid); extern bool func_strict(Oid funcid); extern char func_volatile(Oid funcid); extern char func_parallel(Oid funcid); +extern bool get_func_isagg(Oid funcid); extern bool get_func_leakproof(Oid funcid); extern float4 get_func_cost(Oid funcid); extern float4 get_func_rows(Oid funcid); diff --git a/src/interfaces/ecpg/preproc/ecpg.tokens b/src/interfaces/ecpg/preproc/ecpg.tokens index 68ba925efe4..1d613af02f6 100644 --- a/src/interfaces/ecpg/preproc/ecpg.tokens +++ b/src/interfaces/ecpg/preproc/ecpg.tokens @@ -2,7 +2,7 @@ /* special embedded SQL tokens */ %token SQL_ALLOCATE SQL_AUTOCOMMIT SQL_BOOL SQL_BREAK - SQL_CALL SQL_CARDINALITY SQL_CONNECT + SQL_CARDINALITY SQL_CONNECT SQL_COUNT SQL_DATETIME_INTERVAL_CODE SQL_DATETIME_INTERVAL_PRECISION SQL_DESCRIBE diff --git a/src/interfaces/ecpg/preproc/ecpg.trailer b/src/interfaces/ecpg/preproc/ecpg.trailer index f60a62099d4..19dc7818859 100644 --- a/src/interfaces/ecpg/preproc/ecpg.trailer +++ b/src/interfaces/ecpg/preproc/ecpg.trailer @@ -1460,13 +1460,13 @@ action : CONTINUE_P $<action>$.command = NULL; $<action>$.str = mm_strdup("continue"); } - | SQL_CALL name '(' c_args ')' + | CALL name '(' c_args ')' { $<action>$.code = W_DO; $<action>$.command = cat_str(4, $2, mm_strdup("("), $4, mm_strdup(")")); $<action>$.str = cat2_str(mm_strdup("call"), mm_strdup($<action>$.command)); } - | SQL_CALL name + | CALL name { $<action>$.code = W_DO; $<action>$.command = cat2_str($2, mm_strdup("()")); @@ -1482,7 +1482,6 @@ ECPGKeywords: ECPGKeywords_vanames { $$ = $1; } ; ECPGKeywords_vanames: SQL_BREAK { $$ = mm_strdup("break"); } - | SQL_CALL { $$ = mm_strdup("call"); } | SQL_CARDINALITY { $$ = mm_strdup("cardinality"); } | SQL_COUNT { $$ = mm_strdup("count"); } | SQL_DATETIME_INTERVAL_CODE { $$ = mm_strdup("datetime_interval_code"); } diff --git a/src/interfaces/ecpg/preproc/ecpg_keywords.c b/src/interfaces/ecpg/preproc/ecpg_keywords.c index 3b52b8f3a29..848b2d48493 100644 --- a/src/interfaces/ecpg/preproc/ecpg_keywords.c +++ b/src/interfaces/ecpg/preproc/ecpg_keywords.c @@ -33,7 +33,6 @@ static const ScanKeyword ECPGScanKeywords[] = { {"autocommit", SQL_AUTOCOMMIT, 0}, {"bool", SQL_BOOL, 0}, {"break", SQL_BREAK, 0}, - {"call", SQL_CALL, 0}, {"cardinality", SQL_CARDINALITY, 0}, {"connect", SQL_CONNECT, 0}, {"count", SQL_COUNT, 0}, diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile index 91d1296b219..b829027d05d 100644 --- a/src/pl/plperl/GNUmakefile +++ b/src/pl/plperl/GNUmakefile @@ -55,7 +55,7 @@ endif # win32 SHLIB_LINK = $(perl_embed_ldflags) REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-extension=plperl --load-extension=plperlu -REGRESS = plperl plperl_lc plperl_trigger plperl_shared plperl_elog plperl_util plperl_init plperlu plperl_array +REGRESS = plperl plperl_lc plperl_trigger plperl_shared plperl_elog plperl_util plperl_init plperlu plperl_array plperl_call # if Perl can support two interpreters in one backend, # test plperl-and-plperlu cases ifneq ($(PERL),) diff --git a/src/pl/plperl/expected/plperl_call.out b/src/pl/plperl/expected/plperl_call.out new file mode 100644 index 00000000000..4bccfcb7c82 --- /dev/null +++ b/src/pl/plperl/expected/plperl_call.out @@ -0,0 +1,29 @@ +CREATE PROCEDURE test_proc1() +LANGUAGE plperl +AS $$ +undef; +$$; +CALL test_proc1(); +CREATE PROCEDURE test_proc2() +LANGUAGE plperl +AS $$ +return 5 +$$; +CALL test_proc2(); +CREATE TABLE test1 (a int); +CREATE PROCEDURE test_proc3(x int) +LANGUAGE plperl +AS $$ +spi_exec_query("INSERT INTO test1 VALUES ($_[0])"); +$$; +CALL test_proc3(55); +SELECT * FROM test1; + a +---- + 55 +(1 row) + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; +DROP TABLE test1; diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c index a57393fbdd9..9f5313235f3 100644 --- a/src/pl/plperl/plperl.c +++ b/src/pl/plperl/plperl.c @@ -1915,7 +1915,7 @@ plperl_inline_handler(PG_FUNCTION_ARGS) desc.fn_retistuple = false; desc.fn_retisset = false; desc.fn_retisarray = false; - desc.result_oid = VOIDOID; + desc.result_oid = InvalidOid; desc.nargs = 0; desc.reference = NULL; @@ -2481,7 +2481,7 @@ plperl_func_handler(PG_FUNCTION_ARGS) } retval = (Datum) 0; } - else + else if (prodesc->result_oid) { retval = plperl_sv_to_datum(perlret, prodesc->result_oid, @@ -2826,7 +2826,7 @@ compile_plperl_function(Oid fn_oid, bool is_trigger, bool is_event_trigger) * Get the required information for input conversion of the * return value. ************************************************************/ - if (!is_trigger && !is_event_trigger) + if (!is_trigger && !is_event_trigger && procStruct->prorettype) { Oid rettype = procStruct->prorettype; @@ -3343,7 +3343,7 @@ plperl_return_next_internal(SV *sv) tuplestore_puttuple(current_call_data->tuple_store, tuple); } - else + else if (prodesc->result_oid) { Datum ret[1]; bool isNull[1]; diff --git a/src/pl/plperl/sql/plperl_call.sql b/src/pl/plperl/sql/plperl_call.sql new file mode 100644 index 00000000000..bd2b63b4185 --- /dev/null +++ b/src/pl/plperl/sql/plperl_call.sql @@ -0,0 +1,36 @@ +CREATE PROCEDURE test_proc1() +LANGUAGE plperl +AS $$ +undef; +$$; + +CALL test_proc1(); + + +CREATE PROCEDURE test_proc2() +LANGUAGE plperl +AS $$ +return 5 +$$; + +CALL test_proc2(); + + +CREATE TABLE test1 (a int); + +CREATE PROCEDURE test_proc3(x int) +LANGUAGE plperl +AS $$ +spi_exec_query("INSERT INTO test1 VALUES ($_[0])"); +$$; + +CALL test_proc3(55); + +SELECT * FROM test1; + + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; + +DROP TABLE test1; diff --git a/src/pl/plpgsql/src/pl_comp.c b/src/pl/plpgsql/src/pl_comp.c index d0afa592423..f459c02f7be 100644 --- a/src/pl/plpgsql/src/pl_comp.c +++ b/src/pl/plpgsql/src/pl_comp.c @@ -275,7 +275,6 @@ do_compile(FunctionCallInfo fcinfo, bool isnull; char *proc_source; HeapTuple typeTup; - Form_pg_type typeStruct; PLpgSQL_variable *var; PLpgSQL_rec *rec; int i; @@ -531,53 +530,58 @@ do_compile(FunctionCallInfo fcinfo, /* * Lookup the function's return type */ - typeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(rettypeid)); - if (!HeapTupleIsValid(typeTup)) - elog(ERROR, "cache lookup failed for type %u", rettypeid); - typeStruct = (Form_pg_type) GETSTRUCT(typeTup); - - /* Disallow pseudotype result, except VOID or RECORD */ - /* (note we already replaced polymorphic types) */ - if (typeStruct->typtype == TYPTYPE_PSEUDO) + if (rettypeid) { - if (rettypeid == VOIDOID || - rettypeid == RECORDOID) - /* okay */ ; - else if (rettypeid == TRIGGEROID || rettypeid == EVTTRIGGEROID) - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("trigger functions can only be called as triggers"))); - else - ereport(ERROR, - (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), - errmsg("PL/pgSQL functions cannot return type %s", - format_type_be(rettypeid)))); - } + Form_pg_type typeStruct; - if (typeStruct->typrelid != InvalidOid || - rettypeid == RECORDOID) - function->fn_retistuple = true; - else - { - function->fn_retbyval = typeStruct->typbyval; - function->fn_rettyplen = typeStruct->typlen; + typeTup = SearchSysCache1(TYPEOID, ObjectIdGetDatum(rettypeid)); + if (!HeapTupleIsValid(typeTup)) + elog(ERROR, "cache lookup failed for type %u", rettypeid); + typeStruct = (Form_pg_type) GETSTRUCT(typeTup); - /* - * install $0 reference, but only for polymorphic return - * types, and not when the return is specified through an - * output parameter. - */ - if (IsPolymorphicType(procStruct->prorettype) && - num_out_args == 0) + /* Disallow pseudotype result, except VOID or RECORD */ + /* (note we already replaced polymorphic types) */ + if (typeStruct->typtype == TYPTYPE_PSEUDO) { - (void) plpgsql_build_variable("$0", 0, - build_datatype(typeTup, - -1, - function->fn_input_collation), - true); + if (rettypeid == VOIDOID || + rettypeid == RECORDOID) + /* okay */ ; + else if (rettypeid == TRIGGEROID || rettypeid == EVTTRIGGEROID) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("trigger functions can only be called as triggers"))); + else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("PL/pgSQL functions cannot return type %s", + format_type_be(rettypeid)))); + } + + if (typeStruct->typrelid != InvalidOid || + rettypeid == RECORDOID) + function->fn_retistuple = true; + else + { + function->fn_retbyval = typeStruct->typbyval; + function->fn_rettyplen = typeStruct->typlen; + + /* + * install $0 reference, but only for polymorphic return + * types, and not when the return is specified through an + * output parameter. + */ + if (IsPolymorphicType(procStruct->prorettype) && + num_out_args == 0) + { + (void) plpgsql_build_variable("$0", 0, + build_datatype(typeTup, + -1, + function->fn_input_collation), + true); + } } + ReleaseSysCache(typeTup); } - ReleaseSysCache(typeTup); break; case PLPGSQL_DML_TRIGGER: diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c index cf6120eea9a..ec480cb0ba8 100644 --- a/src/pl/plpgsql/src/pl_exec.c +++ b/src/pl/plpgsql/src/pl_exec.c @@ -462,7 +462,7 @@ plpgsql_exec_function(PLpgSQL_function *func, FunctionCallInfo fcinfo, estate.err_text = NULL; estate.err_stmt = (PLpgSQL_stmt *) (func->action); rc = exec_stmt_block(&estate, func->action); - if (rc != PLPGSQL_RC_RETURN) + if (rc != PLPGSQL_RC_RETURN && func->fn_rettype) { estate.err_stmt = NULL; estate.err_text = NULL; @@ -509,6 +509,12 @@ plpgsql_exec_function(PLpgSQL_function *func, FunctionCallInfo fcinfo, } else if (!estate.retisnull) { + if (!func->fn_rettype) + { + ereport(ERROR, + (errmsg("cannot return a value from a procedure"))); + } + if (estate.retistuple) { /* diff --git a/src/pl/plpython/Makefile b/src/pl/plpython/Makefile index 7680d49cb69..cc91afebde7 100644 --- a/src/pl/plpython/Makefile +++ b/src/pl/plpython/Makefile @@ -78,6 +78,7 @@ REGRESS = \ plpython_spi \ plpython_newline \ plpython_void \ + plpython_call \ plpython_params \ plpython_setof \ plpython_record \ diff --git a/src/pl/plpython/expected/plpython_call.out b/src/pl/plpython/expected/plpython_call.out new file mode 100644 index 00000000000..90785343b6f --- /dev/null +++ b/src/pl/plpython/expected/plpython_call.out @@ -0,0 +1,35 @@ +-- +-- Tests for procedures / CALL syntax +-- +CREATE PROCEDURE test_proc1() +LANGUAGE plpythonu +AS $$ +pass +$$; +CALL test_proc1(); +-- error: can't return non-None +CREATE PROCEDURE test_proc2() +LANGUAGE plpythonu +AS $$ +return 5 +$$; +CALL test_proc2(); +ERROR: PL/Python procedure did not return None +CONTEXT: PL/Python procedure "test_proc2" +CREATE TABLE test1 (a int); +CREATE PROCEDURE test_proc3(x int) +LANGUAGE plpythonu +AS $$ +plpy.execute("INSERT INTO test1 VALUES (%s)" % x) +$$; +CALL test_proc3(55); +SELECT * FROM test1; + a +---- + 55 +(1 row) + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; +DROP TABLE test1; diff --git a/src/pl/plpython/plpy_exec.c b/src/pl/plpython/plpy_exec.c index fe217c6a2c1..4594a08eadb 100644 --- a/src/pl/plpython/plpy_exec.c +++ b/src/pl/plpython/plpy_exec.c @@ -199,12 +199,19 @@ PLy_exec_function(FunctionCallInfo fcinfo, PLyProcedure *proc) error_context_stack = &plerrcontext; /* - * If the function is declared to return void, the Python return value + * For a procedure or function declared to return void, the Python return value * must be None. For void-returning functions, we also treat a None * return value as a special "void datum" rather than NULL (as is the * case for non-void-returning functions). */ - if (proc->result.typoid == VOIDOID) + if (proc->is_procedure) + { + if (plrv != Py_None) + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("PL/Python procedure did not return None"))); + } + else if (proc->result.typoid == VOIDOID) { if (plrv != Py_None) ereport(ERROR, @@ -672,7 +679,8 @@ plpython_return_error_callback(void *arg) { PLyExecutionContext *exec_ctx = PLy_current_execution_context(); - if (exec_ctx->curr_proc) + if (exec_ctx->curr_proc && + !exec_ctx->curr_proc->is_procedure) errcontext("while creating return value"); } diff --git a/src/pl/plpython/plpy_main.c b/src/pl/plpython/plpy_main.c index 32d23ae5b6d..695de305838 100644 --- a/src/pl/plpython/plpy_main.c +++ b/src/pl/plpython/plpy_main.c @@ -389,8 +389,14 @@ plpython_error_callback(void *arg) PLyExecutionContext *exec_ctx = PLy_current_execution_context(); if (exec_ctx->curr_proc) - errcontext("PL/Python function \"%s\"", - PLy_procedure_name(exec_ctx->curr_proc)); + { + if (exec_ctx->curr_proc->is_procedure) + errcontext("PL/Python procedure \"%s\"", + PLy_procedure_name(exec_ctx->curr_proc)); + else + errcontext("PL/Python function \"%s\"", + PLy_procedure_name(exec_ctx->curr_proc)); + } } static void diff --git a/src/pl/plpython/plpy_procedure.c b/src/pl/plpython/plpy_procedure.c index faa4977463a..b7c24e356f5 100644 --- a/src/pl/plpython/plpy_procedure.c +++ b/src/pl/plpython/plpy_procedure.c @@ -189,6 +189,7 @@ PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool is_trigger) proc->fn_tid = procTup->t_self; proc->fn_readonly = (procStruct->provolatile != PROVOLATILE_VOLATILE); proc->is_setof = procStruct->proretset; + proc->is_procedure = (procStruct->prorettype == InvalidOid); proc->src = NULL; proc->argnames = NULL; proc->args = NULL; @@ -206,9 +207,9 @@ PLy_procedure_create(HeapTuple procTup, Oid fn_oid, bool is_trigger) /* * get information required for output conversion of the return value, - * but only if this isn't a trigger. + * but only if this isn't a trigger or procedure. */ - if (!is_trigger) + if (!is_trigger && procStruct->prorettype) { Oid rettype = procStruct->prorettype; HeapTuple rvTypeTup; diff --git a/src/pl/plpython/plpy_procedure.h b/src/pl/plpython/plpy_procedure.h index cd1b87fdc3c..8968b5c92e5 100644 --- a/src/pl/plpython/plpy_procedure.h +++ b/src/pl/plpython/plpy_procedure.h @@ -30,7 +30,8 @@ typedef struct PLyProcedure TransactionId fn_xmin; ItemPointerData fn_tid; bool fn_readonly; - bool is_setof; /* true, if procedure returns result set */ + bool is_setof; /* true, if function returns result set */ + bool is_procedure; PLyObToDatum result; /* Function result output conversion info */ PLyDatumToOb result_in; /* For converting input tuples in a trigger */ char *src; /* textual procedure code, after mangling */ diff --git a/src/pl/plpython/sql/plpython_call.sql b/src/pl/plpython/sql/plpython_call.sql new file mode 100644 index 00000000000..3fb74de5f07 --- /dev/null +++ b/src/pl/plpython/sql/plpython_call.sql @@ -0,0 +1,41 @@ +-- +-- Tests for procedures / CALL syntax +-- + +CREATE PROCEDURE test_proc1() +LANGUAGE plpythonu +AS $$ +pass +$$; + +CALL test_proc1(); + + +-- error: can't return non-None +CREATE PROCEDURE test_proc2() +LANGUAGE plpythonu +AS $$ +return 5 +$$; + +CALL test_proc2(); + + +CREATE TABLE test1 (a int); + +CREATE PROCEDURE test_proc3(x int) +LANGUAGE plpythonu +AS $$ +plpy.execute("INSERT INTO test1 VALUES (%s)" % x) +$$; + +CALL test_proc3(55); + +SELECT * FROM test1; + + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; + +DROP TABLE test1; diff --git a/src/pl/tcl/Makefile b/src/pl/tcl/Makefile index b8971d3cc8f..6a92a9b6aa7 100644 --- a/src/pl/tcl/Makefile +++ b/src/pl/tcl/Makefile @@ -28,7 +28,7 @@ DATA = pltcl.control pltcl--1.0.sql pltcl--unpackaged--1.0.sql \ pltclu.control pltclu--1.0.sql pltclu--unpackaged--1.0.sql REGRESS_OPTS = --dbname=$(PL_TESTDB) --load-extension=pltcl -REGRESS = pltcl_setup pltcl_queries pltcl_start_proc pltcl_subxact pltcl_unicode +REGRESS = pltcl_setup pltcl_queries pltcl_call pltcl_start_proc pltcl_subxact pltcl_unicode # Tcl on win32 ships with import libraries only for Microsoft Visual C++, # which are not compatible with mingw gcc. Therefore we need to build a diff --git a/src/pl/tcl/expected/pltcl_call.out b/src/pl/tcl/expected/pltcl_call.out new file mode 100644 index 00000000000..7221a37ad0b --- /dev/null +++ b/src/pl/tcl/expected/pltcl_call.out @@ -0,0 +1,29 @@ +CREATE PROCEDURE test_proc1() +LANGUAGE pltcl +AS $$ +unset +$$; +CALL test_proc1(); +CREATE PROCEDURE test_proc2() +LANGUAGE pltcl +AS $$ +return 5 +$$; +CALL test_proc2(); +CREATE TABLE test1 (a int); +CREATE PROCEDURE test_proc3(x int) +LANGUAGE pltcl +AS $$ +spi_exec "INSERT INTO test1 VALUES ($1)" +$$; +CALL test_proc3(55); +SELECT * FROM test1; + a +---- + 55 +(1 row) + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; +DROP TABLE test1; diff --git a/src/pl/tcl/pltcl.c b/src/pl/tcl/pltcl.c index 6d97ddc99bd..e0792d93e1c 100644 --- a/src/pl/tcl/pltcl.c +++ b/src/pl/tcl/pltcl.c @@ -146,6 +146,7 @@ typedef struct pltcl_proc_desc Oid result_typid; /* OID of fn's result type */ FmgrInfo result_in_func; /* input function for fn's result type */ Oid result_typioparam; /* param to pass to same */ + bool fn_is_procedure;/* true if this is a procedure */ bool fn_retisset; /* true if function returns a set */ bool fn_retistuple; /* true if function returns composite */ bool fn_retisdomain; /* true if function returns domain */ @@ -968,7 +969,7 @@ pltcl_func_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state, retval = (Datum) 0; fcinfo->isnull = true; } - else if (fcinfo->isnull) + else if (fcinfo->isnull && !prodesc->fn_is_procedure) { retval = InputFunctionCall(&prodesc->result_in_func, NULL, @@ -1026,11 +1027,13 @@ pltcl_func_handler(PG_FUNCTION_ARGS, pltcl_call_state *call_state, call_state); retval = HeapTupleGetDatum(tup); } - else + else if (!prodesc->fn_is_procedure) retval = InputFunctionCall(&prodesc->result_in_func, utf_u2e(Tcl_GetStringResult(interp)), prodesc->result_typioparam, -1); + else + retval = 0; return retval; } @@ -1506,7 +1509,9 @@ compile_pltcl_function(Oid fn_oid, Oid tgreloid, * Get the required information for input conversion of the * return value. ************************************************************/ - if (!is_trigger && !is_event_trigger) + prodesc->fn_is_procedure = (procStruct->prorettype == InvalidOid); + + if (!is_trigger && !is_event_trigger && procStruct->prorettype) { Oid rettype = procStruct->prorettype; @@ -2199,7 +2204,7 @@ pltcl_returnnext(ClientData cdata, Tcl_Interp *interp, tuplestore_puttuple(call_state->tuple_store, tuple); } } - else + else if (!prodesc->fn_is_procedure) { Datum retval; bool isNull = false; diff --git a/src/pl/tcl/sql/pltcl_call.sql b/src/pl/tcl/sql/pltcl_call.sql new file mode 100644 index 00000000000..ef1f540f50c --- /dev/null +++ b/src/pl/tcl/sql/pltcl_call.sql @@ -0,0 +1,36 @@ +CREATE PROCEDURE test_proc1() +LANGUAGE pltcl +AS $$ +unset +$$; + +CALL test_proc1(); + + +CREATE PROCEDURE test_proc2() +LANGUAGE pltcl +AS $$ +return 5 +$$; + +CALL test_proc2(); + + +CREATE TABLE test1 (a int); + +CREATE PROCEDURE test_proc3(x int) +LANGUAGE pltcl +AS $$ +spi_exec "INSERT INTO test1 VALUES ($1)" +$$; + +CALL test_proc3(55); + +SELECT * FROM test1; + + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; + +DROP TABLE test1; diff --git a/src/test/regress/expected/create_procedure.out b/src/test/regress/expected/create_procedure.out new file mode 100644 index 00000000000..5538ef2f2be --- /dev/null +++ b/src/test/regress/expected/create_procedure.out @@ -0,0 +1,92 @@ +CALL nonexistent(); -- error +ERROR: function nonexistent() does not exist +LINE 1: CALL nonexistent(); + ^ +HINT: No function matches the given name and argument types. You might need to add explicit type casts. +CALL random(); -- error +ERROR: random() is not a procedure +LINE 1: CALL random(); + ^ +HINT: To call a function, use SELECT. +CREATE FUNCTION testfunc1(a int) RETURNS int LANGUAGE SQL AS $$ SELECT a $$; +CREATE TABLE cp_test (a int, b text); +CREATE PROCEDURE ptest1(x text) +LANGUAGE SQL +AS $$ +INSERT INTO cp_test VALUES (1, x); +$$; +SELECT ptest1('x'); -- error +ERROR: ptest1(unknown) is a procedure +LINE 1: SELECT ptest1('x'); + ^ +HINT: To call a procedure, use CALL. +CALL ptest1('a'); -- ok +\df ptest1 + List of functions + Schema | Name | Result data type | Argument data types | Type +--------+--------+------------------+---------------------+------ + public | ptest1 | | x text | proc +(1 row) + +SELECT * FROM cp_test ORDER BY a; + a | b +---+--- + 1 | a +(1 row) + +CREATE PROCEDURE ptest2() +LANGUAGE SQL +AS $$ +SELECT 5; +$$; +CALL ptest2(); +-- various error cases +CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; +ERROR: invalid attribute in procedure definition +LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT I... + ^ +CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; +ERROR: invalid attribute in procedure definition +LINE 1: CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT I... + ^ +CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; +ERROR: procedures cannot have OUT parameters +ALTER PROCEDURE ptest1(text) STRICT; +ERROR: invalid attribute in procedure definition +LINE 1: ALTER PROCEDURE ptest1(text) STRICT; + ^ +ALTER FUNCTION ptest1(text) VOLATILE; -- error: not a function +ERROR: ptest1(text) is not a function +ALTER PROCEDURE testfunc1(int) VOLATILE; -- error: not a procedure +ERROR: testfunc1(integer) is not a procedure +ALTER PROCEDURE nonexistent() VOLATILE; +ERROR: procedure nonexistent() does not exist +DROP FUNCTION ptest1(text); -- error: not a function +ERROR: ptest1(text) is not a function +DROP PROCEDURE testfunc1(int); -- error: not a procedure +ERROR: testfunc1(integer) is not a procedure +DROP PROCEDURE nonexistent(); +ERROR: procedure nonexistent() does not exist +-- privileges +CREATE USER regress_user1; +GRANT INSERT ON cp_test TO regress_user1; +REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC; +SET ROLE regress_user1; +CALL ptest1('a'); -- error +ERROR: permission denied for function ptest1 +RESET ROLE; +GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_user1; +SET ROLE regress_user1; +CALL ptest1('a'); -- ok +RESET ROLE; +-- ROUTINE syntax +ALTER ROUTINE testfunc1(int) RENAME TO testfunc1a; +ALTER ROUTINE testfunc1a RENAME TO testfunc1; +ALTER ROUTINE ptest1(text) RENAME TO ptest1a; +ALTER ROUTINE ptest1a RENAME TO ptest1; +DROP ROUTINE testfunc1(int); +-- cleanup +DROP PROCEDURE ptest1; +DROP PROCEDURE ptest2; +DROP TABLE cp_test; +DROP USER regress_user1; diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out index 1fdadbc9ef6..bfd9d54c119 100644 --- a/src/test/regress/expected/object_address.out +++ b/src/test/regress/expected/object_address.out @@ -29,6 +29,7 @@ CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0); CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$; CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig(); CREATE POLICY genpol ON addr_nsp.gentable; +CREATE PROCEDURE addr_nsp.proc(int4) LANGUAGE SQL AS $$ $$; CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw; CREATE USER MAPPING FOR regress_addr_user SERVER "integer"; ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user; @@ -88,7 +89,7 @@ BEGIN ('table'), ('index'), ('sequence'), ('view'), ('materialized view'), ('foreign table'), ('table column'), ('foreign table column'), - ('aggregate'), ('function'), ('type'), ('cast'), + ('aggregate'), ('function'), ('procedure'), ('type'), ('cast'), ('table constraint'), ('domain constraint'), ('conversion'), ('default value'), ('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'), ('text search parser'), ('text search dictionary'), @@ -171,6 +172,12 @@ WARNING: error for function,{addr_nsp,zwei},{}: function addr_nsp.zwei() does n WARNING: error for function,{addr_nsp,zwei},{integer}: function addr_nsp.zwei(integer) does not exist WARNING: error for function,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei WARNING: error for function,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei +WARNING: error for procedure,{eins},{}: procedure eins() does not exist +WARNING: error for procedure,{eins},{integer}: procedure eins(integer) does not exist +WARNING: error for procedure,{addr_nsp,zwei},{}: procedure addr_nsp.zwei() does not exist +WARNING: error for procedure,{addr_nsp,zwei},{integer}: procedure addr_nsp.zwei(integer) does not exist +WARNING: error for procedure,{eins,zwei,drei},{}: cross-database references are not implemented: eins.zwei.drei +WARNING: error for procedure,{eins,zwei,drei},{integer}: cross-database references are not implemented: eins.zwei.drei WARNING: error for type,{eins},{}: type "eins" does not exist WARNING: error for type,{eins},{integer}: type "eins" does not exist WARNING: error for type,{addr_nsp,zwei},{}: name list length must be exactly 1 @@ -371,6 +378,7 @@ WITH objects (type, name, args) AS (VALUES ('foreign table column', '{addr_nsp, genftable, a}', '{}'), ('aggregate', '{addr_nsp, genaggr}', '{int4}'), ('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'), + ('procedure', '{addr_nsp, proc}', '{int4}'), ('type', '{pg_catalog._int4}', '{}'), ('type', '{addr_nsp.gendomain}', '{}'), ('type', '{addr_nsp.gencomptype}', '{}'), @@ -431,6 +439,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, type | addr_nsp | gendomain | addr_nsp.gendomain | t function | pg_catalog | | pg_catalog.pg_identify_object(pg_catalog.oid,pg_catalog.oid,integer) | t aggregate | addr_nsp | | addr_nsp.genaggr(integer) | t + procedure | addr_nsp | | addr_nsp.proc(integer) | t sequence | addr_nsp | gentable_a_seq | addr_nsp.gentable_a_seq | t table | addr_nsp | gentable | addr_nsp.gentable | t table column | addr_nsp | gentable | addr_nsp.gentable.b | t @@ -469,7 +478,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*, subscription | | addr_sub | addr_sub | t publication | | addr_pub | addr_pub | t publication relation | | | gentable in publication addr_pub | t -(46 rows) +(47 rows) --- --- Cleanup resources @@ -480,6 +489,6 @@ NOTICE: drop cascades to 4 other objects DROP PUBLICATION addr_pub; DROP SUBSCRIPTION addr_sub; DROP SCHEMA addr_nsp CASCADE; -NOTICE: drop cascades to 12 other objects +NOTICE: drop cascades to 13 other objects DROP OWNED BY regress_addr_user; DROP USER regress_addr_user; diff --git a/src/test/regress/expected/plpgsql.out b/src/test/regress/expected/plpgsql.out index bb3532676bd..d6e5bc33536 100644 --- a/src/test/regress/expected/plpgsql.out +++ b/src/test/regress/expected/plpgsql.out @@ -6040,3 +6040,44 @@ END; $$ LANGUAGE plpgsql; ERROR: "x" is not a scalar variable LINE 3: GET DIAGNOSTICS x = ROW_COUNT; ^ +-- +-- Procedures +-- +CREATE PROCEDURE test_proc1() +LANGUAGE plpgsql +AS $$ +BEGIN + NULL; +END; +$$; +CALL test_proc1(); +-- error: can't return non-NULL +CREATE PROCEDURE test_proc2() +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN 5; +END; +$$; +CALL test_proc2(); +ERROR: cannot return a value from a procedure +CONTEXT: PL/pgSQL function test_proc2() while casting return value to function's return type +CREATE TABLE proc_test1 (a int); +CREATE PROCEDURE test_proc3(x int) +LANGUAGE plpgsql +AS $$ +BEGIN + INSERT INTO proc_test1 VALUES (x); +END; +$$; +CALL test_proc3(55); +SELECT * FROM proc_test1; + a +---- + 55 +(1 row) + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; +DROP TABLE proc_test1; diff --git a/src/test/regress/expected/polymorphism.out b/src/test/regress/expected/polymorphism.out index 91cfb743b63..66e35a6a5c2 100644 --- a/src/test/regress/expected/polymorphism.out +++ b/src/test/regress/expected/polymorphism.out @@ -915,10 +915,10 @@ select dfunc(); -- verify it lists properly \df dfunc - List of functions - Schema | Name | Result data type | Argument data types | Type ---------+-------+------------------+-----------------------------------------------------------+-------- - public | dfunc | integer | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | normal + List of functions + Schema | Name | Result data type | Argument data types | Type +--------+-------+------------------+-----------------------------------------------------------+------ + public | dfunc | integer | a integer DEFAULT 1, OUT sum integer, b integer DEFAULT 2 | func (1 row) drop function dfunc(int, int); @@ -1083,10 +1083,10 @@ $$ select array_upper($1, 1) $$ language sql; ERROR: cannot remove parameter defaults from existing function HINT: Use DROP FUNCTION dfunc(integer[]) first. \df dfunc - List of functions - Schema | Name | Result data type | Argument data types | Type ---------+-------+------------------+-------------------------------------------------+-------- - public | dfunc | integer | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | normal + List of functions + Schema | Name | Result data type | Argument data types | Type +--------+-------+------------------+-------------------------------------------------+------ + public | dfunc | integer | VARIADIC a integer[] DEFAULT ARRAY[]::integer[] | func (1 row) drop function dfunc(a variadic int[]); diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 771971a095f..e6994f04905 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -651,13 +651,25 @@ GRANT USAGE ON LANGUAGE sql TO regress_user2; -- fail WARNING: no privileges were granted for "sql" CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; -REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC; -GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regress_user2; +CREATE AGGREGATE testagg1(int) (sfunc = int4pl, stype = int4); +CREATE PROCEDURE testproc1(int) AS 'select $1;' LANGUAGE sql; +REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) TO regress_user2; +REVOKE ALL ON FUNCTION testproc1(int) FROM PUBLIC; -- fail, not a function +ERROR: testproc1(integer) is not a function +REVOKE ALL ON PROCEDURE testproc1(int) FROM PUBLIC; +GRANT EXECUTE ON PROCEDURE testproc1(int) TO regress_user2; GRANT USAGE ON FUNCTION testfunc1(int) TO regress_user3; -- semantic error ERROR: invalid privilege type USAGE for function +GRANT USAGE ON FUNCTION testagg1(int) TO regress_user3; -- semantic error +ERROR: invalid privilege type USAGE for function +GRANT USAGE ON PROCEDURE testproc1(int) TO regress_user3; -- semantic error +ERROR: invalid privilege type USAGE for procedure GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regress_user4; GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regress_user4; ERROR: function testfunc_nosuch(integer) does not exist +GRANT ALL PRIVILEGES ON FUNCTION testagg1(int) TO regress_user4; +GRANT ALL PRIVILEGES ON PROCEDURE testproc1(int) TO regress_user4; CREATE FUNCTION testfunc4(boolean) RETURNS text AS 'select col1 from atest2 where col2 = $1;' LANGUAGE sql SECURITY DEFINER; @@ -671,9 +683,20 @@ SELECT testfunc1(5), testfunc2(5); -- ok CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail ERROR: permission denied for language sql +SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok + testagg1 +---------- + 6 +(1 row) + +CALL testproc1(6); -- ok SET SESSION AUTHORIZATION regress_user3; SELECT testfunc1(5); -- fail ERROR: permission denied for function testfunc1 +SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail +ERROR: permission denied for function testagg1 +CALL testproc1(6); -- fail +ERROR: permission denied for function testproc1 SELECT col1 FROM atest2 WHERE col2 = true; -- fail ERROR: permission denied for relation atest2 SELECT testfunc4(true); -- ok @@ -689,8 +712,19 @@ SELECT testfunc1(5); -- ok 10 (1 row) +SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok + testagg1 +---------- + 6 +(1 row) + +CALL testproc1(6); -- ok DROP FUNCTION testfunc1(int); -- fail ERROR: must be owner of function testfunc1 +DROP AGGREGATE testagg1(int); -- fail +ERROR: must be owner of function testagg1 +DROP PROCEDURE testproc1(int); -- fail +ERROR: must be owner of function testproc1 \c - DROP FUNCTION testfunc1(int); -- ok -- restore to sanity @@ -1537,22 +1571,54 @@ SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no SET ROLE regress_user1; CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; +CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); +CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no has_function_privilege ------------------------ f (1 row) -ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTIONS to public; +SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + +SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- no + has_function_privilege +------------------------ + f +(1 row) + +ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public; DROP FUNCTION testns.foo(); CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; +DROP AGGREGATE testns.agg1(int); +CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); +DROP PROCEDURE testns.bar(); +CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- yes has_function_privilege ------------------------ t (1 row) +SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here) + has_function_privilege +------------------------ + t +(1 row) + DROP FUNCTION testns.foo(); +DROP AGGREGATE testns.agg1(int); +DROP PROCEDURE testns.bar(); ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE USAGE ON TYPES FROM public; CREATE DOMAIN testns.testdomain1 AS int; SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- no @@ -1631,12 +1697,26 @@ SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- false (1 row) CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; +CREATE AGGREGATE testns.testagg(int) (sfunc = int4pl, stype = int4); +CREATE PROCEDURE testns.testproc(int) AS 'select 3' LANGUAGE sql; SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default has_function_privilege ------------------------ t (1 row) +SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true by default + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true by default + has_function_privilege +------------------------ + t +(1 row) + REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- false has_function_privilege @@ -1644,9 +1724,47 @@ SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE' f (1 row) +SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- false + has_function_privilege +------------------------ + f +(1 row) + +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- still true, not a function + has_function_privilege +------------------------ + t +(1 row) + +REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC; +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- now false + has_function_privilege +------------------------ + f +(1 row) + +GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC; +SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true + has_function_privilege +------------------------ + t +(1 row) + +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true + has_function_privilege +------------------------ + t +(1 row) + \set VERBOSITY terse \\ -- suppress cascade details DROP SCHEMA testns CASCADE; -NOTICE: drop cascades to 3 other objects +NOTICE: drop cascades to 5 other objects \set VERBOSITY default -- Change owner of the schema & and rename of new schema owner \c - @@ -1729,8 +1847,10 @@ drop table dep_priv_test; -- clean up \c drop sequence x_seq; +DROP AGGREGATE testagg1(int); DROP FUNCTION testfunc2(int); DROP FUNCTION testfunc4(boolean); +DROP PROCEDURE testproc1(int); DROP VIEW atestv0; DROP VIEW atestv1; DROP VIEW atestv2; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 892a214f2f3..e224977791e 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -53,7 +53,7 @@ test: copy copyselect copydml # ---------- # More groups of parallel tests # ---------- -test: create_misc create_operator +test: create_misc create_operator create_procedure # These depend on the above two test: create_index create_view diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 15a1f861a94..9fc5f1a2688 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -63,6 +63,7 @@ test: copyselect test: copydml test: create_misc test: create_operator +test: create_procedure test: create_index test: create_view test: create_aggregate diff --git a/src/test/regress/sql/create_procedure.sql b/src/test/regress/sql/create_procedure.sql new file mode 100644 index 00000000000..f09ba2ad30c --- /dev/null +++ b/src/test/regress/sql/create_procedure.sql @@ -0,0 +1,79 @@ +CALL nonexistent(); -- error +CALL random(); -- error + +CREATE FUNCTION testfunc1(a int) RETURNS int LANGUAGE SQL AS $$ SELECT a $$; + +CREATE TABLE cp_test (a int, b text); + +CREATE PROCEDURE ptest1(x text) +LANGUAGE SQL +AS $$ +INSERT INTO cp_test VALUES (1, x); +$$; + +SELECT ptest1('x'); -- error +CALL ptest1('a'); -- ok + +\df ptest1 + +SELECT * FROM cp_test ORDER BY a; + + +CREATE PROCEDURE ptest2() +LANGUAGE SQL +AS $$ +SELECT 5; +$$; + +CALL ptest2(); + + +-- various error cases + +CREATE PROCEDURE ptestx() LANGUAGE SQL WINDOW AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; +CREATE PROCEDURE ptestx() LANGUAGE SQL STRICT AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; +CREATE PROCEDURE ptestx(OUT a int) LANGUAGE SQL AS $$ INSERT INTO cp_test VALUES (1, 'a') $$; + +ALTER PROCEDURE ptest1(text) STRICT; +ALTER FUNCTION ptest1(text) VOLATILE; -- error: not a function +ALTER PROCEDURE testfunc1(int) VOLATILE; -- error: not a procedure +ALTER PROCEDURE nonexistent() VOLATILE; + +DROP FUNCTION ptest1(text); -- error: not a function +DROP PROCEDURE testfunc1(int); -- error: not a procedure +DROP PROCEDURE nonexistent(); + + +-- privileges + +CREATE USER regress_user1; +GRANT INSERT ON cp_test TO regress_user1; +REVOKE EXECUTE ON PROCEDURE ptest1(text) FROM PUBLIC; +SET ROLE regress_user1; +CALL ptest1('a'); -- error +RESET ROLE; +GRANT EXECUTE ON PROCEDURE ptest1(text) TO regress_user1; +SET ROLE regress_user1; +CALL ptest1('a'); -- ok +RESET ROLE; + + +-- ROUTINE syntax + +ALTER ROUTINE testfunc1(int) RENAME TO testfunc1a; +ALTER ROUTINE testfunc1a RENAME TO testfunc1; + +ALTER ROUTINE ptest1(text) RENAME TO ptest1a; +ALTER ROUTINE ptest1a RENAME TO ptest1; + +DROP ROUTINE testfunc1(int); + + +-- cleanup + +DROP PROCEDURE ptest1; +DROP PROCEDURE ptest2; + +DROP TABLE cp_test; + +DROP USER regress_user1; diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql index 63821b8008b..55faa71edfb 100644 --- a/src/test/regress/sql/object_address.sql +++ b/src/test/regress/sql/object_address.sql @@ -32,6 +32,7 @@ CREATE DOMAIN addr_nsp.gendomain AS int4 CONSTRAINT domconstr CHECK (value > 0); CREATE FUNCTION addr_nsp.trig() RETURNS TRIGGER LANGUAGE plpgsql AS $$ BEGIN END; $$; CREATE TRIGGER t BEFORE INSERT ON addr_nsp.gentable FOR EACH ROW EXECUTE PROCEDURE addr_nsp.trig(); CREATE POLICY genpol ON addr_nsp.gentable; +CREATE PROCEDURE addr_nsp.proc(int4) LANGUAGE SQL AS $$ $$; CREATE SERVER "integer" FOREIGN DATA WRAPPER addr_fdw; CREATE USER MAPPING FOR regress_addr_user SERVER "integer"; ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user IN SCHEMA public GRANT ALL ON TABLES TO regress_addr_user; @@ -81,7 +82,7 @@ BEGIN ('table'), ('index'), ('sequence'), ('view'), ('materialized view'), ('foreign table'), ('table column'), ('foreign table column'), - ('aggregate'), ('function'), ('type'), ('cast'), + ('aggregate'), ('function'), ('procedure'), ('type'), ('cast'), ('table constraint'), ('domain constraint'), ('conversion'), ('default value'), ('operator'), ('operator class'), ('operator family'), ('rule'), ('trigger'), ('text search parser'), ('text search dictionary'), @@ -147,6 +148,7 @@ WITH objects (type, name, args) AS (VALUES ('foreign table column', '{addr_nsp, genftable, a}', '{}'), ('aggregate', '{addr_nsp, genaggr}', '{int4}'), ('function', '{pg_catalog, pg_identify_object}', '{pg_catalog.oid, pg_catalog.oid, int4}'), + ('procedure', '{addr_nsp, proc}', '{int4}'), ('type', '{pg_catalog._int4}', '{}'), ('type', '{addr_nsp.gendomain}', '{}'), ('type', '{addr_nsp.gencomptype}', '{}'), diff --git a/src/test/regress/sql/plpgsql.sql b/src/test/regress/sql/plpgsql.sql index 6620ea61729..1c355132b77 100644 --- a/src/test/regress/sql/plpgsql.sql +++ b/src/test/regress/sql/plpgsql.sql @@ -4820,3 +4820,52 @@ BEGIN GET DIAGNOSTICS x = ROW_COUNT; RETURN; END; $$ LANGUAGE plpgsql; + + +-- +-- Procedures +-- + +CREATE PROCEDURE test_proc1() +LANGUAGE plpgsql +AS $$ +BEGIN + NULL; +END; +$$; + +CALL test_proc1(); + + +-- error: can't return non-NULL +CREATE PROCEDURE test_proc2() +LANGUAGE plpgsql +AS $$ +BEGIN + RETURN 5; +END; +$$; + +CALL test_proc2(); + + +CREATE TABLE proc_test1 (a int); + +CREATE PROCEDURE test_proc3(x int) +LANGUAGE plpgsql +AS $$ +BEGIN + INSERT INTO proc_test1 VALUES (x); +END; +$$; + +CALL test_proc3(55); + +SELECT * FROM proc_test1; + + +DROP PROCEDURE test_proc1; +DROP PROCEDURE test_proc2; +DROP PROCEDURE test_proc3; + +DROP TABLE proc_test1; diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index a900ba2f849..ea8dd028cd0 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -442,12 +442,21 @@ SET SESSION AUTHORIZATION regress_user1; GRANT USAGE ON LANGUAGE sql TO regress_user2; -- fail CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; - -REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC; -GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regress_user2; +CREATE AGGREGATE testagg1(int) (sfunc = int4pl, stype = int4); +CREATE PROCEDURE testproc1(int) AS 'select $1;' LANGUAGE sql; + +REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) FROM PUBLIC; +GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int), testagg1(int) TO regress_user2; +REVOKE ALL ON FUNCTION testproc1(int) FROM PUBLIC; -- fail, not a function +REVOKE ALL ON PROCEDURE testproc1(int) FROM PUBLIC; +GRANT EXECUTE ON PROCEDURE testproc1(int) TO regress_user2; GRANT USAGE ON FUNCTION testfunc1(int) TO regress_user3; -- semantic error +GRANT USAGE ON FUNCTION testagg1(int) TO regress_user3; -- semantic error +GRANT USAGE ON PROCEDURE testproc1(int) TO regress_user3; -- semantic error GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regress_user4; GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regress_user4; +GRANT ALL PRIVILEGES ON FUNCTION testagg1(int) TO regress_user4; +GRANT ALL PRIVILEGES ON PROCEDURE testproc1(int) TO regress_user4; CREATE FUNCTION testfunc4(boolean) RETURNS text AS 'select col1 from atest2 where col2 = $1;' @@ -457,16 +466,24 @@ GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regress_user3; SET SESSION AUTHORIZATION regress_user2; SELECT testfunc1(5), testfunc2(5); -- ok CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail +SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok +CALL testproc1(6); -- ok SET SESSION AUTHORIZATION regress_user3; SELECT testfunc1(5); -- fail +SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail +CALL testproc1(6); -- fail SELECT col1 FROM atest2 WHERE col2 = true; -- fail SELECT testfunc4(true); -- ok SET SESSION AUTHORIZATION regress_user4; SELECT testfunc1(5); -- ok +SELECT testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok +CALL testproc1(6); -- ok DROP FUNCTION testfunc1(int); -- fail +DROP AGGREGATE testagg1(int); -- fail +DROP PROCEDURE testproc1(int); -- fail \c - @@ -931,17 +948,29 @@ SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no SET ROLE regress_user1; CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; +CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); +CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no +SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- no +SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- no -ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTIONS to public; +ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public; DROP FUNCTION testns.foo(); CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; +DROP AGGREGATE testns.agg1(int); +CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); +DROP PROCEDURE testns.bar(); +CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes +SELECT has_function_privilege('regress_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here) DROP FUNCTION testns.foo(); +DROP AGGREGATE testns.agg1(int); +DROP PROCEDURE testns.bar(); ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE USAGE ON TYPES FROM public; @@ -995,12 +1024,28 @@ SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- false CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; +CREATE AGGREGATE testns.testagg(int) (sfunc = int4pl, stype = int4); +CREATE PROCEDURE testns.testproc(int) AS 'select 3' LANGUAGE sql; SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default +SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true by default +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true by default REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- false +SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- false +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- still true, not a function + +REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC; + +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- now false + +GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC; + +SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true +SELECT has_function_privilege('regress_user1', 'testns.testagg(int)', 'EXECUTE'); -- true +SELECT has_function_privilege('regress_user1', 'testns.testproc(int)', 'EXECUTE'); -- true \set VERBOSITY terse \\ -- suppress cascade details DROP SCHEMA testns CASCADE; @@ -1064,8 +1109,10 @@ drop table dep_priv_test; drop sequence x_seq; +DROP AGGREGATE testagg1(int); DROP FUNCTION testfunc2(int); DROP FUNCTION testfunc4(boolean); +DROP PROCEDURE testproc1(int); DROP VIEW atestv0; DROP VIEW atestv1; |