Implement SQL-spec RETURNS TABLE syntax for functions.

(Unlike the original submission, this patch treats TABLE output parameters
as being entirely equivalent to OUT parameters -- tgl)

Pavel Stehule

6 files changed, 247 insertions, 17 deletions

diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 0e2452fa0f6..b10a2e8ea69 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -4,7 +4,7 @@
  *
  * Copyright (c) 2003-2008, PostgreSQL Global Development Group
  *
- * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.44 2008/07/16 01:30:21 tgl Exp $
+ * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.45 2008/07/18 03:32:52 tgl Exp $
  */
 
 /*
@@ -1007,6 +1007,7 @@ CREATE VIEW parameters AS
                 WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT'
                 WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT'
                 WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN'
+                WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT'
              END AS character_data) AS parameter_mode,
            CAST('NO' AS character_data) AS is_result,
            CAST('NO' AS character_data) AS as_locator,
diff --git a/src/backend/catalog/pg_proc.c b/src/backend/catalog/pg_proc.c
index 37e7ed4343e..e2513eb7dde 100644
--- a/src/backend/catalog/pg_proc.c
+++ b/src/backend/catalog/pg_proc.c
@@ -8,7 +8,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.152 2008/07/16 16:55:23 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/catalog/pg_proc.c,v 1.153 2008/07/18 03:32:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -242,6 +242,7 @@ ProcedureCreate(const char *procedureName,
 						elog(ERROR, "variadic parameter must be last");
 					break;
 				case PROARGMODE_OUT:
+				case PROARGMODE_TABLE:
 					/* okay */
 					break;
 				case PROARGMODE_VARIADIC:
diff --git a/src/backend/commands/functioncmds.c b/src/backend/commands/functioncmds.c
index d03de8bff17..2b3723d6445 100644
--- a/src/backend/commands/functioncmds.c
+++ b/src/backend/commands/functioncmds.c
@@ -10,7 +10,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/commands/functioncmds.c,v 1.97 2008/07/16 16:55:23 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/commands/functioncmds.c,v 1.98 2008/07/18 03:32:52 tgl Exp $
  *
  * DESCRIPTION
  *	  These routines take the parse tree and pick out the
@@ -228,9 +228,10 @@ examine_parameter_list(List *parameters, Oid languageOid,
 					(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
 					 errmsg("functions cannot accept set arguments")));
 
-		if (fp->mode != FUNC_PARAM_OUT)
+		/* handle input parameters */
+		if (fp->mode != FUNC_PARAM_OUT && fp->mode != FUNC_PARAM_TABLE)
 		{
-			/* only OUT parameters can follow a VARIADIC parameter */
+			/* other input parameters can't follow a VARIADIC parameter */
 			if (varCount > 0)
 				ereport(ERROR,
 						(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
@@ -238,9 +239,10 @@ examine_parameter_list(List *parameters, Oid languageOid,
 			inTypes[inCount++] = toid;
 		}
 
+		/* handle output parameters */
 		if (fp->mode != FUNC_PARAM_IN && fp->mode != FUNC_PARAM_VARIADIC)
 		{
-			if (outCount == 0)	/* save first OUT param's type */
+			if (outCount == 0)	/* save first output param's type */
 				*requiredResultType = toid;
 			outCount++;
 		}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 70bbe940afd..39e52099f43 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -11,7 +11,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.617 2008/07/16 01:30:22 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/parser/gram.y,v 2.618 2008/07/18 03:32:52 tgl Exp $
  *
  * HISTORY
  *	  AUTHOR			DATE			MAJOR EVENT
@@ -112,6 +112,8 @@ static Node *doNegate(Node *n, int location);
 static void doNegateFloat(Value *v);
 static Node *makeAArrayExpr(List *elements);
 static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args);
+static List *mergeTableFuncParameters(List *func_args, List *columns);
+static TypeName *TableFuncTypeName(List *columns);
 
 %}
 
@@ -253,13 +255,13 @@ static Node *makeXmlExpr(XmlExprOp op, char *name, List *named_args, List *args)
 				TableFuncElementList opt_type_modifiers
 				prep_type_clause
 				execute_param_clause using_clause returning_clause
-				enum_val_list
+				enum_val_list table_func_column_list
 
 %type <range>	OptTempTableName
 %type <into>	into_clause create_as_target
 
 %type <defelt>	createfunc_opt_item common_func_opt_item
-%type <fun_param> func_arg
+%type <fun_param> func_arg table_func_column
 %type <fun_param_mode> arg_class
 %type <typnam>	func_return func_type
 
@@ -4119,6 +4121,19 @@ CreateFunctionStmt:
 					$$ = (Node *)n;
 				}
 			| CREATE opt_or_replace FUNCTION func_name func_args
+			  RETURNS TABLE '(' table_func_column_list ')' createfunc_opt_list opt_definition
+				{
+					CreateFunctionStmt *n = makeNode(CreateFunctionStmt);
+					n->replace = $2;
+					n->funcname = $4;
+					n->parameters = mergeTableFuncParameters($5, $9);
+					n->returnType = TableFuncTypeName($9);
+					n->returnType->location = @7;
+					n->options = $11;
+					n->withClause = $12;
+					$$ = (Node *)n;
+				}
+			| CREATE opt_or_replace FUNCTION func_name func_args
 			  createfunc_opt_list opt_definition
 				{
 					CreateFunctionStmt *n = makeNode(CreateFunctionStmt);
@@ -4338,6 +4353,27 @@ opt_definition:
 			| /*EMPTY*/								{ $$ = NIL; }
 		;
 
+table_func_column:	param_name func_type
+				{
+					FunctionParameter *n = makeNode(FunctionParameter);
+					n->name = $1;
+					n->argType = $2;
+					n->mode = FUNC_PARAM_TABLE;
+					$$ = n;
+				}
+		;
+
+table_func_column_list:
+			table_func_column
+				{
+					$$ = list_make1($1);
+				}
+			| table_func_column_list ',' table_func_column
+				{
+					$$ = lappend($1, $3);
+				}
+		;
+
 /*****************************************************************************
  * ALTER FUNCTION
  *
@@ -9678,7 +9714,7 @@ extractArgTypes(List *parameters)
 	{
 		FunctionParameter *p = (FunctionParameter *) lfirst(i);
 
-		if (p->mode != FUNC_PARAM_OUT)		/* keep if IN, INOUT, VARIADIC */
+		if (p->mode != FUNC_PARAM_OUT && p->mode != FUNC_PARAM_TABLE)
 			result = lappend(result, p->argType);
 	}
 	return result;
@@ -9862,6 +9898,51 @@ parser_init(void)
 }
 
 /*
+ * Merge the input and output parameters of a table function.
+ */
+static List *
+mergeTableFuncParameters(List *func_args, List *columns)
+{
+	ListCell   *lc;
+
+	/* Explicit OUT and INOUT parameters shouldn't be used in this syntax */
+	foreach(lc, func_args)
+	{
+		FunctionParameter *p = (FunctionParameter *) lfirst(lc);
+
+		if (p->mode != FUNC_PARAM_IN && p->mode != FUNC_PARAM_VARIADIC)
+			ereport(ERROR,
+					(errcode(ERRCODE_SYNTAX_ERROR),
+					 errmsg("OUT and INOUT arguments aren't allowed in TABLE functions")));
+	}
+
+	return list_concat(func_args, columns);
+}
+
+/*
+ * Determine return type of a TABLE function.  A single result column
+ * returns setof that column's type; otherwise return setof record.
+ */
+static TypeName *
+TableFuncTypeName(List *columns)
+{
+	TypeName *result;
+
+	if (list_length(columns) == 1)
+	{
+		FunctionParameter *p = (FunctionParameter *) linitial(columns);
+
+		result = (TypeName *) copyObject(p->argType);
+	}
+	else
+		result = SystemTypeName("record");
+
+	result->setof = true;
+
+	return result;
+}
+
+/*
  * Must undefine base_yylex before including scan.c, since we want it
  * to create the function base_yylex not filtered_base_yylex.
  */
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index c7f896c524a..b3603c53c16 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -9,7 +9,7 @@
  *
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.277 2008/07/16 16:55:23 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/adt/ruleutils.c,v 1.278 2008/07/18 03:32:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -135,6 +135,8 @@ static char *pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
 							int prettyFlags);
 static char *pg_get_expr_worker(text *expr, Oid relid, char *relname,
 				   int prettyFlags);
+static int print_function_arguments(StringInfo buf, HeapTuple proctup,
+						 bool print_table_args);
 static void make_ruledef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
 			 int prettyFlags);
 static void make_viewdef(StringInfo buf, HeapTuple ruletup, TupleDesc rulettc,
@@ -1396,6 +1398,147 @@ pg_get_serial_sequence(PG_FUNCTION_ARGS)
 
 
 /*
+ * pg_get_function_arguments
+ *		Get a nicely-formatted list of arguments for a function.
+ *		This is everything that would go between the parentheses in
+ *		CREATE FUNCTION.
+ */
+Datum
+pg_get_function_arguments(PG_FUNCTION_ARGS)
+{
+	Oid			funcid = PG_GETARG_OID(0);
+	StringInfoData buf;
+	HeapTuple	proctup;
+
+	initStringInfo(&buf);
+
+	proctup = SearchSysCache(PROCOID,
+							 ObjectIdGetDatum(funcid),
+							 0, 0, 0);
+	if (!HeapTupleIsValid(proctup))
+		elog(ERROR, "cache lookup failed for function %u", funcid);
+
+	(void) print_function_arguments(&buf, proctup, false);
+
+	ReleaseSysCache(proctup);
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
+ * pg_get_function_result
+ *		Get a nicely-formatted version of the result type of a function.
+ *		This is what would appear after RETURNS in CREATE FUNCTION.
+ */
+Datum
+pg_get_function_result(PG_FUNCTION_ARGS)
+{
+	Oid			funcid = PG_GETARG_OID(0);
+	StringInfoData buf;
+	HeapTuple	proctup;
+	Form_pg_proc procform;
+	int			ntabargs = 0;
+
+	initStringInfo(&buf);
+
+	proctup = SearchSysCache(PROCOID,
+							 ObjectIdGetDatum(funcid),
+							 0, 0, 0);
+	if (!HeapTupleIsValid(proctup))
+		elog(ERROR, "cache lookup failed for function %u", funcid);
+	procform = (Form_pg_proc) GETSTRUCT(proctup);
+
+	if (procform->proretset)
+	{
+		/* It might be a table function; try to print the arguments */
+		appendStringInfoString(&buf, "TABLE(");
+		ntabargs = print_function_arguments(&buf, proctup, true);
+		if (ntabargs > 0)
+			appendStringInfoString(&buf, ")");
+		else
+			resetStringInfo(&buf);
+	}
+
+	if (ntabargs == 0)
+	{
+		/* Not a table function, so do the normal thing */
+		if (procform->proretset)
+			appendStringInfoString(&buf, "SETOF ");
+		appendStringInfoString(&buf, format_type_be(procform->prorettype));
+	}
+
+	ReleaseSysCache(proctup);
+
+	PG_RETURN_TEXT_P(string_to_text(buf.data));
+}
+
+/*
+ * Common code for pg_get_function_arguments and pg_get_function_result:
+ * append the desired subset of arguments to buf.  We print only TABLE
+ * arguments when print_table_args is true, and all the others when it's false.
+ * Function return value is the number of arguments printed.
+ */
+static int
+print_function_arguments(StringInfo buf, HeapTuple proctup,
+						 bool print_table_args)
+{
+	int			numargs;
+	Oid		   *argtypes;
+	char	  **argnames;
+	char	   *argmodes;
+	int			argsprinted;
+	int			i;
+
+	numargs = get_func_arg_info(proctup,
+								&argtypes, &argnames, &argmodes);
+
+	argsprinted = 0;
+	for (i = 0; i < numargs; i++)
+	{
+		Oid		argtype = argtypes[i];
+		char   *argname = argnames ? argnames[i] : NULL;
+		char	argmode = argmodes ? argmodes[i] : PROARGMODE_IN;
+		const char *modename;
+
+		if (print_table_args != (argmode == PROARGMODE_TABLE))
+			continue;
+
+		switch (argmode)
+		{
+			case PROARGMODE_IN:
+				modename = "";
+				break;
+			case PROARGMODE_INOUT:
+				modename = "INOUT ";
+				break;
+			case PROARGMODE_OUT:
+				modename = "OUT ";
+				break;
+			case PROARGMODE_VARIADIC:
+				modename = "VARIADIC ";
+				break;
+			case PROARGMODE_TABLE:
+				modename = "";
+				break;
+			default:
+				elog(ERROR, "invalid parameter mode '%c'", argmode);
+				modename = NULL; /* keep compiler quiet */
+				break;
+		}
+		if (argsprinted)
+			appendStringInfoString(buf, ", ");
+		appendStringInfoString(buf, modename);
+		if (argname && argname[0])
+			appendStringInfo(buf, "%s ", argname);
+		appendStringInfoString(buf, format_type_be(argtype));
+		argsprinted++;
+	}
+
+	return argsprinted;
+}
+
+
+/*
  * deparse_expression			- General utility for deparsing expressions
  *
  * calls deparse_expression_pretty with all prettyPrinting disabled
diff --git a/src/backend/utils/fmgr/funcapi.c b/src/backend/utils/fmgr/funcapi.c
index 7cba375ee03..6ff1b90ffcd 100644
--- a/src/backend/utils/fmgr/funcapi.c
+++ b/src/backend/utils/fmgr/funcapi.c
@@ -7,7 +7,7 @@
  * Copyright (c) 2002-2008, PostgreSQL Global Development Group
  *
  * IDENTIFICATION
- *	  $PostgreSQL: pgsql/src/backend/utils/fmgr/funcapi.c,v 1.40 2008/07/16 01:30:22 tgl Exp $
+ *	  $PostgreSQL: pgsql/src/backend/utils/fmgr/funcapi.c,v 1.41 2008/07/18 03:32:52 tgl Exp $
  *
  *-------------------------------------------------------------------------
  */
@@ -550,7 +550,7 @@ resolve_polymorphic_argtypes(int numargs, Oid *argtypes, char *argmodes,
 			case ANYELEMENTOID:
 			case ANYNONARRAYOID:
 			case ANYENUMOID:
-				if (argmode == PROARGMODE_OUT)
+				if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
 					have_anyelement_result = true;
 				else
 				{
@@ -565,7 +565,7 @@ resolve_polymorphic_argtypes(int numargs, Oid *argtypes, char *argmodes,
 				}
 				break;
 			case ANYARRAYOID:
-				if (argmode == PROARGMODE_OUT)
+				if (argmode == PROARGMODE_OUT || argmode == PROARGMODE_TABLE)
 					have_anyarray_result = true;
 				else
 				{
@@ -582,7 +582,7 @@ resolve_polymorphic_argtypes(int numargs, Oid *argtypes, char *argmodes,
 			default:
 				break;
 		}
-		if (argmode != PROARGMODE_OUT)
+		if (argmode != PROARGMODE_OUT && argmode != PROARGMODE_TABLE)
 			inargno++;
 	}
 
@@ -848,7 +848,8 @@ get_func_result_name(Oid functionId)
 				argmodes[i] == PROARGMODE_VARIADIC)
 				continue;
 			Assert(argmodes[i] == PROARGMODE_OUT ||
-				   argmodes[i] == PROARGMODE_INOUT);
+				   argmodes[i] == PROARGMODE_INOUT ||
+				   argmodes[i] == PROARGMODE_TABLE);
 			if (++numoutargs > 1)
 			{
 				/* multiple out args, so forget it */
@@ -999,7 +1000,8 @@ build_function_result_tupdesc_d(Datum proallargtypes,
 			argmodes[i] == PROARGMODE_VARIADIC)
 			continue;
 		Assert(argmodes[i] == PROARGMODE_OUT ||
-			   argmodes[i] == PROARGMODE_INOUT);
+			   argmodes[i] == PROARGMODE_INOUT ||
+			   argmodes[i] == PROARGMODE_TABLE);
 		outargtypes[numoutargs] = argtypes[i];
 		if (argnames)
 			pname = TextDatumGetCString(argnames[i]);