Writing Functions

PL/pgSQL functions are named, stored routines that accept parameters, execute logic, and return a value. They can be called from SQL queries, making them extremely versatile.

Basic Syntax

CREATE OR REPLACE FUNCTION function_name(
  param1 data_type,
  param2 data_type DEFAULT default_value
)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
  -- local variables
BEGIN
  -- statements
  RETURN value;
END;
$$;

A Simple Example

CREATE OR REPLACE FUNCTION calculate_bonus(
  p_salary  NUMERIC,
  p_percent NUMERIC DEFAULT 10
)
RETURNS NUMERIC
LANGUAGE plpgsql AS $$
BEGIN
  RETURN p_salary * (p_percent / 100.0);
END;
$$;

Calling it from SQL:

SELECT first_name, calculate_bonus(salary) AS bonus FROM employees;
SELECT calculate_bonus(50000, 15);

RETURNS VOID

For functions that perform an action without returning a value:

CREATE OR REPLACE FUNCTION log_action(p_msg TEXT)
RETURNS VOID LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log(message, logged_at) VALUES (p_msg, NOW());
END;
$$;

Call with: PERFORM log_action('User login'); from PL/pgSQL, or SELECT log_action('…'); from SQL.

Returning a Row (%ROWTYPE)

CREATE OR REPLACE FUNCTION get_employee(p_id INT)
RETURNS employees%ROWTYPE LANGUAGE plpgsql AS $$
DECLARE
  v_emp employees%ROWTYPE;
BEGIN
  SELECT * INTO v_emp FROM employees WHERE id = p_id;
  RETURN v_emp;
END;
$$;

Set-Returning Functions (RETURNS TABLE)

Return multiple rows from a function:

CREATE OR REPLACE FUNCTION get_high_earners(p_threshold NUMERIC)
RETURNS TABLE(name TEXT, salary NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
  RETURN QUERY
    SELECT first_name, salary
    FROM   employees
    WHERE  salary > p_threshold
    ORDER  BY salary DESC;
END;
$$;

Calling it:

SELECT * FROM get_high_earners(60000);

RETURN NEXT

Build a result set row by row:

CREATE OR REPLACE FUNCTION count_up(n INT)
RETURNS SETOF INT LANGUAGE plpgsql AS $$
BEGIN
  FOR i IN 1..n LOOP
    RETURN NEXT i;
  END LOOP;
END;
$$;

Function Volatility

Declare volatility so the query planner can optimise correctly:

Keyword	Meaning
VOLATILE (default)	Can modify DB; called every time it appears
STABLE	No DB modifications; same args return same result within a query
IMMUTABLE	No DB access; same args always return same result

CREATE OR REPLACE FUNCTION full_name(first TEXT, last TEXT)
RETURNS TEXT LANGUAGE plpgsql IMMUTABLE AS $$
BEGIN
  RETURN first || ' ' || last;
END;
$$;

Dropping a Function

DROP FUNCTION function_name(param_types);

Include parameter types to disambiguate overloaded functions.