Stored Procedures and Functions

PL/pgSQL is PostgreSQL’s built-in procedural language. It extends SQL with variables, conditionals, loops, and exception handling, letting you write reusable logic that runs inside the database engine — close to the data, with no network round-trips. The primary use cases are enforcing business rules that span multiple statements, automating maintenance tasks, and building triggers that react to data changes.

CREATE FUNCTION Basics

Functions are reusable units of logic that can be called from any SQL context — in SELECT, WHERE, or as standalone calls. CREATE OR REPLACE updates an existing function without dropping it, preserving any permissions granted on it.

CREATE OR REPLACE FUNCTION greet(name TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN 'Hello, ' || name || '!';
END;
$$;

SELECT greet('world');  -- Hello, world!

The $$ dollar-quoting avoids conflicts with single quotes inside the function body. CREATE OR REPLACE updates an existing function or creates it if it doesn’t exist.

Parameters: IN, OUT, INOUT

Functions can accept input, produce output, or both. OUT parameters let a function return multiple named values without building a composite type, which is useful when a function naturally produces two related results.

-- IN is the default: value passed in, not returned
-- OUT: value returned to the caller as a named column
-- INOUT: passed in and returned (modified in place)

CREATE OR REPLACE FUNCTION divide(
  IN  numerator   NUMERIC,
  IN  denominator NUMERIC,
  OUT result      NUMERIC,
  OUT remainder   NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
  result    := numerator / denominator;
  remainder := MOD(numerator, denominator);
END;
$$;

SELECT * FROM divide(17, 5);
-- result: 3.4   remainder: 2

DECLARE Block and Variables

The DECLARE block defines local variables before BEGIN. Variables let you store intermediate query results, compute values step by step, and make the logic easier to follow than a single deeply nested expression.

CREATE OR REPLACE FUNCTION calculate_discount(
  customer_id INT,
  order_total NUMERIC
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  purchase_count INT;
  discount_rate  NUMERIC := 0.0;  -- initialize to zero
  final_total    NUMERIC;
BEGIN
  -- Load the customer's completed order count into a variable
  SELECT COUNT(*) INTO purchase_count
  FROM orders
  WHERE customer_id = calculate_discount.customer_id  -- qualify to avoid ambiguity
    AND status = 'completed';

  -- Apply tiered discount based on loyalty
  IF purchase_count >= 10 THEN
    discount_rate := 0.15;
  ELSIF purchase_count >= 5 THEN
    discount_rate := 0.10;
  ELSIF purchase_count >= 2 THEN
    discount_rate := 0.05;
  END IF;

  final_total := order_total * (1 - discount_rate);
  RETURN final_total;
END;
$$;

Note how parameter names are qualified with the function name (calculate_discount.customer_id) to avoid ambiguity with column names in queries.

Loops

Loops are useful for batch processing — iterating over a result set row by row, or repeating an operation until a condition is met. They’re most appropriate when the operation can’t be expressed as a single set-based SQL statement.

-- FOR loop over a range of integers
CREATE OR REPLACE FUNCTION sum_to(n INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
  total INT := 0;
  i     INT;
BEGIN
  FOR i IN 1..n LOOP
    total := total + i;
  END LOOP;
  RETURN total;
END;
$$;

-- FOR loop over a query result set — process one row at a time
CREATE OR REPLACE FUNCTION update_overdue_orders()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
BEGIN
  FOR rec IN
    SELECT id FROM orders
    WHERE status = 'pending'
      AND created_at < NOW() - INTERVAL '7 days'
  LOOP
    UPDATE orders SET status = 'overdue' WHERE id = rec.id;
    RAISE NOTICE 'Marked order % as overdue', rec.id;
  END LOOP;
END;
$$;

RAISE and Exception Handling

RAISE NOTICE sends a message to the client (useful for debugging and progress reporting). RAISE EXCEPTION aborts the function with an error and rolls back any changes made in the current transaction block. The EXCEPTION section catches errors so you can handle them gracefully.

CREATE OR REPLACE FUNCTION transfer_funds(
  from_id INT,
  to_id   INT,
  amount  NUMERIC
)
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
  src_balance NUMERIC;
BEGIN
  -- Lock the row to prevent concurrent transfers from the same account
  SELECT balance INTO src_balance
  FROM accounts WHERE id = from_id FOR UPDATE;

  IF src_balance < amount THEN
    -- Abort with a descriptive error — the caller sees this as a SQL error
    RAISE EXCEPTION 'Insufficient funds: balance is %, need %',
      src_balance, amount;
  END IF;

  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;

  RAISE NOTICE 'Transferred % from account % to account %',
    amount, from_id, to_id;

EXCEPTION
  WHEN OTHERS THEN
    -- Re-raise with context so the error is traceable
    RAISE EXCEPTION 'Transfer failed: %', SQLERRM;
END;
$$;

RETURN QUERY

Functions can return a full result set using RETURN QUERY. This lets you wrap a complex query in a function with a clean interface — callers use it just like a table.

CREATE OR REPLACE FUNCTION get_customer_orders(cid INT)
RETURNS TABLE(order_id INT, total NUMERIC, status TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT o.id, o.total, o.status
    FROM orders o
    WHERE o.customer_id = cid
    ORDER BY o.created_at DESC;
END;
$$;

-- Call it like a table — works in joins, CTEs, and subqueries too
SELECT * FROM get_customer_orders(42);

CREATE PROCEDURE

Procedures don’t return values but can issue COMMIT and ROLLBACK internally — something functions cannot do. This makes them the right choice for batch jobs that process data in chunks and commit each chunk independently to avoid long-running transactions.

CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff_date DATE)
LANGUAGE plpgsql
AS $$
DECLARE
  batch_size INT := 1000;
  deleted    INT;
BEGIN
  LOOP
    DELETE FROM orders
    WHERE id IN (
      SELECT id FROM orders
      WHERE created_at < cutoff_date
        AND status = 'completed'
      LIMIT batch_size
    );

    GET DIAGNOSTICS deleted = ROW_COUNT;
    COMMIT;  -- commit each batch so the table isn't locked the entire time
    EXIT WHEN deleted < batch_size;  -- stop when no more rows to delete
  END LOOP;
END;
$$;

CALL archive_old_orders('2023-01-01');

Triggers

A trigger fires a function automatically when a row is inserted, updated, or deleted. Triggers are the right tool for cross-cutting concerns — audit logging, maintaining derived columns, or enforcing invariants that can’t be expressed as a CHECK constraint.

Step 1: Write the trigger function. It must return TRIGGER and use the special variables NEW (the new row) and OLD (the old row):

-- Automatically keep updated_at current on every UPDATE
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at := NOW();  -- modify the new row before it's written
  RETURN NEW;
END;
$$;

Step 2: Attach the trigger to a table:

CREATE TRIGGER trg_users_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();
-- Now every UPDATE on users automatically sets updated_at
-- regardless of which client performs the update

Audit Log Trigger

A common pattern is recording every change to a sensitive table — who changed it, when, and what the old and new values were. Triggers make this automatic and tamper-resistant because the audit record is written inside the same transaction as the change.

CREATE TABLE audit_log (
  id         BIGSERIAL PRIMARY KEY,
  table_name TEXT,
  operation  TEXT,  -- INSERT, UPDATE, or DELETE
  old_data   JSONB,
  new_data   JSONB,
  changed_at TIMESTAMPTZ DEFAULT NOW(),
  changed_by TEXT DEFAULT current_user
);

CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO audit_log(table_name, operation, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,  -- built-in: name of the table that fired the trigger
    TG_OP,          -- built-in: INSERT, UPDATE, or DELETE
    CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::jsonb END,
    CASE WHEN TG_OP != 'DELETE'  THEN row_to_json(NEW)::jsonb END
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_accounts_audit
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit_changes();

TG_TABLE_NAME and TG_OP are built-in trigger variables that hold the table name and operation (INSERT, UPDATE, or DELETE).