Specifying the behavior of passing unregistered parameters

By default, Vertica issues a warning message when you pass a UDx an unregistered parameter.

By default, Vertica issues a warning message when you pass a UDx an unregistered parameter. An unregistered parameter is one that you did not declare in the getParameterType() method.

You can control the behavior of your UDx when you pass it an unregistered parameter by altering the StrictUDxParameterChecking configuration parameter.

Unregistered parameter behavior settings

You can specify the behavior of your UDx in response to one or more unregistered parameters. To do so, set the StrictUDxParameterChecking configuration parameter to one of the following values:

  • 0: Allows unregistered parameters to be accessible to the UDx. The ParamReader class's getType() method determines the data type of the unregistered parameter. Vertica does not display any warning or error message.

  • 1 (default): Ignores the unregistered parameter and allows the function to run. Vertica displays a warning message.

  • 2: Returns an error and does not allow the function to run.

Examples

The following examples demonstrate the behavior you can specify using different values with the StrictUDxParameterChecking parameter.

View the current value of StrictUDxParameterChecking

To view the current value of the StrictUDxParameterChecking configuration parameter, run the following query:


=> \x
Expanded display is on.
=> SELECT * FROM configuration_parameters WHERE parameter_name = 'StrictUDxParameterChecking';
-[ RECORD 1 ]-----------------+------------------------------------------------------------------
node_name                     | ALL
parameter_name                | StrictUDxParameterChecking
current_value                 | 1
restart_value                 | 1
database_value                | 1
default_value                 | 1
current_level                 | DATABASE
restart_level                 | DATABASE
is_mismatch                   | f
groups                        |
allowed_levels                | DATABASE
superuser_only                | f
change_under_support_guidance | f
change_requires_restart       | f
description                   | Sets the behavior to deal with undeclared UDx function parameters

Change the value of StrictUDxParameterChecking

You can change the value of the StrictUDxParameterChecking configuration parameter at the database, node, or session level. For example, you can change the value to '0' to specify that unregistered parameters can pass to the UDx without displaying a warning or error message:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 0;
ALTER DATABASE

Invalid parameter behavior with RemoveSymbol

The following example demonstrates how to call the RemoveSymbol UDSF example. The RemoveSymbol UDSF has a required parameter, symbol, and an optional parameter, n. In this case, you do not use the optional parameter.

If you pass both symbol and an additional parameter called wrongParam, which is not declared in the UDx, the behavior of the UDx changes corresponding to the value of StrictUDxParameterChecking.

When you set StrictUDxParameterChecking to '0', the UDx runs normally without a warning. Additionally, wrongParam becomes accessible to the UDx through the ParamReader object of the ServerInterface object:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 0;
ALTER DATABASE

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

When you set StrictUDxParameterChecking to '1', the UDx ignores wrongParam and runs normally. However, it also issues a warning message:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 1;
ALTER DATABASE

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3', wrongParam='x');
WARNING 4320:  Parameter wrongParam was not registered by the function and cannot be coerced to a definite data type
  original_string   |   RemoveSymbol
--------------------+-------------------
 3re3mo3ve3sy3mb3ol | re3mo3ve3sy3mb3ol
(1 row)

When you set StrictUDxParameterChecking to '2', the UDx encounters an error when it tries to call wrongParam and does not run. Instead, it generates an error message:


=> ALTER DATABASE DEFAULT SET StrictUDxParameterChecking = 2;
ALTER DATABASE

=> SELECT '3re3mo3ve3sy3mb3ol' original_string, RemoveSymbol('3re3mo3ve3sy3mb3ol' USING PARAMETERS symbol='3', wrongParam='x');
ERROR 0:  Parameter wrongParam was not registered by the function