Allow SQL-language functions to reference parameters by name.

Matthew Draper, reviewed by Hitoshi Harada

5 files changed, 315 insertions, 5 deletions

diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
index 56426871b76..61f462254ff 100644
--- a/src/backend/executor/functions.c
+++ b/src/backend/executor/functions.c
@@ -23,6 +23,7 @@
 #include "nodes/makefuncs.h"
 #include "nodes/nodeFuncs.h"
 #include "parser/parse_coerce.h"
+#include "parser/parse_func.h"
 #include "tcop/utility.h"
 #include "utils/builtins.h"
 #include "utils/datum.h"
@@ -115,14 +116,23 @@ typedef SQLFunctionCache *SQLFunctionCachePtr;
  */
 typedef struct SQLFunctionParseInfo
 {
-	Oid		   *argtypes;		/* resolved types of input arguments */
+	char	   *fname;			/* function's name */
 	int			nargs;			/* number of input arguments */
+	Oid		   *argtypes;		/* resolved types of input arguments */
+	char	  **argnames;		/* names of input arguments; NULL if none */
+	/* Note that argnames[i] can be NULL, if some args are unnamed */
 	Oid			collation;		/* function's input collation, if known */
 }	SQLFunctionParseInfo;
 
 
 /* non-export function prototypes */
 static Node *sql_fn_param_ref(ParseState *pstate, ParamRef *pref);
+static Node *sql_fn_post_column_ref(ParseState *pstate,
+									ColumnRef *cref, Node *var);
+static Node *sql_fn_make_param(SQLFunctionParseInfoPtr pinfo,
+							   int paramno, int location);
+static Node *sql_fn_resolve_param_name(SQLFunctionParseInfoPtr pinfo,
+									   const char *paramname, int location);
 static List *init_execution_state(List *queryTree_list,
 					 SQLFunctionCachePtr fcache,
 					 bool lazyEvalOK);
@@ -163,6 +173,9 @@ prepare_sql_fn_parse_info(HeapTuple procedureTuple,
 
 	pinfo = (SQLFunctionParseInfoPtr) palloc0(sizeof(SQLFunctionParseInfo));
 
+	/* Function's name (only) can be used to qualify argument names */
+	pinfo->fname = pstrdup(NameStr(procedureStruct->proname));
+
 	/* Save the function's input collation */
 	pinfo->collation = inputCollation;
 
@@ -200,6 +213,38 @@ prepare_sql_fn_parse_info(HeapTuple procedureTuple,
 		pinfo->argtypes = argOidVect;
 	}
 
+	/*
+	 * Collect names of arguments, too, if any
+	 */
+	if (nargs > 0)
+	{
+		Datum		proargnames;
+		Datum		proargmodes;
+		int			n_arg_names;
+		bool		isNull;
+
+		proargnames = SysCacheGetAttr(PROCNAMEARGSNSP, procedureTuple,
+									  Anum_pg_proc_proargnames,
+									  &isNull);
+		if (isNull)
+			proargnames = PointerGetDatum(NULL);	/* just to be sure */
+
+		proargmodes = SysCacheGetAttr(PROCNAMEARGSNSP, procedureTuple,
+									  Anum_pg_proc_proargmodes,
+									  &isNull);
+		if (isNull)
+			proargmodes = PointerGetDatum(NULL);	/* just to be sure */
+
+		n_arg_names = get_func_input_arg_names(proargnames, proargmodes,
+											   &pinfo->argnames);
+
+		/* Paranoia: ignore the result if too few array entries */
+		if (n_arg_names < nargs)
+			pinfo->argnames = NULL;
+	}
+	else
+		pinfo->argnames = NULL;
+
 	return pinfo;
 }
 
@@ -209,15 +254,122 @@ prepare_sql_fn_parse_info(HeapTuple procedureTuple,
 void
 sql_fn_parser_setup(struct ParseState *pstate, SQLFunctionParseInfoPtr pinfo)
 {
-	/* Later we might use these hooks to support parameter names */
 	pstate->p_pre_columnref_hook = NULL;
-	pstate->p_post_columnref_hook = NULL;
+	pstate->p_post_columnref_hook = sql_fn_post_column_ref;
 	pstate->p_paramref_hook = sql_fn_param_ref;
 	/* no need to use p_coerce_param_hook */
 	pstate->p_ref_hook_state = (void *) pinfo;
 }
 
 /*
+ * sql_fn_post_column_ref		parser callback for ColumnRefs
+ */
+static Node *
+sql_fn_post_column_ref(ParseState *pstate, ColumnRef *cref, Node *var)
+{
+	SQLFunctionParseInfoPtr pinfo = (SQLFunctionParseInfoPtr) pstate->p_ref_hook_state;
+	int			nnames;
+	Node	   *field1;
+	Node	   *subfield = NULL;
+	const char *name1;
+	const char *name2 = NULL;
+	Node	   *param;
+
+	/*
+	 * Never override a table-column reference.  This corresponds to
+	 * considering the parameter names to appear in a scope outside the
+	 * individual SQL commands, which is what we want.
+	 */
+	if (var != NULL)
+		return NULL;
+
+	/*----------
+	 * The allowed syntaxes are:
+	 *
+	 * A		A = parameter name
+	 * A.B		A = function name, B = parameter name
+	 *			OR: A = record-typed parameter name, B = field name
+	 *			(the first possibility takes precedence)
+	 * A.B.C	A = function name, B = record-typed parameter name,
+	 *			C = field name
+	 *----------
+	 */
+	nnames = list_length(cref->fields);
+
+	if (nnames > 3)
+		return NULL;
+
+	field1 = (Node *) linitial(cref->fields);
+	Assert(IsA(field1, String));
+	name1 = strVal(field1);
+	if (nnames > 1)
+	{
+		subfield = (Node *) lsecond(cref->fields);
+		Assert(IsA(subfield, String));
+		name2 = strVal(subfield);
+	}
+
+	if (nnames == 3)
+	{
+		/*
+		 * Three-part name: if the first part doesn't match the function name,
+		 * we can fail immediately. Otherwise, look up the second part, and
+		 * take the third part to be a field reference.
+		 */
+		if (strcmp(name1, pinfo->fname) != 0)
+			return NULL;
+
+		param = sql_fn_resolve_param_name(pinfo, name2, cref->location);
+
+		subfield = (Node *) lthird(cref->fields);
+		Assert(IsA(subfield, String));
+	}
+	else if (nnames == 2 && strcmp(name1, pinfo->fname) == 0)
+	{
+		/*
+		 * Two-part name with first part matching function name: first see if
+		 * second part matches any parameter name.
+		 */
+		param = sql_fn_resolve_param_name(pinfo, name2, cref->location);
+
+		if (param)
+		{
+			/* Yes, so this is a parameter reference, no subfield */
+			subfield = NULL;
+		}
+		else
+		{
+			/* No, so try to match as parameter name and subfield */
+			param = sql_fn_resolve_param_name(pinfo, name1, cref->location);
+		}
+	}
+	else
+	{
+		/* Single name, or parameter name followed by subfield */
+		param = sql_fn_resolve_param_name(pinfo, name1, cref->location);
+	}
+
+	if (!param)
+		return NULL;			/* No match */
+
+	if (subfield)
+	{
+		/*
+		 * Must be a reference to a field of a composite parameter; otherwise
+		 * ParseFuncOrColumn will return NULL, and we'll fail back at the
+		 * caller.
+		 */
+		param = ParseFuncOrColumn(pstate,
+								  list_make1(subfield),
+								  list_make1(param),
+								  NIL, false, false, false,
+								  NULL, true, cref->location);
+	}
+
+	return param;
+}
+
+/*
  * sql_fn_param_ref		parser callback for ParamRefs ($n symbols)
  */
 static Node *
@@ -225,19 +377,30 @@ sql_fn_param_ref(ParseState *pstate, ParamRef *pref)
 {
 	SQLFunctionParseInfoPtr pinfo = (SQLFunctionParseInfoPtr) pstate->p_ref_hook_state;
 	int			paramno = pref->number;
-	Param	   *param;
 
 	/* Check parameter number is valid */
 	if (paramno <= 0 || paramno > pinfo->nargs)
 		return NULL;			/* unknown parameter number */
 
+	return sql_fn_make_param(pinfo, paramno, pref->location);
+}
+
+/*
+ * sql_fn_make_param		construct a Param node for the given paramno
+ */
+static Node *
+sql_fn_make_param(SQLFunctionParseInfoPtr pinfo,
+				  int paramno, int location)
+{
+	Param	   *param;
+
 	param = makeNode(Param);
 	param->paramkind = PARAM_EXTERN;
 	param->paramid = paramno;
 	param->paramtype = pinfo->argtypes[paramno - 1];
 	param->paramtypmod = -1;
 	param->paramcollid = get_typcollation(param->paramtype);
-	param->location = pref->location;
+	param->location = location;
 
 	/*
 	 * If we have a function input collation, allow it to override the
@@ -251,6 +414,29 @@ sql_fn_param_ref(ParseState *pstate, ParamRef *pref)
 }
 
 /*
+ * Search for a function parameter of the given name; if there is one,
+ * construct and return a Param node for it.  If not, return NULL.
+ * Helper function for sql_fn_post_column_ref.
+ */
+static Node *
+sql_fn_resolve_param_name(SQLFunctionParseInfoPtr pinfo,
+						  const char *paramname, int location)
+{
+	int		i;
+
+	if (pinfo->argnames == NULL)
+		return NULL;
+
+	for (i = 0; i < pinfo->nargs; i++)
+	{
+		if (pinfo->argnames[i] && strcmp(pinfo->argnames[i], paramname) == 0)
+			return sql_fn_make_param(pinfo, i + 1, location);
+	}
+
+	return NULL;
+}
+
+/*
  * Set up the per-query execution_state records for a SQL function.
  *
  * The input is a List of Lists of parsed and rewritten, but not planned,
diff --git a/src/test/regress/input/create_function_2.source b/src/test/regress/input/create_function_2.source
index 6aed5f008b9..1b013aedcbd 100644
--- a/src/test/regress/input/create_function_2.source
+++ b/src/test/regress/input/create_function_2.source
@@ -13,6 +13,12 @@ CREATE FUNCTION hobby_construct(text, text)
    LANGUAGE SQL;
 
 
+CREATE FUNCTION hobby_construct_named(name text, hobby text)
+   RETURNS hobbies_r
+   AS 'select name, hobby'
+   LANGUAGE SQL;
+
+
 CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
    RETURNS hobbies_r.person%TYPE
    AS 'select person from hobbies_r where name = $1'
@@ -25,6 +31,37 @@ CREATE FUNCTION equipment(hobbies_r)
    LANGUAGE SQL;
 
 
+CREATE FUNCTION equipment_named(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = hobby.name'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_2a(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby'
+   LANGUAGE SQL;
+
+CREATE FUNCTION equipment_named_ambiguous_2b(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby'
+   LANGUAGE SQL;
+
+
 CREATE FUNCTION user_relns()
    RETURNS setof name
    AS 'select relname
diff --git a/src/test/regress/input/misc.source b/src/test/regress/input/misc.source
index 7cd26cb192d..e16dc21f407 100644
--- a/src/test/regress/input/misc.source
+++ b/src/test/regress/input/misc.source
@@ -223,6 +223,20 @@ SELECT user_relns() AS user_relns
 
 SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
 
+SELECT name(equipment(hobby_construct_named(text 'skywalking', text 'mer')));
+
+SELECT name(equipment_named(hobby_construct_named(text 'skywalking', text 'mer')));
+
+SELECT name(equipment_named_ambiguous_1a(hobby_construct_named(text 'skywalking', text 'mer')));
+
+SELECT name(equipment_named_ambiguous_1b(hobby_construct_named(text 'skywalking', text 'mer')));
+
+SELECT name(equipment_named_ambiguous_1c(hobby_construct_named(text 'skywalking', text 'mer')));
+
+SELECT name(equipment_named_ambiguous_2a(text 'skywalking'));
+
+SELECT name(equipment_named_ambiguous_2b(text 'skywalking'));
+
 SELECT hobbies_by_name('basketball');
 
 SELECT name, overpaid(emp.*) FROM emp;
diff --git a/src/test/regress/output/create_function_2.source b/src/test/regress/output/create_function_2.source
index 94ab7eba566..98e1c297337 100644
--- a/src/test/regress/output/create_function_2.source
+++ b/src/test/regress/output/create_function_2.source
@@ -9,6 +9,10 @@ CREATE FUNCTION hobby_construct(text, text)
    RETURNS hobbies_r
    AS 'select $1 as name, $2 as hobby'
    LANGUAGE SQL;
+CREATE FUNCTION hobby_construct_named(name text, hobby text)
+   RETURNS hobbies_r
+   AS 'select name, hobby'
+   LANGUAGE SQL;
 CREATE FUNCTION hobbies_by_name(hobbies_r.name%TYPE)
    RETURNS hobbies_r.person%TYPE
    AS 'select person from hobbies_r where name = $1'
@@ -19,6 +23,30 @@ CREATE FUNCTION equipment(hobbies_r)
    RETURNS setof equipment_r
    AS 'select * from equipment_r where hobby = $1.name'
    LANGUAGE SQL;
+CREATE FUNCTION equipment_named(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = equipment_named.hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1a(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_1a.hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1b(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_1c(hobby hobbies_r)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = hobby.name'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_2a(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where hobby = equipment_named_ambiguous_2a.hobby'
+   LANGUAGE SQL;
+CREATE FUNCTION equipment_named_ambiguous_2b(hobby text)
+   RETURNS setof equipment_r
+   AS 'select * from equipment_r where equipment_r.hobby = hobby'
+   LANGUAGE SQL;
 CREATE FUNCTION user_relns()
    RETURNS setof name
    AS 'select relname
diff --git a/src/test/regress/output/misc.source b/src/test/regress/output/misc.source
index 2f4d482cbe5..979ed337b29 100644
--- a/src/test/regress/output/misc.source
+++ b/src/test/regress/output/misc.source
@@ -693,6 +693,51 @@ SELECT name(equipment(hobby_construct(text 'skywalking', text 'mer')));
  guts
 (1 row)
 
+SELECT name(equipment(hobby_construct_named(text 'skywalking', text 'mer')));
+ name 
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named(hobby_construct_named(text 'skywalking', text 'mer')));
+ name 
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1a(hobby_construct_named(text 'skywalking', text 'mer')));
+ name 
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1b(hobby_construct_named(text 'skywalking', text 'mer')));
+ name 
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_1c(hobby_construct_named(text 'skywalking', text 'mer')));
+ name 
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_2a(text 'skywalking'));
+ name 
+------
+ guts
+(1 row)
+
+SELECT name(equipment_named_ambiguous_2b(text 'skywalking'));
+     name      
+---------------
+ advil
+ peet's coffee
+ hightops
+ guts
+(4 rows)
+
 SELECT hobbies_by_name('basketball');
  hobbies_by_name 
 -----------------