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

CREATE FUNCTION

Creates a new scalar user-defined function (UDF).

Syntax

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

Parameters

OR REPLACE

Specifies that if a function with the same name and input argument data types, or signature, as this one already exists, the existing function is replaced. You can only replace a function with a new function that defines an identical set of data types. You must be a superuser to replace a function.

If you define a function with the same name as an existing function but a different signature, you will create a new function. In other words, the function name will be overloaded. For more information, see Overloading Function Names.

f_function_name

The name of the function. If you specify a schema name (such as myschema.myfunction), the function is created using the specified schema. Otherwise, the function is created in the current schema. For more information about valid names, see Names and Identifiers.

We recommend that you prefix all UDF names with f_. Amazon Redshift reserves the f_ prefix for UDF names, so by using the f_ prefix, you ensure that your UDF name will not conflict with any existing or future Amazon Redshift built-in SQL function names. For more information, see Naming UDFs.

You can define more than one function with the same function name if the data types for the input arguments are different. In other words, the function name will be overloaded. For more information, see Overloading Function Names.

argument_name data_type

A list of input argument names and data types. A UDF can implement any standard Amazon Redshift data type. In addition, UDFs support a data type of ANYELEMENT, which is automatically converted to a standard data type based on the data type of the corresponding argument supplied at run time. If multiple arguments use ANYELEMENT, they will all resolve to the same data type at run time, based on the first ANYELEMENT argument in the list. For more information, see UDF Data Types and Data Types.

You can specify a maximum of 32 arguments.

RETURNS data_type

The data type of the value returned by the function. The RETURNS data type can be any standard Amazon Redshift data type. In addition, UDFs can use a data type of ANYELEMENT, which is automatically converted to a standard data type based on the argument supplied at run time. If you specify ANYELEMENT for the return data type, at least one argument must use ANYELEMENT. The actual return data type will match the data type supplied for the ANYELEMENT argument when the function is called. For more information, see UDF Data Types.

VOLATILE | STABLE | IMMUTABLE

Informs the query optimizer about the volatility of the function.

You will get the best optimization if you label your function with the strictest volatility category that is valid for it. On the other hand, if the category is too strict, there is a risk that the optimizer will erroneously skip some calls, resulting in an incorrect result set. In order of strictness, beginning with the least strict, the volatility categories are:

  • VOLATILE

  • STABLE

  • IMMUTABLE

VOLATILE

Given the same arguments, the function can return different results on successive calls, even for the rows in a single statement. The query optimizer cannot make any assumptions about the behavior of a volatile function, so a query that uses a volatile function must reevaluate the function for every input row.

STABLE

Given the same arguments, the function is guaranteed to return the same results for all rows processed within a single statement. The function can return different results when called in different statements. This category allows the optimizer to optimize multiple calls of the function within a single statement to a single call for the statement.

IMMUTABLE

Given the same arguments, the function always returns the same result, forever. When a query calls an IMMUTABLE function with constant arguments, the optimizer pre-evaluates the function.

AS $$ ... $$ LANGUAGE plpythonu

A construct that encloses the statement to be executed and a keyword showing the language in which the statement is written. The literal keywords AS $$ and $$ LANGUAGE plpythonu are required.

Amazon Redshift requires you to enclose the statement in your function by using a format called dollar quoting. Anything within the enclosure is passed exactly as is. You don’t need to escape any special characters because the contents of the string are written literally.

With dollar quoting, you use a pair of dollar signs ($$) to signify the start and the end of the statement to execute, as shown in the following example.

Copy
$$ my statement $$

Optionally, between the dollar signs in each pair, you can specify a string to help identify the statement. The string that you use must be the same in both the start and the end of the enclosure pairs. This string is case-sensitive, and it follows the same constraints as an unquoted identifier except that it cannot contain dollar signs. The following example uses the string test.

Copy
$test$ my statement $test$

For more information about dollar quoting, see Dollar-quoted String Constants in the Lexical Structure section of the PostgreSQL manual.

The statement that you pass in with the function must conform to indentation requirements as specified in the Style Guide for Python Code on the Python website. Python (plpythonu) is the only supported language.

python_program

A valid executable Python program that returns a value. For more information, see Python Language Support for UDFs.

Examples

The following example creates a function that compares two integers and returns the larger value:

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

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

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