Stored Procedures and Functions

The default model for application-database interaction is: app builds a query, sends it over a connection, waits for results, does further processing in the application layer. Database functions and stored procedures flip that - they move logic into the database itself, closer to the data.

This sounds appealing. You eliminate round trips, you can atomically access data within a transaction, and you enforce rules regardless of which caller touches the data. But there is a real cost: code that lives in the database is harder to test, harder to deploy, harder to version, and harder to roll back than code in your application. Whether the tradeoff makes sense depends heavily on what the logic is doing.

SQL functions

The simplest kind: a named query that takes parameters and returns a value.

CREATE OR REPLACE FUNCTION get_customer_lifetime_value(p_customer_id BIGINT)
RETURNS NUMERIC AS $$
  SELECT COALESCE(SUM(total), 0)
  FROM orders
  WHERE customer_id = p_customer_id
    AND status = 'completed';
$$ LANGUAGE SQL;

Call it anywhere a scalar value is valid:

SELECT id, name, get_customer_lifetime_value(id) AS ltv
FROM customers
ORDER BY ltv DESC;

The trick with SQL functions is that the planner often inlines them - it can see through the function call and optimize the underlying query as if you had written it directly. That makes them a natural fit for frequently reused calculations without the overhead you might expect.

PL/pgSQL: procedural logic

When your logic needs variables, conditionals, loops, or exception handling, PL/pgSQL provides a procedural extension to PostgreSQL:

CREATE OR REPLACE FUNCTION apply_discount(p_order_id BIGINT, p_pct NUMERIC)
RETURNS VOID AS $$
DECLARE
  v_total NUMERIC;
BEGIN
  SELECT total INTO v_total
  FROM orders
  WHERE id = p_order_id;

  IF v_total IS NULL THEN
    RAISE EXCEPTION 'Order % not found', p_order_id;
  END IF;

  IF p_pct <= 0 OR p_pct > 100 THEN
    RAISE EXCEPTION 'Discount percent must be between 1 and 100';
  END IF;

  UPDATE orders
  SET total = total * (1 - p_pct / 100.0)
  WHERE id = p_order_id;
END;
$$ LANGUAGE plpgsql;

PL/pgSQL functions run inside the database process with access to full SQL. The DECLARE block, explicit validation, and RAISE EXCEPTION give you the kind of guard-rails that a bare SQL query cannot express.

Functions vs procedures

Procedures can begin and commit their own transactions, which is useful for batch jobs that process large volumes of data and commit in chunks. Functions cannot control transactions; they run within the caller's transaction.

Triggers

A trigger calls a function automatically when a specified event occurs on a table: INSERT, UPDATE, or DELETE.

CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at := NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_set_updated_at
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

Now updated_at is stamped on every update, regardless of which application, script, or migration tool performs the write. Nothing can forget because there is nothing to remember.

Triggers shine for audit logging, enforcing invariants that span multiple rows, and keeping denormalized summaries in sync. The danger is that their side effects are invisible to whoever writes the triggering query. A trigger that looked harmless on a small table can quietly become a performance problem - or cause subtle correctness bugs - as the system grows.

When in-database logic makes sense

The clearest wins are things the database is uniquely positioned to do:

• Calculations frequently reused across many queries that read naturally as SQL
• Enforcement logic that must fire regardless of the caller - triggers, constraint checks
• Batch operations that avoid dozens of application round trips
• Audit logs that need to capture every write, not just writes through a specific code path

Keep logic in the application when it changes frequently (database functions have no deployment pipeline, no easy rollback, no unit test framework), when the computation is not data-intensive, or when you are starting fresh. The default should always be application code.

The honest version: database functions and triggers are genuinely powerful and chronically underused for enforcement and auditing. They are also genuinely overused as a substitute for good application design. The line to hold is that the database owns data integrity and the application owns business logic. Cross that line only when you have a specific, concrete reason.