CREATE FUNCTION¶

CREATE FUNCTION creates a new user-defined function (UDF) in an existing database.

Permissions¶

The role must have the CREATE FUNCTION permission at the database level.

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 ::= Valid SQL code

Parameters¶

Parameter	Description
`OR REPLACE`	Create a new function, and overwrite any existing function by the same name. Does not return an error if the function already exists. `CREATE OR REPLACE` does not check the function contents or structure, only the function name.
`function_name`	The name of the function to create, which must be unique inside the database.
`argument_list`	A comma separated list of column definitions. A column definition includes a name identifier and a datatype.
`return_type`	The SQL datatype of the return value, such as `INT`, `TEXT`, etc.
`function_body`	SQL code, dollar-quoted (`$$`).

Examples¶

Calculate distance between two points

CREATE OR REPLACE FUNCTION my_distance(x1 FLOAT, y1 FLOAT, x2 FLOAT, y2 FLOAT)
RETURNS FLOAT AS $$
SELECT CASE
        WHEN y1 < x1 THEN 0.0
        ELSE SQRT(POWER(y2 - y1, 2) + POWER(x2 - x1, 2))
END;
$$ LANGUAGE sql;


-- Usage:
SELECT my_distance(1.0, 2.0, 4.0, 6.0);