PL/pgSQL Functions

Master PL/pgSQL CREATE FUNCTION — basic syntax, RETURNS clauses, dollar-quoted bodies, conditional logic, loops, and exception handling within functions. Critical fix: factorial function should use BIGINT not INT (13! overflows INT). Includes the three function call notations: positional, named, and mixed.

A function in PL/pgSQL is a named, reusable unit of logic that takes parameters, runs a body, and returns a value. Functions can be called from SELECT statements, used inside expressions, and composed with other SQL — they're first-class citizens of the database.

Functions are the right tool when you want to:

• Encapsulate a computation that's used in multiple places
• Add domain-specific logic that pure SQL can't express
• Build a function callable from your application's queries
• Aggregate data with custom rules

For multi-step DML or transaction control, use procedures (covered on previous pages). For everything else that returns a value, use a function.

Four required parts:

1. Name and parameters — what to call it and what arguments to take
2. RETURNS — the type of value the function produces
3. LANGUAGE plpgsql — tells PostgreSQL which interpreter to use. Without this, PostgreSQL defaults to plain SQL and the body won't parse.
4. Dollar-quoted body — the code itself, between $$ markers

The smallest possible function:

DROP FUNCTION IF EXISTS add_numbers(INT, INT);

CREATE OR REPLACE FUNCTION add_numbers(a INT, b INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a + b;
END;
$$;

SELECT add_numbers(10, 20);

Count the films whose length falls within a given range. The function declares a local variable to hold the result:

DROP TABLE IF EXISTS film;

CREATE TABLE film (
    film_id SERIAL PRIMARY KEY,
    title   VARCHAR(255) NOT NULL,
    length  INT
);

INSERT INTO film (title, length) VALUES
    ('Film A',  90),
    ('Film B', 120),
    ('Film C', 150),
    ('Film D',  80),
    ('Film E',  30),
    ('Film F',  60);

DROP FUNCTION IF EXISTS get_film_count(INT, INT);

CREATE OR REPLACE FUNCTION get_film_count(len_from INT, len_to INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
    film_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO film_count
    FROM   film
    WHERE  length BETWEEN len_from AND len_to;

    RETURN film_count;
END;
$$;

SELECT get_film_count(90, 120);

Classify a number as positive, negative, or zero. Demonstrates IF/ELSIF/ELSE inside a function with multiple RETURN points:

CREATE OR REPLACE FUNCTION check_number(num INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    IF num > 0 THEN
        RETURN 'Positive';
    ELSIF num < 0 THEN
        RETURN 'Negative';
    ELSE
        RETURN 'Zero';
    END IF;
END;
$$;

SELECT check_number(10) AS pos,
       check_number(-5) AS neg,
       check_number(0)  AS zero;

CREATE OR REPLACE FUNCTION factorial(n INT)
RETURNS BIGINT
LANGUAGE plpgsql
AS $$
DECLARE
    result BIGINT := 1;
BEGIN
    FOR i IN 1..n LOOP
        result := result * i;
    END LOOP;

    RETURN result;
END;
$$;

SELECT factorial(5) AS five_fact,
       factorial(10) AS ten_fact;

Catch division by zero and return a sentinel:

CREATE OR REPLACE FUNCTION divide_numbers(a INT, b INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN a::NUMERIC / b;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'Cannot divide % by zero', a;
        RETURN NULL;
END;
$$;

SELECT divide_numbers(10, 2) AS ok,
       divide_numbers(10, 0) AS by_zero;

The cast a::NUMERIC forces numeric division (rather than integer division), so 10 / 2 would otherwise be 5 instead of 5.0. The NULL appears as an empty cell in the output for the by-zero case.

The rule for mixed: all positional arguments must come before any named ones. get_film_count(len_from => 40, 90) is rejected with positional argument cannot follow named argument.

1. Always include LANGUAGE plpgsql. Without it, PostgreSQL treats the body as plain SQL and PL/pgSQL syntax (DECLARE, LOOP, IF, etc.) will fail to parse.
2. Use DROP FUNCTION IF EXISTS at the top of deployment scripts — handles signature changes that CREATE OR REPLACE alone can't.
3. Pick wide enough return types. BIGINT for factorials and large counts; NUMERIC for arbitrary precision; NUMERIC(p, s) for money.
4. Cast for numeric division. 10 / 3 in SQL is integer division (3, not 3.33). Cast one operand to NUMERIC when you want a fractional result.
5. Reach for plain SQL when PL/pgSQL adds nothing. A function that's just RETURN a + b is faster as LANGUAGE sql.
6. Use named arguments for functions with many parameters. Reads more clearly than a row of unlabelled positional values.

• A function is a named, reusable unit of logic that takes parameters and returns a value, callable from SELECT.
• The four required parts: name + parameters, RETURNS type, LANGUAGE plpgsql, dollar-quoted body.
• Functions support DECLARE for local variables, control flow (IF/LOOP/CASE), and exception handling (EXCEPTION block).
• Pick the right return type for your data — BIGINT over INT for factorials, NUMERIC for money.
• Three call styles: positional, named, and mixed. Named arguments help readability for many-parameter functions.
• For multi-step DML or transaction control, use procedures instead.