Catalog My learning Blog
PostgreSQL Course: Beginner to Production in 30 Days · Phase 3 · Lesson 15 · 6 min read

PL/pgSQL Functions Tutorial

On this page
  1. What PL/pgSQL functions are for
  2. Step 1: Start with a plain SQL function
  3. Step 2: Add variables and branching with PL/pgSQL
  4. Step 3: Return rows, not just one value
  5. Step 4: Volatility: IMMUTABLE, STABLE and VOLATILE
  6. Step 5: Exception handling in PL/pgSQL
  7. Functions vs procedures
  8. Check yourself

What PL/pgSQL functions are for

By the end of this lesson you'll have a small set of functions for an order system. One computes a price with tax, one returns a customer's stats, one returns a list of rows, and one inserts safely when duplicates are possible. Along the way you'll learn when a plain SQL function is enough and when you need PL/pgSQL, PostgreSQL's built-in procedural language.

Set up two tables first:

SQL
CREATE TABLE customers (
  customer_id  int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email        text NOT NULL UNIQUE,
  full_name    text NOT NULL
);
CREATE TABLE orders (
  order_id     int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id  int NOT NULL REFERENCES customers,
  total        numeric(10,2) NOT NULL
);
INSERT INTO customers (email, full_name) VALUES ('ana@example.com', 'Ana Silva'), ('ben@example.com', 'Ben Ortiz');
INSERT INTO orders (customer_id, total) VALUES (1, 120), (1, 80), (2, 45);

Step 1: Start with a plain SQL function

If the logic fits in one SQL expression, write a LANGUAGE sql function. It's the simplest kind, and the planner can often inline it into the calling query, which makes it the fastest too.

SQL
CREATE FUNCTION add_tax(amount numeric, rate numeric)
RETURNS numeric
LANGUAGE sql IMMUTABLE
RETURN round(amount * (1 + rate), 2);

SELECT add_tax(100, 0.08);   -- 108.00

The IMMUTABLE label is explained in Step 4. The RETURN expression body is the SQL-standard short form, available since PostgreSQL 14.

Step 2: Add variables and branching with PL/pgSQL

Once you need variables, IF statements, loops or error handling, switch to LANGUAGE plpgsql. The body is a block of DECLARE (variables), BEGIN (statements) and END:

SQL
CREATE FUNCTION customer_tier(p_customer_id int)
RETURNS text
LANGUAGE plpgsql STABLE
AS $$
DECLARE
  v_spent numeric;
BEGIN
  SELECT coalesce(sum(total), 0) INTO v_spent
  FROM orders WHERE customer_id = p_customer_id;

  IF NOT EXISTS (SELECT 1 FROM customers WHERE customer_id = p_customer_id) THEN
    RAISE EXCEPTION 'Customer % does not exist', p_customer_id;
  ELSIF v_spent >= 150 THEN
    RETURN 'gold';
  ELSIF v_spent > 0 THEN
    RETURN 'regular';
  ELSE
    RETURN 'new';
  END IF;
END;
$$;

SELECT full_name, customer_tier(customer_id) FROM customers;   -- Ana gold, Ben regular

A few things to notice:

  • The $$ ... $$ quotes the body, so you don't have to escape single quotes inside it.
  • SELECT ... INTO v_spent stores a query result in a variable.
  • RAISE EXCEPTION stops the function with an error. % placeholders are filled from the arguments that follow.
  • The p_ and v_ prefixes aren't required, but they matter. If a variable has the same name as a column used in a query, PostgreSQL raises an "ambiguous" error. Prefixes avoid the clash.

You can type a variable after a column with customers.email%TYPE, or after a whole row with customers%ROWTYPE. The function then follows schema changes automatically.

PL/pgSQL also has loops. FOR r IN SELECT ... LOOP ... END LOOP walks a query result row by row, and FOREACH x IN ARRAY ... walks an array. Reach for them only when you can't express the work as one set-based SQL statement. A loop that updates rows one at a time is far slower than a single UPDATE.

Step 3: Return rows, not just one value

A function can return a whole result set and be used like a table. Declare the output columns with RETURNS TABLE and produce them with RETURN QUERY:

SQL
CREATE FUNCTION top_customers(p_limit int)
RETURNS TABLE (customer_id int, full_name text, spent numeric)
LANGUAGE plpgsql STABLE
AS $$
BEGIN
  RETURN QUERY
  SELECT c.customer_id, c.full_name, sum(o.total)
  FROM customers c
  JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id, c.full_name
  ORDER BY sum(o.total) DESC
  LIMIT p_limit;
END;
$$;

SELECT * FROM top_customers(5);

The output column names (customer_id, full_name) also act as variables inside the function. That's why the query qualifies every column with an alias, c.customer_id, to avoid the ambiguity mentioned above.

Step 4: Volatility: IMMUTABLE, STABLE and VOLATILE

Every function has a volatility label. It's a promise to the planner about what the function does, and the planner optimizes based on it:

LabelPromiseExample
IMMUTABLESame inputs always give the same output, and it never reads tablesadd_tax
STABLEReads the database, but returns the same answer for the same inputs within one statementcustomer_tier
VOLATILE (default)Can change data, or return something different on every callAnything using random() or doing an INSERT

Only IMMUTABLE functions can be used in index expressions (Day 23). The danger is lying. If you mark a function IMMUTABLE but it reads a table, PostgreSQL may cache a stale answer or build an index that's silently wrong. When in doubt, choose the less strict label.

Step 5: Exception handling in PL/pgSQL

An EXCEPTION section catches errors from its block. Catch the specific condition you expect, not everything:

SQL
CREATE FUNCTION add_customer(p_email text, p_name text)
RETURNS text
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO customers (email, full_name) VALUES (p_email, p_name);
  RETURN 'inserted';
EXCEPTION
  WHEN unique_violation THEN
    RETURN 'skipped: email already exists';
END;
$$;

SELECT add_customer('ana@example.com', 'Ana Again');   -- skipped: email already exists

When an error is caught, PostgreSQL undoes whatever the block did, like an automatic savepoint (Day 12). That safety has a cost, so keep EXCEPTION blocks small and around the statement that can fail. Catching OTHERS hides bugs you'd want to know about. If you use it, at least log the error with SQLERRM. For this particular case, INSERT ... ON CONFLICT DO NOTHING (Day 6) is simpler still.

Functions vs procedures

A function returns a value and runs inside the caller's transaction. A procedure, created with CREATE PROCEDURE and run with CALL, can COMMIT partway through. That makes procedures the tool for long batch jobs that should save progress as they go:

SQL
CREATE PROCEDURE purge_small_orders(p_batch int)
LANGUAGE plpgsql
AS $$
BEGIN
  LOOP
    DELETE FROM orders WHERE order_id IN (
      SELECT order_id FROM orders WHERE total < 50 LIMIT p_batch);
    EXIT WHEN NOT FOUND;
    COMMIT;   -- allowed in a procedure, not in a function
  END LOOP;
END;
$$;

CALL purge_small_orders(1000);

One security note. Functions run with the caller's privileges by default. SECURITY DEFINER makes them run with the owner's privileges instead, which is useful for tightly controlled operations. If you use it, set the search_path on the function (SET search_path = pg_catalog, public) for the reasons covered on Day 14.

Check yourself

IMMUTABLE promised the result never depends on table data, so PostgreSQL may have reused earlier results. Mark it STABLE, which allows reading tables.

A parameter, variable or RETURNS TABLE column has the same name as a column in a query inside the function. Qualify the column with a table alias, or rename the variable with a prefix like p_ or v_.

Make it a procedure that deletes in batches and COMMITs after each one. Locks are released after every batch, and the work done so far survives a failure.

When it's a single query or expression. SQL functions are simpler, and the planner can often inline them into the calling query. Use PL/pgSQL when you need variables, loops, branching or exception handling.

A function returns a value and runs inside the caller's transaction, so it can't commit. A procedure is called with CALL, returns no value in the usual way, and can COMMIT or ROLLBACK partway through, which makes it suitable for batch jobs that process work in chunks.