Functions & Stored Procedures

PL/pgSQL Basics

PL/pgSQL is PostgreSQL's procedural language for writing functions and stored procedures. It adds variables, control flow, loops, and error handling on top of SQL.

-- Simple function returning a scalar
CREATE OR REPLACE FUNCTION get_user_order_total(p_user_id INT)
RETURNS NUMERIC AS $$
DECLARE
  v_total NUMERIC;
BEGIN
  SELECT COALESCE(SUM(total), 0)
  INTO v_total
  FROM orders
  WHERE user_id = p_user_id;

  RETURN v_total;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT get_user_order_total(42);

Functions Returning Tables

-- Return a set of rows
CREATE OR REPLACE FUNCTION get_active_users_by_role(p_role TEXT)
RETURNS TABLE(id INT, name TEXT, email TEXT) AS $$
BEGIN
  RETURN QUERY
    SELECT u.id, u.name, u.email
    FROM users u
    WHERE u.role = p_role
      AND u.deleted_at IS NULL
    ORDER BY u.name;
END;
$$ LANGUAGE plpgsql;

-- Use like a table
SELECT * FROM get_active_users_by_role('admin');

-- OUT parameters (alternative to RETURNS TABLE)
CREATE OR REPLACE FUNCTION get_stats(
  OUT total_users INT,
  OUT total_orders INT,
  OUT revenue NUMERIC
) AS $$
BEGIN
  SELECT COUNT(*) INTO total_users FROM users;
  SELECT COUNT(*) INTO total_orders FROM orders;
  SELECT COALESCE(SUM(total), 0) INTO revenue FROM orders;
END;
$$ LANGUAGE plpgsql;

Triggers

Triggers automatically execute a function when a table event (INSERT, UPDATE, DELETE) occurs. Use them for auditing, enforcing business rules, or maintaining derived data.

-- Audit trigger: track all changes to the orders table
CREATE TABLE orders_audit (
  id SERIAL PRIMARY KEY,
  order_id INT,
  action TEXT,
  old_data JSONB,
  new_data JSONB,
  changed_at TIMESTAMP DEFAULT NOW(),
  changed_by TEXT DEFAULT current_user
);

CREATE OR REPLACE FUNCTION audit_orders()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO orders_audit (order_id, action, old_data, new_data)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP != 'INSERT' THEN to_jsonb(OLD) END,
    CASE WHEN TG_OP != 'DELETE' THEN to_jsonb(NEW) END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_orders_audit
  AFTER INSERT OR UPDATE OR DELETE ON orders
  FOR EACH ROW EXECUTE FUNCTION audit_orders();

Error Handling

-- EXCEPTION blocks catch and handle errors
CREATE OR REPLACE FUNCTION safe_transfer(
  p_from INT, p_to INT, p_amount NUMERIC
) RETURNS TEXT AS $$
DECLARE
  v_balance NUMERIC;
BEGIN
  -- Check balance
  SELECT balance INTO v_balance
  FROM accounts WHERE id = p_from FOR UPDATE;

  IF v_balance < p_amount THEN
    RAISE EXCEPTION 'Insufficient funds: have %, need %', v_balance, p_amount;
  END IF;

  UPDATE accounts SET balance = balance - p_amount WHERE id = p_from;
  UPDATE accounts SET balance = balance + p_amount WHERE id = p_to;

  RETURN 'Transfer complete';

EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Transfer failed: %', SQLERRM;
    RETURN 'Transfer failed: ' || SQLERRM;
END;
$$ LANGUAGE plpgsql;

Stored Procedures (PG11+)

-- Procedures can manage transactions (COMMIT/ROLLBACK inside)
CREATE OR REPLACE PROCEDURE process_batch(p_batch_size INT)
LANGUAGE plpgsql AS $$
DECLARE
  v_processed INT := 0;
BEGIN
  LOOP
    UPDATE job_queue SET status = 'done'
    WHERE id IN (
      SELECT id FROM job_queue
      WHERE status = 'pending'
      LIMIT p_batch_size
      FOR UPDATE SKIP LOCKED
    );

    GET DIAGNOSTICS v_processed = ROW_COUNT;
    EXIT WHEN v_processed = 0;

    COMMIT;  -- commit each batch separately
    RAISE NOTICE 'Processed % rows', v_processed;
  END LOOP;
END;
$$;

-- Call a procedure
CALL process_batch(1000);