diff options
| author | Peter Eisentraut | 2017-11-30 13:46:13 +0000 |
|---|---|---|
| committer | Peter Eisentraut | 2017-11-30 16:03:20 +0000 |
| commit | e4128ee767df3c8c715eb08f8977647ae49dfb59 (patch) | |
| tree | 6513b824fd69b982057f5fe2742039597ce6cea4 /src | |
| parent | 1761653bbb17447906c812c347b3fe284ce699cf (diff) | |
SQL procedures
This adds a new object type "procedure" that is similar to a function
but does not have a return type and is invoked by the new CALL statement
instead of SELECT or similar. This implementation is aligned with the
SQL standard and compatible with or similar to other SQL implementations.
This commit adds new commands CALL, CREATE/ALTER/DROP PROCEDURE, as well
as ALTER/DROP ROUTINE that can refer to either a function or a
procedure (or an aggregate function, as an extension to SQL). There is
also support for procedures in various utility commands such as COMMENT
and GRANT, as well as support in pg_dump and psql. Support for defining
procedures is available in all the languages supplied by the core
distribution.
While this commit is mainly syntax sugar around existing functionality,
future features will rely on having procedures as a separate object
type.
Reviewed-by: Andrew Dunstan <[email protected]>
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; |