UDx parameters

Parameters let you define arguments for your UDxs that remain constant across all of the rows processed by the SQL statement that calls your UDx. Typically, your UDxs accept arguments that come from columns in a SQL statement. For example, in the following SQL statement, the arguments a and b to the add2ints UDSF change value for each row processed by the SELECT statement:

=> SELECT a, b, add2ints(a,b) AS 'sum' FROM example;
a | b  | sum
---+----+-----
1 |  2 |   3
3 |  4 |   7
5 |  6 |  11
7 |  8 |  15
9 | 10 |  19
(5 rows)

Parameters remain constant for all the rows your UDx processes. You can also make parameters optional so that if the user does not supply it, your UDx uses a default value. For example, the following example demonstrates calling a UDSF named add2intsWithConstant that has a single parameter value named constant whose value is added to each the arguments supplied in each row of input:

=> SELECT a, b, add2intsWithConstant(a, b USING PARAMETERS constant=42)
    AS 'a+b+42' from example;
a | b  | a+b+42
---+----+--------
1 |  2 |     45
3 |  4 |     49
5 |  6 |     53
7 |  8 |     57
9 | 10 |     61
(5 rows)

The topics in this section explain how to develop UDxs that accept parameters.