Stored Procedures and Functions

Most application code talks to the database at arm's length: the application constructs a query, sends it over a connection, waits for results, and does further processing in the application layer. Database functions and stored procedures move some of that logic into the database itself, where it can run closer to the data.

This has tradeoffs. Code that runs in the database eliminates round trips, can access data atomically within a transaction, and enforces rules regardless of how the data is written. It is also harder to test, harder to deploy, and harder to change than application code. Understanding when that tradeoff favors in-database logic is what this guide is about.

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;

SQL functions are inlined by the planner in many cases, which means the optimizer can see through the function and optimize the underlying query. They are a good fit for frequently used calculations.

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. They have access to full SQL and can read and write any table they have permission to access.

Functions vs procedures

	Function	Procedure
Returns a value?	Yes	Not required
Used in SELECT?	Yes	No (called with CALL)
Can manage transactions?	No	Yes (PostgreSQL 11+)
Call syntax	SELECT fn(...)	CALL proc(...)

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 is a database object that 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 set automatically on every update, regardless of which application, script, or migration tool performs the write. The application does not have to remember.

Triggers are well-suited to auditing (logging who changed what), enforcing invariants that span multiple rows, and maintaining denormalized summary data. They can also become a source of hard-to-trace bugs if they have side effects that are not obvious to the person writing the triggering query.

When in-database logic makes sense

Good uses:

• Calculations that are naturally expressed as SQL and called frequently
• Logic that must enforce integrity regardless of the caller (triggers, check logic)
• Batch operations that benefit from not round-tripping through the application layer
• Audit logs that must capture every write, not just writes made through the application

When to keep logic in the application instead:

• Business logic that changes frequently: database functions are harder to version, test, and roll back than application code
• Logic that is not inherently data-intensive: the database is not a better execution environment for general computation
• Any new system: start in the application, move to the database only when you have a specific, demonstrated need

The honest assessment is that database functions and triggers are powerful and often underused for enforcement and auditing, but overused as a substitute for good application architecture. The right division of responsibility keeps the database responsible for data integrity and the application responsible for business logic.