CREATE FUNCTION¶
CREATE FUNCTION
creates a new user-defined function (UDF) in an existing database.
See more in our Python UDF (user-defined functions) guide.
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();