CREATE FUNCTION

Create a user-defined scalar or table function.

Syntax

Parameters

Parameter	Description
function_name	Name of the function to create
param_name	Name of a function parameter
data_type	Data type of the parameter
return_type	Data type of the scalar return value
column_name	Name of a column in the returned table
body	SQL query that computes the result

Examples

Scalar function

CREATE FUNCTION article_url(slug TEXT)
RETURNS TEXT AS $$
    SELECT '/articles/' || slug
$$ LANGUAGE SQL;

SELECT title, article_url(slug) FROM articles;

Table function (dollar-quoted)

CREATE FUNCTION top_employees()
RETURNS TABLE(name TEXT, grade INTEGER)
AS $$
    SELECT name, (2 * weight + age) / 10 AS grade
    FROM employees
$$ LANGUAGE SQL;

SELECT * FROM top_employees();

Table function (BEGIN ATOMIC)

CREATE FUNCTION employees_above_grade(grade_threshold INTEGER)
RETURNS TABLE(name TEXT, grade INTEGER)
LANGUAGE SQL
BEGIN ATOMIC
    SELECT name, (2 * weight + age) / 10 AS grade
    FROM employees
    WHERE (2 * weight + age) / 10 > grade_threshold;
END;

SELECT * FROM employees_above_grade(16);

Table function in a join

SELECT e.name, e.grade, d.department_name
FROM employees_above_grade(10) AS e
JOIN departments d ON e.department_id = d.id;

See also

• DROP FUNCTION
• SELECT