Executing Functions - Kinetica Docs

An existing UDF/UDTF can be executed using the EXECUTE FUNCTION command. Additionally, a UDTF can be executed as a table function within a SELECT statement.

Execute UDF/UDTF Syntax

EXECUTE FUNCTION <function name>
(
   <function parameter 1> => <function value 1>,
   ...
   <function parameter N> => <function value N>
)

Execute UDTF Table Function Syntax

SELECT * FROM TABLE
(
   <function name>
   (
      <function parameter 1> => <function value 1>,
      ...
      <function parameter N> => <function value N>
   )
)

The <function name> should be the name of an existing UDF.

To preserve the runtime statistics of a UDF/UDTF, use the KI_HINT_SAVE_UDF_STATS hint in the call to it; see below for an example.

Function Parameters

The following are the available function parameters that may be passed to a UDF in an EXECUTE FUNCTION or table function call, along with their corresponding function values.

INPUT_TABLE_NAMES

Optional set of “tables” that will be used as input to the UDF/UDTF, specified as a set of queries defining the source data for each “table”. The value of this parameter is either the INPUT_TABLE or INPUT_TABLES function, depending on the number of input “tables”.

Value	Description
`INPUT_TABLE(<query>)`	If only one input table is needed, it can be passed by query or by name to the `INPUT_TABLE` function; e.g.: `INPUT_TABLE(customer) INPUT_TABLE(SELECT id, name FROM customer)`
`INPUT_TABLES(<queries>)`	If more than one input table is needed, each can be passed via query as a list to the `INPUT_TABLES` function; e.g.: `INPUT_TABLES ( (SELECT id, name FROM customer), (SELECT id, customer_id FROM order) )`

OUTPUT_TABLE_NAMES

Optional set of tables that will be used as output, specified as a set of names passed as a comma-delimited list of strings to the OUTPUT_TABLES function; e.g.:

OUTPUT_TABLES('sales_summary', 'customer_profile')

PARAMS

Optional list of parameters to pass to the function, specified as a set of key/value pairs passed as a comma-delimited list of <key> = '<value>' assignments to the KV_PAIRS function; e.g.:

KV_PAIRS(customer_id = '1', report_type = 'yearly')

OPTIONS

Optional list of function execution options, specified as a set of key/value pairs passed as a comma-delimited list of <key> = '<value>' assignments to the KV_PAIRS function; e.g.:

KV_PAIRS(run_tag = 'my-udf')

All valid options from Execute Proc can be used here.

Examples

To execute the udf_st UDF, specifying an input and output table and the hint to save runtime statistics:

Execute UDF Example

EXECUTE FUNCTION udf_st /* KI_HINT_SAVE_UDF_STATS */
(
    INPUT_TABLE_NAMES => INPUT_TABLE(example.udf_st_in),
    OUTPUT_TABLE_NAMES => OUTPUT_TABLES('example.udf_st_out'),
    PARAMS => KV_PAIRS(sentence = 'I live in a neighborhood in a small town.')
)

To execute the UDTF_ST UDTF, specifying an input table and returning the output as the result set of a SELECT statement:

Execute UDTF Table Function Example

SELECT TOP 5 score, sentence
FROM
	example.udf_st_in i,
	TABLE
	(
	    UDTF_ST
	    (
	        INPUT_TABLE_NAMES => INPUT_TABLE(example.udf_st_in),
	        PARAMS => KV_PAIRS(sentence = 'I live in a neighborhood in a small town.')
	    )
	) o
WHERE i.id = o.id
ORDER BY score DESC

​Function Parameters

​Examples

Function Parameters

Examples