Scalar Python UDFs - Amazon Redshift

Scalar Python UDFs

A scalar Python UDF incorporates a Python program that runs when the function is called and returns a single value. The CREATE FUNCTION command defines the following parameters:

  • (Optional) Input arguments. Each argument must have a name and a data type.

  • One return data type.

  • One executable Python program.

The input and return data types for Python UDFs can be any of the following types:

  • SMALLINT

  • INTEGER

  • BIGINT

  • DECIMAL

  • REAL

  • DOUBLE PRECISION

  • BOOLEAN

  • CHAR

  • VARCHAR

  • DATE

  • TIMESTAMP

  • ANYELEMENT

The aliases for these types are also valid. For a full list of data types and their aliases, see Data types.

When Python UDFs use the data type ANYELEMENT, Amazon Redshift automatically converts to a standard data type based on the arguments supplied at runtime. For more information, see ANYELEMENT data type.

When an Amazon Redshift query calls a scalar UDF, the following steps occur at runtime:

  1. The function converts the input arguments to Python data types.

    For a mapping of Amazon Redshift data types to Python data types, see Python UDF data types.

  2. The function runs the Python program, passing the converted input arguments.

  3. The Python code returns a single value. The data type of the return value must correspond to the RETURNS data type specified by the function definition.

  4. The function converts the Python return value to the specified Amazon Redshift data type, then returns that value to the query.

Note

Python 3 isn’t available for Python UDFs. To get Python 3 support for Amazon Redshift UDFs, use Scalar Lambda UDFs instead.