Patch: Allow SQL-language functions to reference parameters by parameter name

diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml
new file mode 100644
index 7064312..cc5b5ef
*** a/doc/src/sgml/xfunc.sgml
--- b/doc/src/sgml/xfunc.sgml
*************** SELECT getname(new_emp());
*** 538,556 ****
  <programlisting>
  CREATE FUNCTION tf1 (acct_no integer, debit numeric) RETURNS numeric AS $$
      UPDATE bank
!         SET balance = balance - $2
!         WHERE accountno = $1
      RETURNING balance;
  $$ LANGUAGE SQL;
  </programlisting>
  
       Here the first parameter has been given the name <literal>acct_no</>,
       and the second parameter the name <literal>debit</>.
!      So far as the SQL function itself is concerned, these names are just
!      decoration; you must still refer to the parameters as <literal>$1</>,
!      <literal>$2</>, etc within the function body.  (Some procedural
!      languages let you use the parameter names instead.)  However,
!      attaching names to the parameters is useful for documentation purposes.
       When a function has many parameters, it is also useful to use the names
       while calling the function, as described in
       <xref linkend="sql-syntax-calling-funcs">.
--- 538,580 ----
  <programlisting>
  CREATE FUNCTION tf1 (acct_no integer, debit numeric) RETURNS numeric AS $$
      UPDATE bank
!         SET balance = balance - debit
!         WHERE accountno = acct_no
      RETURNING balance;
  $$ LANGUAGE SQL;
  </programlisting>
  
       Here the first parameter has been given the name <literal>acct_no</>,
       and the second parameter the name <literal>debit</>.
!      Named parameters can still be referenced as
!      <literal>$<replaceable>n</></>; in this example, the second
!      parameter can be referenced as <literal>$2</>, <literal>debit</>,
!      or <literal>tf1.debit</>.
!     </para>
! 
!     <para>
!      If a parameter is given the same name as a column that is available
!      in the query, the name will refer to the column. To explicitly
!      refer to the parameter, you can qualify its name with the name of
!      the containing function. For example,
! 
! <programlisting>
! CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS numeric AS $$
!     UPDATE bank
!         SET balance = balance - debit
!         WHERE accountno = tf1.accountno
!     RETURNING balance;
! $$ LANGUAGE SQL;
! </programlisting>
! 
!      This time, the first parameter has been given the ambiguous name
!      <literal>accountno</>.
!      Notice that inside the function body, <literal>accountno</> still
!      refers to <literal>bank.accountno</>, so <literal>tf1.accountno</>
!      must be used to refer to the parameter.
!     </para>
! 
!     <para>
       When a function has many parameters, it is also useful to use the names
       while calling the function, as described in
       <xref linkend="sql-syntax-calling-funcs">.

diff --git a/src/backend/executor/functions.c b/src/backend/executor/functions.c
new file mode 100644
index 5642687..fe87990
*** a/src/backend/executor/functions.c
--- b/src/backend/executor/functions.c
***************
*** 23,28 ****
--- 23,29 ----
  #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"
*************** typedef SQLFunctionCache *SQLFunctionCac
*** 115,121 ****
--- 116,124 ----
   */
  typedef struct SQLFunctionParseInfo
  {
+ 	char	   *name;			/* function's name */
  	Oid		   *argtypes;		/* resolved types of input arguments */
+ 	char	  **argnames;		/* names of input arguments */
  	int			nargs;			/* number of input arguments */
  	Oid			collation;		/* function's input collation, if known */
  }	SQLFunctionParseInfo;
*************** typedef struct SQLFunctionParseInfo
*** 123,128 ****
--- 126,133 ----
  
  /* 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_param_ref_num(ParseState *pstate, SQLFunctionParseInfoPtr pinfo, int paramno, int location);
  static List *init_execution_state(List *queryTree_list,
  					 SQLFunctionCachePtr fcache,
  					 bool lazyEvalOK);
*************** prepare_sql_fn_parse_info(HeapTuple proc
*** 162,167 ****
--- 167,173 ----
  	int			nargs;
  
  	pinfo = (SQLFunctionParseInfoPtr) palloc0(sizeof(SQLFunctionParseInfo));
+ 	pinfo->name = NameStr(procedureStruct->proname);
  
  	/* Save the function's input collation */
  	pinfo->collation = inputCollation;
*************** prepare_sql_fn_parse_info(HeapTuple proc
*** 200,205 ****
--- 206,240 ----
  		pinfo->argtypes = argOidVect;
  	}
  
+ 	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);
+ 
+ 		if (n_arg_names < nargs)
+ 			pinfo->argnames = NULL;
+ 	}
+ 	else
+ 	{
+ 		pinfo->argnames = NULL;
+ 	}
+ 
  	return pinfo;
  }
  
*************** prepare_sql_fn_parse_info(HeapTuple proc
*** 209,222 ****
  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_paramref_hook = sql_fn_param_ref;
  	/* no need to use p_coerce_param_hook */
  	pstate->p_ref_hook_state = (void *) pinfo;
  }
  
  /*
   * sql_fn_param_ref		parser callback for ParamRefs ($n symbols)
   */
--- 244,353 ----
  void
  sql_fn_parser_setup(struct ParseState *pstate, SQLFunctionParseInfoPtr pinfo)
  {
  	pstate->p_pre_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;
  }
  
+ static Node *
+ sql_fn_resolve_name(ParseState *pstate, SQLFunctionParseInfoPtr pinfo, const char *paramname, int location)
+ {
+ 	int		i;
+ 	for (i = 0; i < pinfo->nargs; i++)
+ 		if (pinfo->argnames[i] && strcmp(pinfo->argnames[i], paramname) == 0)
+ 			return sql_fn_param_ref_num(pstate, pinfo, i + 1, location);
+ 
+ 	return NULL;
+ }
+ 
+ /*
+  * 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			names;
+ 	Node	   *field1;
+ 	Node	   *subfield = NULL;
+ 	const char *pname;
+ 	Node	   *param;
+ 
+ 	if (var != NULL)
+ 		return NULL;			/* there's a table column, prefer that */
+ 
+ 	/*
+ 	 * The allowed syntaxes are:
+ 	 *
+ 	 * A		A = parameter name
+ 	 * A.B		A = (record-typed) parameter name, B = field reference,
+ 	 *			OR A = function name, B = parameter name
+ 	 * A.B.C	A = function name, B = (record-typed) parameter name,
+ 	 *			C = field reference
+ 	 */
+ 	names = list_length(cref->fields);
+ 
+ 	if (names > 3)
+ 		return NULL;
+ 
+ 	field1 = (Node *) linitial(cref->fields);
+ 	if (names > 1)
+ 		subfield = (Node *) lsecond(cref->fields);
+ 	Assert(IsA(field1, String));
+ 	pname = strVal(field1);
+ 
+ 	if (names == 3)
+ 	{
+ 		/*
+ 		 * Function-qualified reference: if the first name doesn't match
+ 		 * the function, we can fail immediately. Otherwise, discard the
+ 		 * first name, and continue.
+ 		 */
+ 		if (strcmp(pname, pinfo->name) != 0)
+ 			return NULL;
+ 
+ 		Assert(IsA(subfield, String));
+ 		pname = strVal(subfield);
+ 		param = sql_fn_resolve_name(pstate, pinfo, pname, cref->location);
+ 		subfield = (Node *) lthird(cref->fields);
+ 	}
+ 	else
+ 	{
+ 		param = sql_fn_resolve_name(pstate, pinfo, pname, cref->location);
+ 
+ 		if (!param && names == 2 && strcmp(pname, pinfo->name) == 0)
+ 		{
+ 			/*
+ 			 * We have a two-part name, the first part matches the name
+ 			 * of our containing function, and did not match a
+ 			 * parameter; discard the first name, and try again.
+ 			 */
+ 			Assert(IsA(subfield, String));
+ 			pname = strVal(subfield);
+ 			param = sql_fn_resolve_name(pstate, pinfo, pname, cref->location);
+ 			subfield = NULL;
+ 		}
+ 	}
+ 
+ 	if (!param)
+ 		return NULL;
+ 
+ 	if (subfield)
+ 	{
+ 		Assert(IsA(subfield, String));
+ 
+ 		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)
   */
*************** sql_fn_param_ref(ParseState *pstate, Par
*** 225,230 ****
--- 356,371 ----
  {
  	SQLFunctionParseInfoPtr pinfo = (SQLFunctionParseInfoPtr) pstate->p_ref_hook_state;
  	int			paramno = pref->number;
+ 
+ 	return sql_fn_param_ref_num(pstate, pinfo, paramno, pref->location);
+ }
+ 
+ /*
+  * sql_fn_param_ref_num		construct a Param node for the given paramno
+  */
+ static Node *
+ sql_fn_param_ref_num(ParseState *pstate, SQLFunctionParseInfoPtr pinfo, int paramno, int location)
+ {
  	Param	   *param;
  
  	/* Check parameter number is valid */
*************** sql_fn_param_ref(ParseState *pstate, Par
*** 237,243 ****
  	param->paramtype = pinfo->argtypes[paramno - 1];
  	param->paramtypmod = -1;
  	param->paramcollid = get_typcollation(param->paramtype);
! 	param->location = pref->location;
  
  	/*
  	 * If we have a function input collation, allow it to override the
--- 378,384 ----
  	param->paramtype = pinfo->argtypes[paramno - 1];
  	param->paramtypmod = -1;
  	param->paramcollid = get_typcollation(param->paramtype);
! 	param->location = location;
  
  	/*
  	 * If we have a function input collation, allow it to override the
diff --git a/src/test/regress/input/create_function_2.source b/src/test/regress/input/create_function_2.source
new file mode 100644
index 6aed5f0..1b013ae
*** a/src/test/regress/input/create_function_2.source
--- b/src/test/regress/input/create_function_2.source
*************** CREATE FUNCTION hobby_construct(text, te
*** 13,18 ****
--- 13,24 ----
     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'
*************** CREATE FUNCTION equipment(hobbies_r)
*** 25,30 ****
--- 31,67 ----
     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
new file mode 100644
index 7cd26cb..e16dc21
*** a/src/test/regress/input/misc.source
--- b/src/test/regress/input/misc.source
*************** SELECT user_relns() AS user_relns
*** 223,228 ****
--- 223,242 ----
  
  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
new file mode 100644
index 94ab7eb..98e1c29
*** a/src/test/regress/output/create_function_2.source
--- b/src/test/regress/output/create_function_2.source
*************** CREATE FUNCTION hobby_construct(text, te
*** 9,14 ****
--- 9,18 ----
     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'
*************** CREATE FUNCTION equipment(hobbies_r)
*** 19,24 ****
--- 23,52 ----
     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
new file mode 100644
index 2f4d482..979ed33
*** a/src/test/regress/output/misc.source
--- b/src/test/regress/output/misc.source
*************** SELECT name(equipment(hobby_construct(te
*** 693,698 ****
--- 693,743 ----
   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 
  -----------------