diff options
| -rw-r--r-- | doc/src/sgml/xfunc.sgml | 158 | ||||
| -rw-r--r-- | src/backend/executor/functions.c | 196 | ||||
| -rw-r--r-- | src/test/regress/input/create_function_2.source | 37 | ||||
| -rw-r--r-- | src/test/regress/input/misc.source | 14 | ||||
| -rw-r--r-- | src/test/regress/output/create_function_2.source | 28 | ||||
| -rw-r--r-- | src/test/regress/output/misc.source | 45 |
6 files changed, 411 insertions, 67 deletions
diff --git a/doc/src/sgml/xfunc.sgml b/doc/src/sgml/xfunc.sgml index 70643122046..d81d63f922f 100644 --- a/doc/src/sgml/xfunc.sgml +++ b/doc/src/sgml/xfunc.sgml @@ -154,14 +154,50 @@ SELECT clean_emp(); the function (see <xref linkend="sql-syntax-strings">). </para> - <para> - Arguments to the SQL function are referenced in the function - body using the syntax <literal>$<replaceable>n</></>: <literal>$1</> - refers to the first argument, <literal>$2</> to the second, and so on. - If an argument is of a composite type, then the dot notation, - e.g., <literal>$1.name</literal>, can be used to access attributes - of the argument. The arguments can only be used as data values, - not as identifiers. Thus for example this is reasonable: + <sect2 id="xfunc-sql-function-arguments"> + <title>Arguments for <acronym>SQL</acronym> Functions</title> + + <indexterm> + <primary>function</primary> + <secondary>named argument</secondary> + </indexterm> + + <para> + Arguments of a SQL function can be referenced in the function + body using either names or numbers. Examples of both methods appear + below. + </para> + + <para> + To use a name, declare the function argument as having a name, and + then just write that name in the function body. If the argument name + is the same as any column name in the current SQL command within the + function, the column name will take precedence. To override this, + qualify the argument name with the name of the function itself, that is + <literal><replaceable>function_name</>.<replaceable>argument_name</></literal>. + (If this would conflict with a qualified column name, again the column + name wins. You can avoid the ambiguity by choosing a different alias for + the table within the SQL command.) + </para> + + <para> + In the older numeric approach, arguments are referenced using the syntax + <literal>$<replaceable>n</></>: <literal>$1</> refers to the first input + argument, <literal>$2</> to the second, and so on. This will work + whether or not the particular argument was declared with a name. + </para> + + <para> + If an argument is of a composite type, then the dot notation, + e.g., <literal>argname.fieldname</literal> or + <literal>$1.fieldname</literal>, can be used to access attributes of the + argument. Again, you might need to qualify the argument's name with the + function name to make the form with an argument name unambiguous. + </para> + + <para> + SQL function arguments can only be used as data values, + not as identifiers. Thus for example this is reasonable: <programlisting> INSERT INTO mytable VALUES ($1); </programlisting> @@ -169,7 +205,16 @@ but this will not work: <programlisting> INSERT INTO $1 VALUES (42); </programlisting> - </para> + </para> + + <note> + <para> + The ability to use names to reference SQL function arguments was added + in <productname>PostgreSQL</productname> 9.2. Functions to be used in + older servers must use the <literal>$<replaceable>n</></> notation. + </para> + </note> + </sect2> <sect2 id="xfunc-sql-base-functions"> <title><acronym>SQL</acronym> Functions on Base Types</title> @@ -205,9 +250,24 @@ SELECT one(); <para> It is almost as easy to define <acronym>SQL</acronym> functions - that take base types as arguments. In the example below, notice - how we refer to the arguments within the function as <literal>$1</> - and <literal>$2</>. + that take base types as arguments: + +<screen> +CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$ + SELECT x + y; +$$ LANGUAGE SQL; + +SELECT add_em(1, 2) AS answer; + + answer +-------- + 3 +</screen> + </para> + + <para> + Alternatively, we could dispense with names for the arguments and + use numbers: <screen> CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$ @@ -227,10 +287,10 @@ SELECT add_em(1, 2) AS answer; bank account: <programlisting> -CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$ +CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank - SET balance = balance - $2 - WHERE accountno = $1; + SET balance = balance - debit + WHERE accountno = tf1.accountno; SELECT 1; $$ LANGUAGE SQL; </programlisting> @@ -244,16 +304,25 @@ SELECT tf1(17, 100.0); </para> <para> + In this example, we chose the name <literal>accountno</> for the first + argument, but this is the same as the name of a column in the + <literal>bank</> table. Within the <command>UPDATE</> command, + <literal>accountno</> refers to the column <literal>bank.accountno</>, + so <literal>tf1.accountno</> must be used to refer to the argument. + We could of course avoid this by using a different name for the argument. + </para> + + <para> In practice one would probably like a more useful result from the function than a constant 1, so a more likely definition is: <programlisting> -CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$ +CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank - SET balance = balance - $2 - WHERE accountno = $1; - SELECT balance FROM bank WHERE accountno = $1; + SET balance = balance - debit + WHERE accountno = tf1.accountno; + SELECT balance FROM bank WHERE accountno = tf1.accountno; $$ LANGUAGE SQL; </programlisting> @@ -261,10 +330,10 @@ $$ LANGUAGE SQL; The same thing could be done in one command using <literal>RETURNING</>: <programlisting> -CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$ +CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$ UPDATE bank - SET balance = balance - $2 - WHERE accountno = $1 + SET balance = balance - debit + WHERE accountno = tf1.accountno RETURNING balance; $$ LANGUAGE SQL; </programlisting> @@ -275,11 +344,9 @@ $$ LANGUAGE SQL; <title><acronym>SQL</acronym> Functions on Composite Types</title> <para> - When writing functions with arguments of composite - types, we must not only specify which - argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but - also the desired attribute (field) of that argument. For example, - suppose that + When writing functions with arguments of composite types, we must not + only specify which argument we want but also the desired attribute + (field) of that argument. For example, suppose that <type>emp</type> is a table containing employee data, and therefore also the name of the composite type of each row of the table. Here is a function <function>double_salary</function> that computes what someone's @@ -524,39 +591,6 @@ SELECT getname(new_emp()); </para> </sect2> - <sect2 id="xfunc-named-parameters"> - <title><acronym>SQL</> Functions with Parameter Names</title> - - <indexterm> - <primary>function</primary> - <secondary>named parameter</secondary> - </indexterm> - - <para> - It is possible to attach names to a function's parameters, for example - -<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">. - </para> - </sect2> - <sect2 id="xfunc-output-parameters"> <title><acronym>SQL</> Functions with Output Parameters</title> @@ -571,7 +605,7 @@ $$ LANGUAGE SQL; <screen> CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int) -AS 'SELECT $1 + $2' +AS 'SELECT x + y' LANGUAGE SQL; SELECT add_em(3,7); @@ -588,7 +622,7 @@ SELECT add_em(3,7); <screen> CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int) -AS 'SELECT $1 + $2, $1 * $2' +AS 'SELECT x + y, x * y' LANGUAGE SQL; SELECT * FROM sum_n_product(11,42); 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 ----------------- |