Postgres by Example: Functions and PL/pgSQL

PostgreSQL lets you define your own functions. The simplest are SQL functions — a function body that is one or more SQL statements; PostgreSQL can often inline them. The more powerful are PL/pgSQL functions — written in a procedural language built into the server, with variables, loops, conditions, and exceptions. PL/pgSQL is what you use for triggers, complex business logic, and one-off maintenance routines. There are also PL/Python, PL/Perl, and others, but PL/pgSQL is always available and is what you should learn first.

What you'll learn:

• The CREATE FUNCTION syntax
• The difference between LANGUAGE sql and LANGUAGE plpgsql
• Volatility categories: IMMUTABLE, STABLE, VOLATILE
• Variables, IF, LOOP, and RAISE NOTICE in PL/pgSQL
• SECURITY DEFINER and the search_path warning

-- A simple SQL function
CREATE OR REPLACE FUNCTION square(x integer)
RETURNS integer
LANGUAGE sql
IMMUTABLE
AS $$
  SELECT x * x;
$$;

SELECT square(7) AS result;

-- A SQL function that returns a set (multiple rows)
CREATE OR REPLACE FUNCTION recent_orders(min_total numeric)
RETURNS TABLE (id integer, customer_id integer, total numeric)
LANGUAGE sql
STABLE
AS $$
  SELECT id, customer_id, total
  FROM orders_example
  WHERE total >= min_total
  ORDER BY id;
$$;

SELECT * FROM recent_orders(20);

-- A PL/pgSQL function with control flow
CREATE OR REPLACE FUNCTION classify_total(total numeric)
RETURNS text
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  result text;
BEGIN
  IF total IS NULL THEN
    result := 'unknown';
  ELSIF total < 20 THEN
    result := 'small';
  ELSIF total < 100 THEN
    result := 'medium';
  ELSE
    result := 'large';
  END IF;
  RETURN result;
END;
$$;

SELECT id, total, classify_total(total) AS bucket
FROM orders_example
ORDER BY id;

-- A function that loops and uses RAISE NOTICE (a print statement)
CREATE OR REPLACE FUNCTION ping(n integer)
RETURNS integer
LANGUAGE plpgsql
AS $$
DECLARE
  i integer;
BEGIN
  FOR i IN 1..n LOOP
    RAISE NOTICE 'ping %', i;
  END LOOP;
  RETURN n;
END;
$$;

SELECT ping(3);

-- Cleanup
DROP FUNCTION IF EXISTS square(integer);
DROP FUNCTION IF EXISTS recent_orders(numeric);
DROP FUNCTION IF EXISTS classify_total(numeric);
DROP FUNCTION IF EXISTS ping(integer);

A function declaration includes a name, argument list, return type, language, optional volatility, and body. The body is bracketed by dollar quotes ($$ ... $$) — these avoid the need to escape quotes inside the body. You can use $tag$ ... $tag$ if you ever need nested dollar quotes.

The volatility categories are critical for performance:

• IMMUTABLE: same inputs always produce the same output, no database state involved. The planner can constant-fold these into expressions and even pre-evaluate them at planning time. Use for math, string formatting, deterministic transforms.
• STABLE: same inputs produce the same output within a single statement, but may differ across statements (e.g. functions that read tables). The planner can cache the result for the statement.
• VOLATILE (default): may return different results even within one statement (e.g. random(), now(), anything that modifies data). The planner re-evaluates every time.

Wrong volatility leads to wrong query plans — most beginners leave everything VOLATILE and pay the cost. Mark functions as IMMUTABLE / STABLE whenever true.

To run:

$ psql -f source/functions-and-plpgsql.sql postgres
CREATE FUNCTION
 result
--------
     49
(1 row)
 ...
NOTICE:  ping 1
NOTICE:  ping 2
NOTICE:  ping 3
 ping
------
    3
(1 row)

Common pitfalls:

• Defining a SECURITY DEFINER function (runs as the function owner) without setting search_path is a known privilege-escalation pattern. Always include SET search_path = pg_catalog, public in such functions.
• Marking a function IMMUTABLE when it actually reads tables — the planner caches a wrong value. Reserve IMMUTABLE for true math/string functions.
• Overloading by argument types is allowed but easy to abuse. recent_orders(numeric) and recent_orders(integer) are different functions; small calls may unexpectedly target the wrong one.
• PL/pgSQL is line-oriented and case-insensitive. Be careful with reserved words inside the function body.

Tip: Stored procedures (CREATE PROCEDURE, PostgreSQL 11+) are like functions but can manage transactions (COMMIT, ROLLBACK inside the body). Use functions for things that return values; procedures for batch operations that need transaction control.

Try it: Write an IMMUTABLE SQL function discount(price, pct) that returns the discounted price. Apply it in a query: SELECT id, total, discount(total, 10) FROM orders_example;. Then write a PL/pgSQL function that returns the largest order amount per customer using a loop or a single SQL query (the SQL form is much faster — try both to see).

Source: functions-and-plpgsql.sql

Next: Triggers

Home: Postgres by Example