Menu
Amazon Redshift
Database Developer Guide (API Version 2012-12-01)

Creating a Scalar UDF

A scalar UDF incorporates a Python program that executes 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 CREATE FUNCTION syntax is as follows:

Copy
CREATE [ OR REPLACE ] FUNCTION f_function_name ( [ argument_name arg_type [ , ... ] ] ) RETURNS data_type { VOLATILE | STABLE | IMMUTABLE } AS $$ python_program $$ LANGUAGE plpythonu;

When an Amazon Redshift query calls a scalar UDF, the following steps occur at run time.

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

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

  2. The function executes 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.

Scalar Function Example

The following example creates a function that compares two numbers and returns the larger value. Note that the indentation of the code between the double dollar signs ($$) is a Python requirement. For more information, see CREATE FUNCTION.

Copy
create function f_greater (a float, b float) returns float stable as $$ if a > b: return a return b $$ language plpythonu;

The following query calls the new f_greater function to query the SALES table and return either COMMISSION or 20 percent of PRICEPAID, whichever is greater:

Copy
select f_greater (commission, pricepaid*0.20) from sales;