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 <andrew.dunstan@2ndquadrant.com>

8 files changed, 31 insertions, 7 deletions

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);