Calling UDxs with parameters

You pass parameters to a UDx by adding a USING PARAMETERS clause in the function call after the last argument.

You pass parameters to a UDx by adding a USING PARAMETERS clause in the function call after the last argument.

  • Do not insert a comma between the last argument and the USING PARAMETERS clause.

  • After the USING PARAMETERS clause, add one or more parameter definitions, in the following form:

    <parameter name> = <parameter value>

  • Separate parameter definitions by commas.

Parameter values can be a constant expression (for example 1234 + SQRT(5678)). You cannot use volatile functions (such as RANDOM) in the expression, because they do not return a constant value. If you do supply a volatile expression as a parameter value, by default, Vertica returns an incorrect parameter type warning. Vertica then tries to run the UDx without the parameter value. If the UDx requires the parameter, it returns its own error, which cancels the query.

Calling a UDx with a single parameter

The following example demonstrates how you can call the Add2intsWithConstant UDSF example shown in C++ example: defining parameters:

=> 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)

To remove the first instance of the number 3, you can call the RemoveSymbol UDSF example:

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3');
  original_string   |   RemoveSymbol
--------------------+-------------------
 3re3mo3ve3sy3mb3ol | re3mo3ve3sy3mb3ol
(1 row)

Calling a UDx with multiple parameters

The following example shows how you can call a version of the tokenize UDTF. This UDTF includes parameters to limit the shortest allowed word and force the words to be output in uppercase. Separate multiple parameters with commas.

=> SELECT url, tokenize(description USING PARAMETERS minLength=4, uppercase=true) OVER (partition by url) FROM T;
       url       |   words
-----------------+-----------
 www.amazon.com  | ONLINE
 www.amazon.com  | RETAIL
 www.amazon.com  | MERCHANT
 www.amazon.com  | PROVIDER
 www.amazon.com  | CLOUD
 www.amazon.com  | SERVICES
 www.dell.com    | LEADING
 www.dell.com    | PROVIDER
 www.dell.com    | COMPUTER
 www.dell.com    | HARDWARE
 www.vertica.com | WORLD'S
 www.vertica.com | FASTEST
 www.vertica.com | ANALYTIC
 www.vertica.com | DATABASE
(16 rows)

The following example calls the RemoveSymbol UDSF. By changing the value of the optional parameter, n, you can remove all instances of the number 3:

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3', n=6);
  original_string   | RemoveSymbol
--------------------+--------------
 3re3mo3ve3sy3mb3ol | removesymbol
(1 row)

Calling a UDx with optional or incorrect parameters

You can optionally add the Add2intsWithConstant UDSF's constant parameter. Calling this constraint without the parameter does not return an error or warning:

=> SELECT a,b,Add2intsWithConstant(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)

Although calling a UDx with incorrect parameters generates a warning, by default, the query still runs. For further information on setting the behavior of your UDx when you supply incorrect parameters, see Specifying the behavior of passing unregistered parameters.

=> SELECT a, b,  add2intsWithConstant(a, b USING PARAMETERS wrongparam=42) AS 'result' from example;
WARNING 4332:  Parameter wrongparam was not registered by the function and cannot
be coerced to a definite data type
 a | b  | result
---+----+--------
 1 |  2 |      3
 3 |  4 |      7
 5 |  6 |     11
 7 |  8 |     15
 9 | 10 |     19
(5 rows)