PostgreSQL functions, procedures and PL/pgSQL

You can store your own logic in the database. A function returns a value (or a table) and can be used inside queries. A procedure is called with CALL and may commit transactions itself.

Write simple functions in plain LANGUAGE sql. For variables, IF, loops and error handling use PL/pgSQL, PostgreSQL’s procedural language. The body sits between $$ ... $$ (dollar quoting, so you don’t need to escape single quotes).

DECLARE variables; assign with := or SELECT ... INTO var.
IF ... THEN ... ELSIF ... ELSE ... END IF;, FOR r IN SELECT ... LOOP ... END LOOP;, WHILE.
RAISE NOTICE 'x = %', x; prints messages; RAISE EXCEPTION aborts with an error.
RETURNS TABLE (...) with RETURN QUERY returns rows.
Volatility labels (IMMUTABLE, STABLE, VOLATILE) tell the planner how much it can optimise.

DO $$ ... $$ runs an anonymous block once, without saving a function.

Syntax

CREATE OR REPLACE FUNCTION name(arg type) RETURNS type
LANGUAGE plpgsql AS $$
DECLARE
  v type;
BEGIN
  ...
  RETURN v;
END;
$$;

Examples

Edit the code and run it. Output is printed the way psql prints it.

24.1A simple SQL function

CREATE OR REPLACE FUNCTION order_total(p_order_id integer)
RETURNS numeric
LANGUAGE sql STABLE
AS $$
  SELECT coalesce(sum(quantity * unit_price), 0)
  FROM order_items
  WHERE order_id = p_order_id;
$$;

SELECT id, status, order_total(id) AS total
FROM orders
ORDER BY total DESC
LIMIT 5;

The function wraps a query that takes a parameter, so it can be called per row like any built-in. STABLE tells PostgreSQL the result won’t change within one statement.

24.2PL/pgSQL with IF and variables

CREATE OR REPLACE FUNCTION customer_level(p_customer_id integer)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_spend numeric;
BEGIN
  SELECT coalesce(sum(oi.quantity * oi.unit_price), 0) INTO v_spend
  FROM orders o JOIN order_items oi ON oi.order_id = o.id
  WHERE o.customer_id = p_customer_id AND o.status <> 'cancelled';

  IF v_spend >= 1500 THEN
    RETURN 'gold';
  ELSIF v_spend >= 300 THEN
    RETURN 'silver';
  ELSE
    RETURN 'bronze';
  END IF;
END;
$$;

SELECT name, customer_level(id) AS level FROM customers ORDER BY id;

SELECT ... INTO v_spend stores a query result in a variable; IF/ELSIF/ELSE decides what to return.

24.3Loops and RAISE NOTICE

DO $$
DECLARE
  r record;
  running numeric := 0;
BEGIN
  FOR r IN SELECT name, price FROM products WHERE category = 'Audio' ORDER BY price LOOP
    running := running + r.price;
    RAISE NOTICE '% costs %, running total %', r.name, r.price, running;
  END LOOP;
END;
$$;

A DO block runs once. The loop visits each row returned by the query; RAISE NOTICE messages show up under the result, with each % replaced by the next argument.

24.4Returning a table

CREATE OR REPLACE FUNCTION top_products(p_limit integer DEFAULT 3)
RETURNS TABLE (product text, units bigint, revenue numeric)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT p.name, sum(oi.quantity), sum(oi.quantity * oi.unit_price)
  FROM order_items oi JOIN products p ON p.id = oi.product_id
  GROUP BY p.name
  ORDER BY 3 DESC
  LIMIT p_limit;
END;
$$;

SELECT * FROM top_products();
SELECT * FROM top_products(5);

RETURNS TABLE declares output columns, and the function is used in FROM like a table. DEFAULT 3 makes the argument optional.

24.5A procedure with validation

CREATE OR REPLACE PROCEDURE restock(p_product_id integer, p_qty integer)
LANGUAGE plpgsql
AS $$
BEGIN
  IF p_qty <= 0 THEN
    RAISE EXCEPTION 'Quantity must be positive, got %', p_qty;
  END IF;
  UPDATE products SET stock = stock + p_qty WHERE id = p_product_id;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'No product with id %', p_product_id;
  END IF;
END;
$$;

CALL restock(7, 25);
SELECT id, name, stock FROM products WHERE id = 7;

FOUND is set after each statement; here it tells us whether the UPDATE touched a row. Try CALL restock(7, -5); or CALL restock(999, 5); to see the custom errors.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 24.1

Create a function product_count(p_category text) that returns the number of products in that category as an integer.

Hint

LANGUAGE sql, SELECT count(*)::int FROM products WHERE category = p_category

Solution

CREATE FUNCTION product_count(p_category text) RETURNS integer LANGUAGE sql AS $$ SELECT count(*)::int FROM products WHERE category = p_category $$;