SQL Functions

PostgreSQL lets you bundle one or more SQL statements into a reusable function that you can call just like a built-in function. SQL-language functions are the simplest kind: the function body is plain SQL, and PostgreSQL can often inline them directly into the calling query for excellent performance.

Why Write Functions?

• Reusability -- define logic once, call it everywhere.
• Encapsulation -- hide complex queries behind a clean interface.
• Security -- grant EXECUTE on a function instead of SELECT on underlying tables.
• Performance -- the planner can inline simple SQL functions, avoiding function-call overhead.

Creating Your First Function

The basic syntax is:

CREATE FUNCTION function_name(parameter_list)
RETURNS return_type
LANGUAGE SQL
AS $$
  -- one or more SQL statements
  -- the result of the LAST statement is returned
$$;

The $$ delimiters mark the function body (called dollar quoting). You can also use single quotes, but dollar quoting is far easier to read.

A Simple Scalar Function

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER
LANGUAGE SQL
AS $$
  SELECT a + b;
$$;

Call it like any expression:

SELECT add_numbers(3, 7);
-- Returns: 10

Using Positional Parameters

You can also reference parameters by position with $1, $2, etc.:

CREATE FUNCTION discount_price(price NUMERIC, pct NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
AS $$
  SELECT ROUND($1 * (1 - $2 / 100), 2);
$$;

SELECT discount_price(99.99, 15);
-- Returns: 84.99

Named parameters and positional notation can be mixed, but sticking with names is generally clearer.

Parameter Defaults

Parameters can have default values, making them optional:

CREATE FUNCTION greet(name TEXT, greeting TEXT DEFAULT 'Hello')
RETURNS TEXT
LANGUAGE SQL
AS $$
  SELECT greeting || ', ' || name || '!';
$$;

SELECT greet('Alice');           -- Hello, Alice!
SELECT greet('Bob', 'Welcome'); -- Welcome, Bob!

Return Types

Returning a Single Value (Scalar)

The examples above return a single scalar value. The return type can be any PostgreSQL data type: INTEGER, TEXT, NUMERIC, BOOLEAN, DATE, JSONB, etc.

Returning a Row (Composite Type)

You can return a single composite row by returning a table's row type:

CREATE FUNCTION newest_employee()
RETURNS employees
LANGUAGE SQL
AS $$
  SELECT * FROM employees ORDER BY hire_date DESC LIMIT 1;
$$;

SELECT (newest_employee()).name;
SELECT * FROM newest_employee();  -- expand all columns

Returning Multiple Rows with RETURNS TABLE

RETURNS TABLE defines both the output columns and tells PostgreSQL this function can return multiple rows:

CREATE FUNCTION high_earners(min_salary NUMERIC)
RETURNS TABLE(emp_name TEXT, emp_salary NUMERIC, emp_department TEXT)
LANGUAGE SQL
AS $$
  SELECT name, salary, department
  FROM employees
  WHERE salary >= min_salary
  ORDER BY salary DESC;
$$;

Call it in the FROM clause just like a table:

SELECT * FROM high_earners(80000);

Returning SETOF

An alternative to RETURNS TABLE is RETURNS SETOF:

CREATE FUNCTION active_employees()
RETURNS SETOF employees
LANGUAGE SQL
AS $$
  SELECT * FROM employees WHERE is_active = true;
$$;

SELECT name, department FROM active_employees();

RETURNS SETOF sometype returns rows matching an existing type, while RETURNS TABLE(...) lets you define the shape inline.

Multiple Statements

A SQL function can contain multiple statements. Only the result of the last statement is returned:

CREATE FUNCTION transfer_and_log(
  from_acct INT, to_acct INT, amt NUMERIC
)
RETURNS VOID
LANGUAGE SQL
AS $$
  UPDATE accounts SET balance = balance - amt WHERE id = from_acct;
  UPDATE accounts SET balance = balance + amt WHERE id = to_acct;
  INSERT INTO transfer_log(from_id, to_id, amount, transferred_at)
    VALUES (from_acct, to_acct, amt, NOW());
$$;

Functions that perform side effects but return nothing use RETURNS VOID.

Volatility Categories

Every function has a volatility classification that tells the query planner how aggressively it can optimize calls:

Why It Matters

-- IMMUTABLE lets PostgreSQL evaluate once during planning
CREATE FUNCTION cm_to_inches(cm NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE
AS $$
  SELECT ROUND(cm / 2.54, 2);
$$;

-- STABLE is safe for use in index scans
CREATE FUNCTION current_fiscal_year()
RETURNS INTEGER
LANGUAGE SQL
STABLE
AS $$
  SELECT EXTRACT(YEAR FROM CURRENT_DATE)::INTEGER;
$$;

Setting the wrong volatility can cause incorrect results. If in doubt, leave it as VOLATILE (the default) -- it is always safe, just potentially slower.

Additional Options

PARALLEL SAFE

If your function is thread-safe and IMMUTABLE or STABLE, mark it PARALLEL SAFE so PostgreSQL can use it in parallel query plans:

CREATE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT
LANGUAGE SQL
IMMUTABLE PARALLEL SAFE
AS $$
  SELECT first || ' ' || last;
$$;

STRICT (RETURNS NULL ON NULL INPUT)

A STRICT function automatically returns NULL if any argument is NULL, without executing the body:

CREATE FUNCTION safe_divide(a NUMERIC, b NUMERIC)
RETURNS NUMERIC
LANGUAGE SQL
IMMUTABLE STRICT
AS $$
  SELECT a / b;
$$;

SELECT safe_divide(10, 0);    -- ERROR: division by zero
SELECT safe_divide(10, NULL); -- Returns NULL (body never runs)

COST and ROWS

You can hint to the planner about function expense:

CREATE FUNCTION expensive_lookup(id INT)
RETURNS TEXT
LANGUAGE SQL
STABLE
COST 1000        -- relative cost (default is 100)
ROWS 50          -- estimated rows for set-returning functions
AS $$
  SELECT description FROM big_table WHERE big_table.id = expensive_lookup.id;
$$;

Replacing and Dropping Functions

Use CREATE OR REPLACE to update an existing function without dropping it first (the signature must match):

CREATE OR REPLACE FUNCTION greet(name TEXT, greeting TEXT DEFAULT 'Hello')
RETURNS TEXT
LANGUAGE SQL
AS $$
  SELECT greeting || ', ' || name || '!!';
$$;

Drop a function when you no longer need it:

DROP FUNCTION IF EXISTS greet(TEXT, TEXT);

You must specify the argument types if the function name is overloaded.

Try It Yourself

Since these are conceptual examples, try creating functions in your own PostgreSQL database:

1. Write a function circle_area(radius NUMERIC) that returns the area of a circle. Mark it IMMUTABLE.
2. Write a function employee_count_by_dept(dept TEXT) that returns the number of employees in a department. What volatility should it have?
3. Write a RETURNS TABLE function that accepts a date range and returns all orders within that range.

Summary