Skip to content
Tutorials Center
My learning

Topics

Lesson 50 of 90 ยท Functions, PL/pgSQL and Triggers

PL/pgSQL Basics

Learn PL/pgSQL, PostgreSQL's procedural language: blocks, DECLARE, IF and CASE, loops, RAISE NOTICE, RETURN QUERY and dollar quoting, with clear examples.

Plain SQL describes what you want. Sometimes you need to describe how: compute a value in steps, branch on a condition, loop over rows, or stop with a clear error message. PL/pgSQL is PostgreSQL's built-in procedural language for exactly that. It adds variables, IF, loops, error handling and messages to ordinary SQL, and it runs inside the server, right next to your data.

A PL/pgSQL program is stored in the database as a function, a procedure, a trigger function or an anonymous DO block. You write SQL statements inside it as usual, and the language wraps them with control flow. PL/pgSQL ships with every PostgreSQL installation and is enabled by default, so there is nothing to install.

What runs live here. The in-browser engine on this site is SQLite, which has no procedural language. Every PL/pgSQL block below is a read-only listing that you would run in psql. The two live examples show the same ideas as set-based SQL (CASE and a recursive CTE), which is often the better way to write them anyway.

The structure of a block

Every PL/pgSQL body is a block. A block has an optional declaration section and a mandatory body:

SQL
DO $$
DECLARE
  greeting text := 'Hello';
  visitors int;
BEGIN
  visitors := 3;
  RAISE NOTICE '% to % visitors', greeting, visitors;
END;
$$;

Running it prints NOTICE: Hello to 3 visitors in psql. Here is what each part does:

  • DO $$ ... $$ runs an anonymous block once, with no function to create. It is handy for experiments and one-off scripts.
  • DECLARE introduces variables, each with a type and an optional starting value. Use := (or =) for assignment. A variable with no initial value starts as NULL.
  • BEGIN ... END; holds the statements. Every statement ends with a semicolon, including the last one before END.
  • RAISE NOTICE prints a message. % inside the format string is replaced by the next argument, in order.

The dollar signs are dollar quoting. The server stores the body as a string, and $$ marks where that string starts and ends. Because apostrophes need no escaping inside $$ ... $$, you can write normal SQL strings in the body. If the body itself contains $$, give the outer quotes a tag, such as $body$ ... $body$.

You can nest blocks. A variable declared in an inner block hides one with the same name from an outer block, and the inner block has its own EXCEPTION handler (see the next lesson).

Variables and types

PL/pgSQL variables use any PostgreSQL type. Three special type forms save you from repeating column definitions:

SQL
DECLARE
  v_price   products.price%TYPE;     -- same type as the column
  v_product products%ROWTYPE;        -- a whole row of the table
  v_any     record;                  -- a row of unknown shape

%TYPE keeps a variable in step with the column if you later change the column from integer to bigint. %ROWTYPE holds a full row, and you read fields with a dot: v_product.name. A record variable takes its shape from the first query that fills it.

To fill variables from a query, use SELECT ... INTO:

SQL
SELECT price, name INTO v_price, v_name
FROM products
WHERE id = 7;

If the query returns no row, the variables become NULL. If it returns several rows, only the first is used, silently. Add STRICT (SELECT ... INTO STRICT v_price) when you expect exactly one row: PostgreSQL then raises an error for zero rows (NO_DATA_FOUND) or for more than one (TOO_MANY_ROWS). The special variable FOUND becomes true after a SELECT INTO, UPDATE, DELETE or FETCH that affected at least one row, which gives you a lightweight check without STRICT.

Be careful with names: if a variable has the same name as a column used in the same query, PostgreSQL reports an ambiguous reference. The usual fix is a naming convention, for instance a v_ prefix for variables and p_ for parameters.

Branching with IF and CASE

The IF statement has the forms you would expect:

SQL
IF v_stock = 0 THEN
  v_label := 'sold out';
ELSIF v_stock < 5 THEN
  v_label := 'low';
ELSE
  v_label := 'ok';
END IF;

Note the spelling ELSIF (not ELSEIF), and that END IF; is two words with a semicolon. PL/pgSQL also has a CASE statement, with a simple form and a searched form:

SQL
CASE
  WHEN v_total >= 100 THEN v_shipping := 0;
  WHEN v_total >= 50  THEN v_shipping := 3.50;
  ELSE                     v_shipping := 6.90;
END CASE;

Unlike the SQL CASE expression, the statement version raises an error if no branch matches and there is no ELSE. That is a feature: it turns a forgotten case into a loud failure rather than a silent NULL.

Because a classification is really a calculation, the set-based way is usually just SQL. Run this live:

SQL IF and CASE as plain SQL

Press Run (or Ctrl + Enter) to see the result here.

Totals of 50 and above ship cheaper, and 100 and above ship free. This single statement handles every row at once, while a PL/pgSQL loop would handle them one at a time.

Loops

PL/pgSQL has several loop forms. The plain LOOP repeats until you leave it:

SQL
LOOP
  v_n := v_n + 1;
  EXIT WHEN v_n >= 10;
END LOOP;

WHILE condition LOOP ... END LOOP; tests first. A numeric FOR counts:

SQL
FOR i IN 1..5 LOOP
  RAISE NOTICE 'step %', i;
END LOOP;

FOR i IN REVERSE 10..1 BY 3 LOOP   -- 10, 7, 4, 1
  RAISE NOTICE 'countdown %', i;
END LOOP;

The loop variable i is declared implicitly. The most useful form walks through the rows of a query:

SQL
FOR rec IN SELECT id, email FROM users WHERE active LOOP
  PERFORM send_reminder(rec.id, rec.email);
END LOOP;

PERFORM runs a query or function call and discards the result. You use it instead of SELECT whenever a PL/pgSQL statement does not need a value back. Inside any loop, CONTINUE skips to the next iteration and EXIT leaves the loop; both accept a WHEN condition and an optional label for nested loops.

Row-by-row loops are convenient, but they are slow compared with a single set-based statement, because every iteration is a separate trip into the SQL executor. Reach for a loop when each row needs genuinely different handling, such as calling an external routine. For arithmetic on many rows, write one UPDATE or INSERT ... SELECT.

The next example is the set-based replacement for a counting LOOP: a recursive CTE that builds the factorials 1! to 10!.

SQL A loop as a recursive CTE

Press Run (or Ctrl + Enter) to see the result here.

The anchor row is (1, 1), and each pass multiplies the previous value by the next number, exactly like v_value := v_value * i inside a loop. The WHERE n < 10 plays the role of the EXIT WHEN.

Loops in depth: WHILE, FOREACH, EXIT, CONTINUE and labels

The overview above shows the basic forms. Here is the complete set, with the details that trip people up.

WHILE tests its condition before each pass, so the body may never run:

SQL
-- PostgreSQL
DECLARE
  v_balance numeric := 1000;
  v_years   int := 0;
BEGIN
  WHILE v_balance < 2000 LOOP
    v_balance := v_balance * 1.05;
    v_years := v_years + 1;
  END LOOP;
  RAISE NOTICE 'doubled after % years', v_years;
END;

FOR ... IN ... BY counts up (or down with REVERSE). In the reversed form you still write the smaller bound last: FOR i IN REVERSE 10..1. The bounds are evaluated once, before the loop starts, and the loop variable is a new local variable that hides any outer variable with the same name and cannot be assigned inside the loop.

FOREACH walks the elements of an array, which is cleaner than indexing with FOR i IN 1..array_length(...):

SQL
-- PostgreSQL
DECLARE
  v_tags  text[] := ARRAY['sql', 'jsonb', 'wal'];
  v_tag   text;
  v_grid  int[]  := ARRAY[[1,2],[3,4]];
  v_row   int[];
BEGIN
  FOREACH v_tag IN ARRAY v_tags LOOP
    RAISE NOTICE 'tag: %', v_tag;
  END LOOP;

  -- SLICE 1 returns one-dimensional slices (the rows) of a 2-D array
  FOREACH v_row SLICE 1 IN ARRAY v_grid LOOP
    RAISE NOTICE 'row: %', v_row;          -- {1,2} then {3,4}
  END LOOP;
END;

Without SLICE, FOREACH visits every element of a multi-dimensional array in storage order, one by one. For a jsonb array, loop over jsonb_array_elements(doc) with a FOR ... IN SELECT instead.

FOR ... IN SELECT and FOR ... IN EXECUTE loop over query rows; the second one builds the SQL text dynamically (use format('%I', ...) and USING parameters, see PL/pgSQL Advanced). After a query loop the special variable FOUND is true if the loop ran at least once.

EXIT and CONTINUE control any loop kind. Both take an optional WHEN condition:

SQL
-- PostgreSQL
FOR rec IN SELECT id, status FROM jobs ORDER BY id LOOP
  CONTINUE WHEN rec.status = 'done';      -- skip finished jobs
  EXIT WHEN rec.status = 'poison';        -- stop at the first bad one
  PERFORM run_job(rec.id);
END LOOP;

Labels let an inner loop control an outer one. Put <<name>> before the loop and refer to it:

SQL
-- PostgreSQL
<<rows>>
FOR r IN 1..3 LOOP
  <<cols>>
  FOR c IN 1..3 LOOP
    CONTINUE rows WHEN c > r;             -- jump to the next r
    EXIT rows WHEN r * c = 6;             -- leave both loops
    RAISE NOTICE 'r=%, c=%', r, c;
  END LOOP cols;
END LOOP rows;

Without a label, EXIT and CONTINUE only affect the innermost loop. A label can also name a plain BEGIN ... END block, and then EXIT label; leaves that block, which is a neat way to skip the rest of a block without raising an error.

PL/pgSQL has no break or goto, so labels are the only way out of nested loops. Python has no labels either, so the program below uses a flag to do the same job. It prints exactly what the labelled example above would raise as notices:

Python What the labelled loops visit

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

If a loop only transforms rows, replace it with one set-based statement. Reserve loops for work that must happen row by row, such as calling a function with side effects, and prefer FOREACH over index arithmetic when walking arrays.

Messages and errors with RAISE

RAISE reports information or stops execution:

SQL
RAISE NOTICE  'processed % rows', v_count;      -- shown to the client
RAISE WARNING 'price is zero for product %', p_id;
RAISE EXCEPTION 'stock cannot be negative (got %)', p_qty
  USING ERRCODE = 'check_violation',
        HINT = 'Use a returns entry instead.';

DEBUG, LOG, INFO, NOTICE and WARNING only produce messages. EXCEPTION (the default level) aborts the current statement and rolls back its transaction unless a surrounding block handles it. Always give business-rule errors a meaningful ERRCODE so callers can catch them precisely; the next lesson shows how.

For a quick sanity check there is also ASSERT condition, 'message';, which can be switched off with a setting in production.

Functions that return rows

A PL/pgSQL function that returns a table builds its result either row by row with RETURN NEXT or in one go with RETURN QUERY:

SQL
CREATE FUNCTION low_stock(threshold int DEFAULT 5)
RETURNS TABLE (product_id int, name text, stock int)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
  RETURN QUERY
    SELECT p.id, p.name, p.stock
    FROM products AS p
    WHERE p.stock < threshold
    ORDER BY p.stock;
END;
$$;

SELECT * FROM low_stock(3);

RETURN QUERY appends the rows of a query to the result; you can use it several times and add rows with RETURN NEXT in between. A bare RETURN; ends the function. The output columns from RETURNS TABLE are variables inside the function, so qualify column names with a table alias (p.stock) to avoid ambiguity.

A function that returns a single value ends with RETURN expression;:

SQL
CREATE FUNCTION shipping_cost(p_total numeric)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF p_total IS NULL THEN
    RETURN NULL;
  ELSIF p_total >= 100 THEN
    RETURN 0;
  ELSIF p_total >= 50 THEN
    RETURN 3.50;
  END IF;
  RETURN 6.90;
END;
$$;

This example could be a SQL function with a CASE expression, which the planner could inline. Prefer plain SQL for logic that fits in one expression, and use PL/pgSQL for logic with real control flow, intermediate variables or error handling.

Practical habits

  • Keep functions small and named for what they do. A function that does one thing is easy to test with SELECT.
  • Give it the right volatility. The labels from SQL Functions and Procedures apply to PL/pgSQL as well; the default VOLATILE is correct for anything that writes.
  • Use STRICT on lookups so surprises raise errors instead of returning NULL.
  • Prefer set-based SQL inside the function. Use loops only when you must.
  • Test in a transaction. Run BEGIN;, call the function, inspect the result, then ROLLBACK;.
  • Mind the plan cache. PL/pgSQL caches the plans of its embedded statements per session. That is usually a speed-up, but data that changes shape dramatically can make a cached plan poor. The statistics lesson explains generic and custom plans.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does the dollar quoting in AS $$ ... $$ do?

    Dollar quotes delimit a string literal, so apostrophes in the code need no doubling. Tagged forms such as $body$ allow $$ to appear inside the body.
  2. 2. What is the difference between SELECT ... INTO v and SELECT ... INTO STRICT v?

    Without STRICT, zero rows set the variable to NULL and extra rows are silently ignored. With STRICT, PostgreSQL raises NO_DATA_FOUND or TOO_MANY_ROWS.
  3. 3. What happens in a PL/pgSQL CASE statement (not the SQL expression) when no WHEN matches and there is no ELSE?

    The CASE statement is strict about completeness. A missing ELSE with no matching branch raises an error, which catches forgotten cases early.
  4. 4. When should you prefer a single set-based UPDATE over a FOR rec IN SELECT ... LOOP that updates each row?

    A single statement is planned and executed as a whole. A loop runs a separate statement per row, which is much slower on large tables.

Summary

  • A PL/pgSQL body is a block: optional DECLARE, then BEGIN ... END;. DO $$ ... $$ runs one anonymously.
  • Variables have SQL types; %TYPE and %ROWTYPE track table definitions, and SELECT ... INTO STRICT insists on exactly one row.
  • Control flow is IF/ELSIF/ELSE, CASE (which errors if nothing matches), LOOP, WHILE and FOR, with EXIT and CONTINUE.
  • RAISE NOTICE prints and RAISE EXCEPTION aborts, optionally with an ERRCODE.
  • Functions return rows with RETURN QUERY or RETURN NEXT, and single values with RETURN.
  • Prefer set-based SQL to loops, and keep PL/pgSQL for real control flow. The live examples here used CASE and a recursive CTE instead of a procedural language.

Next: PL/pgSQL: Cursors, Dynamic SQL and Errors, where you will build queries at run time and handle exceptions.