Functions & Procedures (PL/pgSQL)

A simple SQL function

CREATE OR REPLACE FUNCTION letter_grade(p_cgpa numeric)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT CASE
    WHEN p_cgpa >= 3.75 THEN 'A+'
    WHEN p_cgpa >= 3.50 THEN 'A'
    WHEN p_cgpa >= 3.00 THEN 'B'
    ELSE 'C' END;
$$;

SELECT name_en, letter_grade(cgpa) FROM students;

The $$ is dollar quoting — it lets you write a body containing quotes without escaping anything. You can also use $func$ … $func$ for nesting.

Volatility matters

Label	Meaning
IMMUTABLE	Same input always gives the same output; can be used in an index
STABLE	Consistent within one statement; safe for planner optimisation
VOLATILE (default)	May change anything or return anything

Labelling a function correctly is not cosmetic: only IMMUTABLE functions can back an expression index, and STABLE lets the planner call the function once instead of per row.

PL/pgSQL functions

CREATE OR REPLACE FUNCTION dept_stats(p_dept text,
                                      OUT total bigint,
                                      OUT avg_cgpa numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT count(*), round(avg(cgpa), 2)
    INTO total, avg_cgpa
  FROM students WHERE department = p_dept;
END;
$$;

SELECT * FROM dept_stats('CSE');

Variables, control flow and loops:

CREATE FUNCTION grade_report(p_id int) RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
  v_cgpa numeric;
  v_label text;
BEGIN
  SELECT cgpa INTO v_cgpa FROM students WHERE id = p_id;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Student % not found', p_id USING ERRCODE = 'no_data_found';
  END IF;

  IF    v_cgpa >= 3.75 THEN v_label := 'Excellent';
  ELSIF v_cgpa >= 3.00 THEN v_label := 'Good';
  ELSE  v_label := 'Needs work';
  END IF;

  RETURN v_label;
END;
$$;

Loop forms: FOR i IN 1..10 LOOP, FOR rec IN SELECT … LOOP, WHILE cond LOOP, and LOOP … EXIT WHEN … END LOOP.

Returning sets

CREATE FUNCTION top_students(p_dept text, p_limit int DEFAULT 3)
RETURNS TABLE (id int, name_en text, cgpa numeric)
LANGUAGE sql STABLE
AS $$
  SELECT id, name_en, cgpa FROM students
  WHERE department = p_dept
  ORDER BY cgpa DESC LIMIT p_limit;
$$;

SELECT * FROM top_students('CSE');
SELECT * FROM top_students('EEE', 5);

A set-returning function can be joined like a table, which makes it a genuinely composable building block.

Exception handling

CREATE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric
LANGUAGE plpgsql AS $$
BEGIN
  RETURN a / b;
EXCEPTION
  WHEN division_by_zero THEN
    RAISE NOTICE 'Division by zero, returning NULL';
    RETURN NULL;
END;
$$;

An EXCEPTION block creates an internal savepoint, so it has a real cost — do not wrap a tight loop in one.

Procedures vs functions

Procedures (Postgres 11+) are called with CALL and, unlike functions, can commit and roll back inside their own body:

CREATE PROCEDURE archive_old_logs()
LANGUAGE plpgsql AS $$
DECLARE moved int;
BEGIN
  LOOP
    WITH batch AS (
      DELETE FROM logs WHERE created_at < now() - interval '1 year'
      RETURNING * LIMIT 10000
    )
    INSERT INTO logs_archive SELECT * FROM batch;
    GET DIAGNOSTICS moved = ROW_COUNT;
    EXIT WHEN moved = 0;
    COMMIT;      -- only possible in a procedure
  END LOOP;
END;
$$;

CALL archive_old_logs();

	Function	Procedure
Called with	An expression or SELECT	CALL
Returns	A value, a row or a set	Nothing (or INOUT params)
Transaction control	No	Yes

Managing them

\df
\sf letter_grade
DROP FUNCTION IF EXISTS letter_grade(numeric);

Functions can be overloaded by argument types, so you must give the signature when dropping.