CREATE FUNCTION

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

UDFs are Python functions that can run on row values.

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 python
    ;

function_name ::= identifier

argument_list :: = { value_name type_name [, ...] }

value_name ::= identifier

return_type ::= type_name

function_body ::= Valid Python 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, VARCHAR, etc.
function_body	Python 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 $$
import  math
if  y1  <  x1:
    return  0.0
else:
    return  math.sqrt((y2  -  y1)  **  2  +  (x2  -  x1)  **  2)
$$ LANGUAGE PYTHON;

-- Usage:
SELECT  city, my_location, my_distance(x1,y1,x2,y2)  from  cities;

Calling files from other locations

-- Our script my_code.py is in ~/my_python_stuff

CREATE FUNCTION write_log()
  RETURNS INT
  AS $$
import sys
sys.path.append("/home/user/my_python_stuff")

import my_code as f

f.main()

return 1

$$ LANGUAGE PYTHON;

-- Usage:
SELECT write_log();