PL/pgSQL Functions

While SQL-language functions are great for simple queries, many real-world tasks need variables, conditionals, loops, and error handling. That is where PL/pgSQL comes in -- PostgreSQL's built-in procedural language that extends SQL with full programming constructs.

Anatomy of a PL/pgSQL Function

CREATE FUNCTION function_name(parameters)
RETURNS return_type
LANGUAGE plpgsql
AS $$
DECLARE
  -- variable declarations
BEGIN
  -- function body: statements, control flow, queries
  RETURN something;
END;
$$;

Key differences from SQL functions:

• The language is plpgsql, not sql.
• You have a DECLARE section for local variables.
• The body is wrapped in BEGIN ... END;.
• You explicitly RETURN a value (or use RETURN NEXT / RETURN QUERY).

Declaring Variables

Variables are declared between DECLARE and BEGIN:

CREATE FUNCTION calculate_bonus(emp_id INT)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  emp_salary   NUMERIC;
  bonus_rate   NUMERIC := 0.10;   -- default value
  bonus_amount NUMERIC;
  emp_name     TEXT;
BEGIN
  SELECT salary, name
    INTO emp_salary, emp_name
    FROM employees
    WHERE id = emp_id;

  bonus_amount := emp_salary * bonus_rate;
  RETURN bonus_amount;
END;
$$;

Variable Types

DECLARE
  counter      INTEGER := 0;          -- explicit type with default
  label        TEXT;                   -- NULL by default
  created      TIMESTAMP := NOW();    -- expression default
  row_data     employees%ROWTYPE;     -- matches the employees table row
  col_val      employees.salary%TYPE; -- matches the salary column type
  is_valid     BOOLEAN := FALSE;
  items        TEXT[] := '{}';        -- empty array

• %ROWTYPE -- the variable holds an entire row of the named table.
• %TYPE -- the variable matches the data type of a specific column.

These keep your function in sync if the table schema changes.

SELECT INTO

Use SELECT INTO to capture query results into variables:

DECLARE
  total NUMERIC;
  order_count INTEGER;
BEGIN
  SELECT SUM(total_price), COUNT(*)
    INTO total, order_count
    FROM orders
    WHERE status = 'completed';

  -- Check if the query found anything
  IF NOT FOUND THEN
    RAISE NOTICE 'No completed orders found';
    RETURN 0;
  END IF;

  RETURN total / order_count;
END;

The special variable FOUND is TRUE if the last query affected or returned at least one row.

Conditional Logic: IF / ELSIF / ELSE

CREATE FUNCTION price_category(amount NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  IF amount >= 1000 THEN
    RETURN 'premium';
  ELSIF amount >= 100 THEN
    RETURN 'standard';
  ELSIF amount > 0 THEN
    RETURN 'budget';
  ELSE
    RETURN 'free';
  END IF;
END;
$$;

Note: it is ELSIF (not ELSEIF or ELIF).

CASE Statements

PL/pgSQL also supports CASE:

CREATE FUNCTION day_type(d DATE)
RETURNS TEXT
LANGUAGE plpgsql
IMMUTABLE
AS $$
BEGIN
  CASE EXTRACT(DOW FROM d)
    WHEN 0, 6 THEN
      RETURN 'weekend';
    WHEN 1, 5 THEN
      RETURN 'near-weekend';
    ELSE
      RETURN 'midweek';
  END CASE;
END;
$$;

Loops

PL/pgSQL has several loop constructs.

Basic LOOP with EXIT

CREATE FUNCTION countdown(start_val INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  result TEXT := '';
  i INT := start_val;
BEGIN
  LOOP
    EXIT WHEN i <= 0;   -- break out of the loop
    result := result || i::TEXT || ' ';
    i := i - 1;
  END LOOP;
  RETURN TRIM(result);
END;
$$;

SELECT countdown(5);
-- Returns: '5 4 3 2 1'

FOR Loop (Integer Range)

CREATE FUNCTION factorial(n INT)
RETURNS BIGINT
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  result BIGINT := 1;
BEGIN
  FOR i IN 1..n LOOP
    result := result * i;
  END LOOP;
  RETURN result;
END;
$$;

SELECT factorial(6);
-- Returns: 720

Use REVERSE to count downward:

FOR i IN REVERSE 10..1 LOOP
  -- i goes 10, 9, 8, ..., 1
END LOOP;

FOR Loop Over Query Results

This is one of the most useful patterns in PL/pgSQL:

CREATE FUNCTION department_summary()
RETURNS TABLE(dept TEXT, headcount INT, avg_salary NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN
    SELECT department, COUNT(*) AS cnt, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department
    ORDER BY department
  LOOP
    dept       := rec.department;
    headcount  := rec.cnt;
    avg_salary := ROUND(rec.avg_sal, 2);
    RETURN NEXT;  -- add this row to the output
  END LOOP;
END;
$$;

SELECT * FROM department_summary();

WHILE Loop

DECLARE
  balance NUMERIC := 1000;
  years   INT := 0;
BEGIN
  WHILE balance < 2000 LOOP
    balance := balance * 1.07;  -- 7% annual growth
    years := years + 1;
  END LOOP;
  -- years now holds the number of years to double
END;

FOREACH (Array Iteration)

CREATE FUNCTION array_total(nums INT[])
RETURNS INT
LANGUAGE plpgsql
IMMUTABLE
AS $$
DECLARE
  total INT := 0;
  n     INT;
BEGIN
  FOREACH n IN ARRAY nums LOOP
    total := total + n;
  END LOOP;
  RETURN total;
END;
$$;

SELECT array_total(ARRAY[10, 20, 30]);
-- Returns: 60

RETURN NEXT and RETURN QUERY

For set-returning functions (RETURNS TABLE or RETURNS SETOF), you have two options:

RETURN NEXT

Builds the result set row by row (shown above in department_summary).

RETURN QUERY

Returns the entire result of a query at once -- simpler when you do not need to process each row:

CREATE FUNCTION find_employees(search_dept TEXT)
RETURNS SETOF employees
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT * FROM employees
    WHERE department = search_dept
    ORDER BY name;

  IF NOT FOUND THEN
    RAISE NOTICE 'No employees found in department: %', search_dept;
  END IF;
END;
$$;

RAISE: Messages and Errors

RAISE sends messages at different severity levels:

-- Informational (appears in server log / client messages)
RAISE NOTICE 'Processing employee %', emp_name;
RAISE NOTICE 'Count is %, total is %', cnt, total;  -- multiple % placeholders

-- Warning
RAISE WARNING 'Salary % exceeds maximum', new_salary;

-- Fatal error: aborts the function and transaction
RAISE EXCEPTION 'Employee with ID % not found', emp_id;

-- Error with SQL error code
RAISE EXCEPTION 'Duplicate entry'
  USING ERRCODE = 'unique_violation';

Format Specifiers

The % character is a placeholder replaced by the next argument in order:

RAISE NOTICE 'Hello %, your balance is $%', user_name, balance;
-- Output: Hello Alice, your balance is $150.00

Exception Handling

PL/pgSQL uses BEGIN ... EXCEPTION ... END blocks to catch and handle errors:

CREATE FUNCTION safe_insert_employee(
  p_name TEXT,
  p_email TEXT,
  p_department TEXT,
  p_salary NUMERIC
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO employees(name, email, department, salary, hire_date, is_active)
    VALUES (p_name, p_email, p_department, p_salary, CURRENT_DATE, TRUE);

  RETURN 'Employee added successfully';

EXCEPTION
  WHEN unique_violation THEN
    RETURN 'Error: email ' || p_email || ' already exists';
  WHEN check_violation THEN
    RETURN 'Error: data failed a check constraint';
  WHEN OTHERS THEN
    RETURN 'Unexpected error: ' || SQLERRM;
END;
$$;

Key Points About Exceptions

• Each BEGIN ... EXCEPTION ... END block acts as a subtransaction. If an exception is caught, work done inside that block is rolled back.
• SQLERRM holds the error message text.
• SQLSTATE holds the 5-character error code.
• Common conditions: unique_violation, foreign_key_violation, check_violation, null_value_not_allowed, division_by_zero, no_data_found.
• Use WHEN OTHERS as a catch-all (but prefer specific conditions).

Nested Exception Blocks

You can nest blocks for fine-grained control:

CREATE FUNCTION process_order(p_order_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  v_total NUMERIC;
BEGIN
  -- Outer block
  SELECT total_price INTO STRICT v_total
    FROM orders WHERE id = p_order_id;

  -- Inner block with its own exception handler
  BEGIN
    UPDATE inventory SET stock = stock - 1
      WHERE product_id = (SELECT product_id FROM orders WHERE id = p_order_id);

    IF NOT FOUND THEN
      RAISE EXCEPTION 'Inventory update failed';
    END IF;
  EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'Inventory error: %, rolling back inventory change only', SQLERRM;
      -- The outer transaction continues
  END;

  RETURN 'Order processed, total: ' || v_total;

EXCEPTION
  WHEN no_data_found THEN
    RETURN 'Order ' || p_order_id || ' not found';
END;
$$;

SELECT INTO STRICT raises no_data_found if zero rows are returned and too_many_rows if more than one row is returned.

Putting It All Together

Here is a realistic function that combines variables, conditionals, loops, and error handling:

CREATE FUNCTION generate_monthly_report(report_month DATE)
RETURNS TABLE(
  metric_name TEXT,
  metric_value NUMERIC
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_start DATE;
  v_end   DATE;
  v_total NUMERIC := 0;
  v_count INTEGER := 0;
  rec     RECORD;
BEGIN
  -- Calculate date range for the month
  v_start := DATE_TRUNC('month', report_month);
  v_end   := v_start + INTERVAL '1 month';

  -- Validate
  IF v_start > CURRENT_DATE THEN
    RAISE EXCEPTION 'Cannot generate report for future month: %',
      TO_CHAR(v_start, 'YYYY-MM');
  END IF;

  -- Total revenue
  SELECT COALESCE(SUM(amount), 0), COUNT(*)
    INTO v_total, v_count
    FROM sales
    WHERE sale_date >= v_start AND sale_date < v_end;

  metric_name  := 'total_revenue';
  metric_value := v_total;
  RETURN NEXT;

  metric_name  := 'transaction_count';
  metric_value := v_count;
  RETURN NEXT;

  metric_name  := 'average_sale';
  metric_value := CASE WHEN v_count > 0 THEN ROUND(v_total / v_count, 2) ELSE 0 END;
  RETURN NEXT;

  -- Revenue by region
  FOR rec IN
    SELECT region, SUM(amount) AS region_total
    FROM sales
    WHERE sale_date >= v_start AND sale_date < v_end
    GROUP BY region
    ORDER BY region_total DESC
  LOOP
    metric_name  := 'revenue_' || LOWER(rec.region);
    metric_value := rec.region_total;
    RETURN NEXT;
  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    RAISE WARNING 'Report generation failed: %', SQLERRM;
    metric_name  := 'error';
    metric_value := -1;
    RETURN NEXT;
END;
$$;

SELECT * FROM generate_monthly_report('2025-01-01');

Try It Yourself

Practice these concepts on your own PostgreSQL instance:

1. Write a PL/pgSQL function fizzbuzz(n INT) RETURNS TABLE(num INT, result TEXT) that returns "Fizz", "Buzz", "FizzBuzz", or the number for each value from 1 to n.
2. Write a function that accepts an employee ID, gives them a 10% raise, and returns their old and new salary. Use exception handling to deal with a missing employee.
3. Write a function using FOR ... IN ... LOOP that iterates over all departments and returns the one with the highest average salary.

Summary