Skip to content

Commit 9bff078

Browse files
tglsfdctglsfdc
committed
Allow SQL-language functions to reference parameters by name.
Matthew Draper, reviewed by Hitoshi Harada
1 parent 342b83f commit 9bff078

File tree

6 files changed

+411
-67
lines changed

6 files changed

+411
-67
lines changed

‎doc/src/sgml/xfunc.sgml

Lines changed: 96 additions & 62 deletions
Original file line numberDiff line numberDiff line change
@@ -154,22 +154,67 @@ SELECT clean_emp();
154154
the function (see <xref linkend="sql-syntax-strings">).
155155
</para>
156156

157-
<para>
158-
Arguments to the SQL function are referenced in the function
159-
body using the syntax <literal>$<replaceable>n</></>: <literal>$1</>
160-
refers to the first argument, <literal>$2</> to the second, and so on.
161-
If an argument is of a composite type, then the dot notation,
162-
e.g., <literal>$1.name</literal>, can be used to access attributes
163-
of the argument. The arguments can only be used as data values,
164-
not as identifiers. Thus for example this is reasonable:
157+
<sect2 id="xfunc-sql-function-arguments">
158+
<title>Arguments for <acronym>SQL</acronym> Functions</title>
159+
160+
<indexterm>
161+
<primary>function</primary>
162+
<secondary>named argument</secondary>
163+
</indexterm>
164+
165+
<para>
166+
Arguments of a SQL function can be referenced in the function
167+
body using either names or numbers. Examples of both methods appear
168+
below.
169+
</para>
170+
171+
<para>
172+
To use a name, declare the function argument as having a name, and
173+
then just write that name in the function body. If the argument name
174+
is the same as any column name in the current SQL command within the
175+
function, the column name will take precedence. To override this,
176+
qualify the argument name with the name of the function itself, that is
177+
<literal><replaceable>function_name</>.<replaceable>argument_name</></literal>.
178+
(If this would conflict with a qualified column name, again the column
179+
name wins. You can avoid the ambiguity by choosing a different alias for
180+
the table within the SQL command.)
181+
</para>
182+
183+
<para>
184+
In the older numeric approach, arguments are referenced using the syntax
185+
<literal>$<replaceable>n</></>: <literal>$1</> refers to the first input
186+
argument, <literal>$2</> to the second, and so on. This will work
187+
whether or not the particular argument was declared with a name.
188+
</para>
189+
190+
<para>
191+
If an argument is of a composite type, then the dot notation,
192+
e.g., <literal>argname.fieldname</literal> or
193+
<literal>$1.fieldname</literal>, can be used to access attributes of the
194+
argument. Again, you might need to qualify the argument's name with the
195+
function name to make the form with an argument name unambiguous.
196+
</para>
197+
198+
<para>
199+
SQL function arguments can only be used as data values,
200+
not as identifiers. Thus for example this is reasonable:
165201
<programlisting>
166202
INSERT INTO mytable VALUES ($1);
167203
</programlisting>
168204
but this will not work:
169205
<programlisting>
170206
INSERT INTO $1 VALUES (42);
171207
</programlisting>
172-
</para>
208+
</para>
209+
210+
<note>
211+
<para>
212+
The ability to use names to reference SQL function arguments was added
213+
in <productname>PostgreSQL</productname> 9.2. Functions to be used in
214+
older servers must use the <literal>$<replaceable>n</></> notation.
215+
</para>
216+
</note>
217+
</sect2>
173218

174219
<sect2 id="xfunc-sql-base-functions">
175220
<title><acronym>SQL</acronym> Functions on Base Types</title>
@@ -205,9 +250,24 @@ SELECT one();
205250

206251
<para>
207252
It is almost as easy to define <acronym>SQL</acronym> functions
208-
that take base types as arguments. In the example below, notice
209-
how we refer to the arguments within the function as <literal>$1</>
210-
and <literal>$2</>.
253+
that take base types as arguments:
254+
255+
<screen>
256+
CREATE FUNCTION add_em(x integer, y integer) RETURNS integer AS $$
257+
SELECT x + y;
258+
$$ LANGUAGE SQL;
259+
260+
SELECT add_em(1, 2) AS answer;
261+
262+
answer
263+
--------
264+
3
265+
</screen>
266+
</para>
267+
268+
<para>
269+
Alternatively, we could dispense with names for the arguments and
270+
use numbers:
211271

212272
<screen>
213273
CREATE FUNCTION add_em(integer, integer) RETURNS integer AS $$
@@ -227,10 +287,10 @@ SELECT add_em(1, 2) AS answer;
227287
bank account:
228288

229289
<programlisting>
230-
CREATE FUNCTION tf1 (integer, numeric) RETURNS integer AS $$
290+
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$
231291
UPDATE bank
232-
SET balance = balance - $2
233-
WHERE accountno = $1;
292+
SET balance = balance - debit
293+
WHERE accountno = tf1.accountno;
234294
SELECT 1;
235295
$$ LANGUAGE SQL;
236296
</programlisting>
@@ -243,28 +303,37 @@ SELECT tf1(17, 100.0);
243303
</programlisting>
244304
</para>
245305

306+
<para>
307+
In this example, we chose the name <literal>accountno</> for the first
308+
argument, but this is the same as the name of a column in the
309+
<literal>bank</> table. Within the <command>UPDATE</> command,
310+
<literal>accountno</> refers to the column <literal>bank.accountno</>,
311+
so <literal>tf1.accountno</> must be used to refer to the argument.
312+
We could of course avoid this by using a different name for the argument.
313+
</para>
314+
246315
<para>
247316
In practice one would probably like a more useful result from the
248317
function than a constant 1, so a more likely definition
249318
is:
250319

251320
<programlisting>
252-
CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
321+
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$
253322
UPDATE bank
254-
SET balance = balance - $2
255-
WHERE accountno = $1;
256-
SELECT balance FROM bank WHERE accountno = $1;
323+
SET balance = balance - debit
324+
WHERE accountno = tf1.accountno;
325+
SELECT balance FROM bank WHERE accountno = tf1.accountno;
257326
$$ LANGUAGE SQL;
258327
</programlisting>
259328

260329
which adjusts the balance and returns the new balance.
261330
The same thing could be done in one command using <literal>RETURNING</>:
262331

263332
<programlisting>
264-
CREATE FUNCTION tf1 (integer, numeric) RETURNS numeric AS $$
333+
CREATE FUNCTION tf1 (accountno integer, debit numeric) RETURNS integer AS $$
265334
UPDATE bank
266-
SET balance = balance - $2
267-
WHERE accountno = $1
335+
SET balance = balance - debit
336+
WHERE accountno = tf1.accountno
268337
RETURNING balance;
269338
$$ LANGUAGE SQL;
270339
</programlisting>
@@ -275,11 +344,9 @@ $$ LANGUAGE SQL;
275344
<title><acronym>SQL</acronym> Functions on Composite Types</title>
276345

277346
<para>
278-
When writing functions with arguments of composite
279-
types, we must not only specify which
280-
argument we want (as we did above with <literal>$1</> and <literal>$2</literal>) but
281-
also the desired attribute (field) of that argument. For example,
282-
suppose that
347+
When writing functions with arguments of composite types, we must not
348+
only specify which argument we want but also the desired attribute
349+
(field) of that argument. For example, suppose that
283350
<type>emp</type> is a table containing employee data, and therefore
284351
also the name of the composite type of each row of the table. Here
285352
is a function <function>double_salary</function> that computes what someone's
@@ -524,39 +591,6 @@ SELECT getname(new_emp());
524591
</para>
525592
</sect2>
526593

527-
<sect2 id="xfunc-named-parameters">
528-
<title><acronym>SQL</> Functions with Parameter Names</title>
529-
530-
<indexterm>
531-
<primary>function</primary>
532-
<secondary>named parameter</secondary>
533-
</indexterm>
534-
535-
<para>
536-
It is possible to attach names to a function's parameters, for example
537-
538-
<programlisting>
539-
CREATE FUNCTION tf1 (acct_no integer, debit numeric) RETURNS numeric AS $$
540-
UPDATE bank
541-
SET balance = balance - $2
542-
WHERE accountno = $1
543-
RETURNING balance;
544-
$$ LANGUAGE SQL;
545-
</programlisting>
546-
547-
Here the first parameter has been given the name <literal>acct_no</>,
548-
and the second parameter the name <literal>debit</>.
549-
So far as the SQL function itself is concerned, these names are just
550-
decoration; you must still refer to the parameters as <literal>$1</>,
551-
<literal>$2</>, etc within the function body. (Some procedural
552-
languages let you use the parameter names instead.) However,
553-
attaching names to the parameters is useful for documentation purposes.
554-
When a function has many parameters, it is also useful to use the names
555-
while calling the function, as described in
556-
<xref linkend="sql-syntax-calling-funcs">.
557-
</para>
558-
</sect2>
559-
560594
<sect2 id="xfunc-output-parameters">
561595
<title><acronym>SQL</> Functions with Output Parameters</title>
562596

@@ -571,7 +605,7 @@ $$ LANGUAGE SQL;
571605

572606
<screen>
573607
CREATE FUNCTION add_em (IN x int, IN y int, OUT sum int)
574-
AS 'SELECT $1 + $2'
608+
AS 'SELECT x + y'
575609
LANGUAGE SQL;
576610

577611
SELECT add_em(3,7);
@@ -588,7 +622,7 @@ SELECT add_em(3,7);
588622

589623
<screen>
590624
CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product int)
591-
AS 'SELECT $1 + $2, $1 * $2'
625+
AS 'SELECT x + y, x * y'
592626
LANGUAGE SQL;
593627

594628
SELECT * FROM sum_n_product(11,42);

0 commit comments

Comments
 (0)