PL/pgSQL Functions & Procedures

Some logic belongs next to the data: calculating an order total the same way everywhere, applying a discount rule, or archiving old rows every night. PostgreSQL lets you store that logic in the database as functions and procedures, written in plain SQL or in PL/pgSQL, PostgreSQL's procedural language with variables, conditions and loops.

In this lesson you will write SQL functions, PL/pgSQL functions with IF, loops and error handling, functions that return tables, and procedures that can commit work in steps. You will also learn the volatility labels that tell the planner how a function behaves.

A simple SQL function

The easiest functions are written in the sql language: the body is one or more SQL statements, and the last one gives the result. Parameters are referred to by name:

CREATE FUNCTION order_total(p_order_id int)
RETURNS numeric
LANGUAGE sql
STABLE
RETURN (SELECT coalesce(sum(quantity * unit_price), 0)
        FROM order_items
        WHERE order_id = p_order_id);

SELECT order_id, status, order_total(order_id) AS total
FROM orders
WHERE customer_id = 1
ORDER BY order_id;

Query OK
order_id | status    | total
---------+-----------+--------
101      | delivered | 2147.00
104      | delivered | 8999.00
115      | pending   | 3798.00
(3 rows)

The RETURN expression form (a "SQL-standard function body", available since PostgreSQL 14) is checked when the function is created, so a typo in a table name fails at once. Prefixing parameters with p_ avoids confusion with column names. Older code writes the body as a string instead: AS $$ SELECT ... $$, which you will also see below.

Volatility: IMMUTABLE, STABLE, VOLATILE

Every function has a volatility label that tells the planner how much it can trust the result:

Correct labels let PostgreSQL optimise calls, and only IMMUTABLE functions can be used in index expressions. Labelling a function IMMUTABLE when it reads a table is a bug that gives wrong results, so when unsure, leave the default.

PL/pgSQL: variables, IF and RAISE

For logic with several steps, use LANGUAGE plpgsql. The body is a block of DECLARE (variables), BEGIN ... END. It is written inside dollar quotes $$ ... $$, which work like single quotes but let you use ' freely inside:

CREATE FUNCTION loyalty_tier(p_customer_id int)
RETURNS text
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  v_spent numeric;
  v_tier text;
BEGIN
  SELECT coalesce(sum(oi.quantity * oi.unit_price), 0)
  INTO v_spent
  FROM orders AS o
  JOIN order_items AS oi ON oi.order_id = o.order_id
  WHERE o.customer_id = p_customer_id AND o.status <> 'cancelled';

  IF v_spent >= 20000 THEN
    v_tier := 'Gold';
  ELSIF v_spent >= 5000 THEN
    v_tier := 'Silver';
  ELSE
    v_tier := 'Bronze';
  END IF;

  RAISE NOTICE 'Customer % spent %', p_customer_id, v_spent;
  RETURN v_tier;
END;
$$;

SELECT loyalty_tier(8) AS sneha, loyalty_tier(5) AS kabir;

Query OK
NOTICE:  Customer 8 spent 32797.00
NOTICE:  Customer 5 spent 3948.00
sneha | kabir
------+-------
Gold  | Bronze
(1 row)

SELECT ... INTO variable stores a query result in a variable, := assigns a value, and RAISE NOTICE prints a message, where each % is replaced by the next argument. Notices are handy for debugging; RAISE EXCEPTION stops the function with an error.

Returning a table

A function can return many rows with RETURNS TABLE (...). In PL/pgSQL, RETURN QUERY adds the rows of a query to the result. You then call the function in FROM, like a table:

CREATE FUNCTION low_stock(p_limit int DEFAULT 30)
RETURNS TABLE (product_name text, stock int, reorder_qty int)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN QUERY
    SELECT p.product_name::text, p.stock, (p_limit * 2 - p.stock)
    FROM products AS p
    WHERE p.stock < p_limit
    ORDER BY p.stock;
END;
$$;

SELECT * FROM low_stock();
SELECT * FROM low_stock(p_limit => 15);

Query OK
product_name                | stock | reorder_qty
----------------------------+-------+------------
HD Webcam                   | 0     | 60
Graphics Tablet             | 12    | 48
27-inch Monitor             | 18    | 42
Noise-Cancelling Headphones | 25    | 35
(4 rows)

product_name    | stock | reorder_qty
----------------+-------+------------
HD Webcam       | 0     | 30
Graphics Tablet | 12    | 18
(2 rows)

DEFAULT 30 makes the parameter optional, and p_limit => 15 passes it by name. The ::text cast matters: the declared result columns must match the query's column types exactly, and product_name is varchar(60).

Loops and error handling

PL/pgSQL has FOR row IN query LOOP, WHILE, and FOREACH over arrays. An EXCEPTION section catches errors so the function can react instead of failing. Here a DO block (an anonymous, one-off PL/pgSQL block, not saved as a function) tries to sell items and reports the ones that fail:

ALTER TABLE products ADD CONSTRAINT stock_not_negative CHECK (stock >= 0);

DO $$
DECLARE
  r record;
BEGIN
  FOR r IN SELECT product_id, product_name FROM products WHERE product_id IN (7, 8, 12) ORDER BY product_id LOOP
    BEGIN
      UPDATE products SET stock = stock - 1 WHERE product_id = r.product_id;
      RAISE NOTICE 'Sold one %', r.product_name;
    EXCEPTION WHEN check_violation THEN
      RAISE NOTICE 'Cannot sell %: out of stock', r.product_name;
    END;
  END LOOP;
END;
$$;

SELECT product_id, stock FROM products WHERE product_id IN (7, 8, 12) ORDER BY product_id;

Query OK
NOTICE:  Sold one Bluetooth Speaker
NOTICE:  Cannot sell HD Webcam: out of stock
NOTICE:  Sold one Graphics Tablet
Query OK
product_id | stock
-----------+------
7          | 39
8          | 0
12         | 11
(3 rows)

The inner BEGIN ... EXCEPTION ... END block works like a savepoint: the failed UPDATE for the webcam is undone, and the loop carries on. Exception blocks have a cost, so use them where you really need recovery, not around every statement.

Procedures

A procedure (PostgreSQL 11 and later) is like a function that returns nothing, is run with CALL, and may COMMIT inside its body. That makes it suitable for long batch jobs that should save progress in chunks:

CREATE PROCEDURE archive_old_orders(p_before date)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO orders_archive SELECT * FROM orders WHERE order_date < p_before;
  COMMIT;
  DELETE FROM order_items WHERE order_id IN (SELECT order_id FROM orders_archive);
  DELETE FROM orders WHERE order_date < p_before;
  COMMIT;
END;
$$;

CALL archive_old_orders('2025-02-01');

A function cannot commit, because it always runs inside the caller's statement. Use functions for calculations and queries, procedures for jobs that change data in steps.

Dynamic SQL

When a table or column name is only known at run time, build the statement with format() and run it with EXECUTE. Always use %I for names and %L (or USING parameters) for values, so the text cannot break your SQL:

EXECUTE format('SELECT count(*) FROM %I WHERE city = %L', 'customers', 'Kochi') INTO v_count;

Common mistakes

• Parameter names that clash with column names. WHERE customer_id = customer_id is always true. Use a prefix such as p_.
• Wrong volatility. IMMUTABLE on a function that reads tables gives stale results. Use STABLE or the default.
• Mismatched column types in RETURNS TABLE. Cast in the query (::text, ::int).
• Building dynamic SQL with ||. Use format() with %I and %L, or EXECUTE ... USING.
• Trying to COMMIT in a function. Only procedures called with CALL can commit.

Key points

• LANGUAGE sql functions are best for single queries; LANGUAGE plpgsql adds variables, IF, loops and exceptions.
• Dollar quotes $$ ... $$ hold function bodies without escaping quotes.
• Label volatility correctly: IMMUTABLE, STABLE or VOLATILE.
• RETURNS TABLE with RETURN QUERY makes functions you can use in FROM.
• EXCEPTION WHEN ... THEN catches errors in a block; RAISE NOTICE and RAISE EXCEPTION report.
• Procedures run with CALL and may COMMIT; DO runs a one-off block.

CREATE FUNCTION gst_price(p_price numeric, p_rate numeric DEFAULT 18)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF p_rate < 0 THEN
    RAISE EXCEPTION 'GST rate cannot be negative: %', p_rate;
  END IF;
  RETURN round(p_price * (1 + p_rate / 100), 2);
END;
$$;

SELECT product_name, price, gst_price(price) AS price_with_gst
FROM products
WHERE category = 'Audio';