Stored procedures and triggers

Stored procedures and triggers move logic INTO the database. A function encapsulates reusable SQL logic. A trigger automatically fires when data changes, making it perfect for audit logs, validation, and derived data.

Stored procedures run inside the database engine, avoiding network round-trips and ensuring consistency regardless of which application connects. They are ideal for audit logging, data validation, and complex business rules that must be enforced even if someone connects directly via SQL.

pg_function.sql
sql
-- PL/pgSQL function: get order count for a customer
CREATE OR REPLACE FUNCTION get_customer_order_count(
  p_customer_id VARCHAR
) RETURNS INTEGER AS
$BODY$
DECLARE
  v_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM orders
  WHERE customer_id = p_customer_id;
  RETURN v_count;
END;
$BODY$
LANGUAGE plpgsql;

-- Use it:
SELECT get_customer_order_count('customer_abc');

PL/pgSQL functions use DECLARE for variables, BEGIN/END for the body, and RETURN for the result. $BODY$ delimiters avoid quote escaping issues.

PL/pgSQL functions can do more than simple queries. They support control flow like IF/ELSIF/ELSE for branching logic.

pg_function_logic.sql
sql
-- Function with IF/ELSE control flow
CREATE OR REPLACE FUNCTION classify_customer(
  p_customer_id VARCHAR
) RETURNS VARCHAR AS
$BODY$
DECLARE
  v_total NUMERIC;
BEGIN
  SELECT COALESCE(SUM(payment_value), 0) INTO v_total
  FROM orders o
  JOIN order_payments p ON o.order_id = p.order_id
  WHERE o.customer_id = p_customer_id;

  IF v_total > 500 THEN
    RETURN 'High Value';
  ELSIF v_total > 100 THEN
    RETURN 'Medium Value';
  ELSE
    RETURN 'Low Value';
  END IF;
END;
$BODY$
LANGUAGE plpgsql;

PL/pgSQL supports IF/ELSIF/ELSE, loops, exception handling, and all SQL statements. It is a full programming language inside PostgreSQL.

Trigger flow

pg_trigger.sql
sql
-- Step 1: Create an audit table
CREATE TABLE order_audit (
  audit_id SERIAL PRIMARY KEY,
  order_id VARCHAR,
  old_status VARCHAR,
  new_status VARCHAR,
  changed_at TIMESTAMP DEFAULT NOW()
);

-- Step 2: Create the trigger function
CREATE OR REPLACE FUNCTION log_order_status_change()
RETURNS TRIGGER AS
$BODY$
BEGIN
  IF OLD.order_status != NEW.order_status THEN
    INSERT INTO order_audit (order_id, old_status, new_status)
    VALUES (NEW.order_id, OLD.order_status, NEW.order_status);
  END IF;
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

-- Step 3: Attach the trigger
CREATE TRIGGER trg_order_status_change
  AFTER UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION log_order_status_change();

Three steps: create audit table, create trigger function (accesses OLD and NEW row values), attach trigger to the table event.

Database: data integrity constraints, audit logging, derived/computed values, and cross-table consistency. Application: business rules that change frequently, complex workflows, user-facing logic, and anything that needs external API calls. Keep the database as the guardian of data correctness.

Fill in the blanks: Create a trigger

Loading practice…

Quiz: Quiz

Loading practice…

Code playground: Audit trigger

Loading practice…

Flashcards: Flashcards

Loading practice…