Scalar SQL UDF

A scalar SQL UDF is a user-defined function that returns a single value, such as the sum of a group of values. Scalar UDFs are different than table-value functions, which return a result set in the form of a table.

Syntax

create_function_statement ::=
CREATE [ OR REPLACE ] FUNCTION function_name (argument_list)
RETURNS return_type
AS $$
{ function_body }
$$ LANGUAGE SQL
;

function_name ::= identifier
argument_list :: = { value_name type_name [, ...] }
value_name ::= identifier
return_type ::= type_name
function_body ::= A valid SQL statement

Examples

Support for Different Syntax

Scalar SQL UDF supports standard functionality even when different syntax is used.

In the example below, the syntax dateadd is used instead of add_months, although the function of each is identical. In addition, the operation works correctly even though the order of the expressions in add_months (dt, datetime, and n int) is different than MONTH, n, and dt in dateadd.

CREATE OR REPLACE FUNCTION add_months(dt datetime,n int)
RETURNS datetime
AS $$
SELECT  dateadd(MONTH ,n,dt)
$$ LANGUAGE SQL;

Manipulating Strings

The Scalar SQL UDF can be used to manipulate strings.

The following example shows the correct syntax for converting a TEXT date to the DATE type:

CREATE OR REPLACE FUNCTION  STR_TO_DATE(f text)
RETURNS date
AS $$
SELECT (substring(f,1,4)||'-'||substring(f,5,2)||'-'||substring(f,7,2))::date
$$ LANGUAGE SQL;

Manually Building Functionality

You can use the Scalar SQL UDF to manually build functionality for otherwise unsupported operations.

CREATE OR REPLACE function "least_sq" (a float, b float) -- Replace the LEAST(from hql) function
returns float AS
  $$SELECT CASE
        WHEN a <= b THEN a
        WHEN b < a THEN b
        WHEN a IS NULL THEN b
        WHEN b IS NULL THEN a
        ELSE NULL
   END;
  $$
LANGUAGE SQL;

Usage Notes

The following usage notes apply when using simple scalar SQL UDF’s:

• During this stage, the SQL embedded in the function body must be of the type SELECT expr;. Creating a UDF with invalid SQL, or with valid SQL of any other type, results in an error.

• The argument list can be left empty.

• SQL UDFs can reference other UDF’s.

NOTICE: A function cannot (directly or indirectly) reference itself (such as by referencing another function that references it).

Restriction

Simple scalar SQL UDF’s cannot currently reference other UDFs.