Stored Procedures and Triggers

Stored procedures (introduced in PostgreSQL 11) are similar to functions but support transaction control — you can call COMMIT and ROLLBACK inside a procedure. This makes them suitable for batch operations that need to checkpoint progress. Triggers are special functions that run automatically before or after INSERT, UPDATE, or DELETE events on a table. Trigger functions return type TRIGGER and access the affected row via the special NEW and OLD record variables. BEFORE triggers can modify NEW to change what is written; AFTER triggers are fired after the row is written and cannot modify it. Statement-level triggers (FOR EACH STATEMENT) fire once per SQL statement rather than once per affected row.

Example

-- Stored procedure with transaction control
CREATE OR REPLACE PROCEDURE process_order_batch(batch_size INTEGER DEFAULT 100)
LANGUAGE plpgsql AS $$
DECLARE
  processed INTEGER := 0;
BEGIN
  LOOP
    UPDATE orders
      SET status = 'processing', started_at = NOW()
    WHERE id IN (
      SELECT id FROM orders
      WHERE status = 'pending'
      ORDER BY created_at
      LIMIT batch_size
    );

    processed := processed + ROW_COUNT;
    COMMIT;  -- checkpoint after each batch

    EXIT WHEN ROW_COUNT = 0;
  END LOOP;
  RAISE NOTICE 'Processed % orders', processed;
END;
$$;

CALL process_order_batch(50);

-- Trigger function — maintain audit log
CREATE OR REPLACE FUNCTION audit_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, old_data, new_data, changed_at, changed_by)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    CASE WHEN TG_OP IN ('UPDATE','DELETE') THEN row_to_json(OLD) END,
    CASE WHEN TG_OP IN ('UPDATE','INSERT') THEN row_to_json(NEW) END,
    NOW(),
    current_user
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

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

HTML

Full Editor

Related Resources

PostgreSQL Reference

Complete tag & property list

PostgreSQL How-To Guides

Step-by-step practical guides

PostgreSQL Exercises

Practice what you've learned