Scalar Functions (UDSFs)

A user-defined scalar function (UDSF) returns a single value for each row of data it reads. You can use a UDSF anywhere you can use a built-in Vertica function. You usually develop a UDSF to perform data manipulations that are too complex or too slow to perform using SQL statements and functions. UDSFs also let you use analytic functions provided by third-party libraries within Vertica while still maintaining high performance.

A UDSF returns a single column. You can use a ROW to return multiple values. A ROW is a group of property-value pairs. In the following example, div_with_rem is a UDFS that performs a division operation, returning the quotient and remainder as integers:

=> SELECT div_with_rem(18,5);
        div_with_rem
------------------------------
 {"quotient":3,"remainder":3}
(1 row)

A ROW returned from a UDFS can be used only in the following contexts:

  • SELECT (returns the ROW column in JSON format, as for any other ROW)
  • SELECT of individual fields from the ROW
  • IS [NOT] NULL
  • ORDER BY
  • In a view or subquery

Fields of scalar types can be extracted from the ROW and used in expressions that support those scalar types.

Your UDSF must return a value for every input row (unless it generates an error; see Handling Errors for details). Failure to return a value for a row results in incorrect results and potentially destabilizes the Vertica server if not run in Fenced and Unfenced Modes.

A UDSF can have up to 9800 arguments.

In This Section