Views, functions and triggers

Logic inside the database: what views, functions, procedures and triggers are good for, and what they cost.

Draft. This page is new and has not yet been reviewed by a second person. Every query output on it was produced by a real run on PostgreSQL 18.6 (PGlite 0.5.9). If something looks wrong, report it.

On this page
  1. Views: a named query
  2. Functions
  3. Procedures
  4. Triggers
  5. Trade-offs
  6. In MySQL
  7. Sources

PostgreSQL can hold logic as well as data: views name a query, functions and procedures run code inside the server, and triggers run code automatically when rows change. Each is useful, and each moves behaviour somewhere that application developers may not look. Use them where the database is the right place to enforce something, not as a default.

Views: a named query#

A view stores a query, not data. Selecting from it runs the query, and the planner merges it into the outer query as if you had written it out, so a simple view costs nothing.

sql
create view order_summary as
select o.id, o.created_at, o.status, u.email, u.country, o.total
from orders o
join users u on u.id = o.user_id;
sql
select * from order_summary where id = 500;
Plan real output from PostgreSQL 18.6 (PGlite 0.5.9)
Nested Loop  (cost=0.58..16.62 rows=1 width=51)
  ->  Index Scan using orders_pkey on orders o  (cost=0.29..8.31 rows=1 width=31)
        Index Cond: (id = 500)
  ->  Index Scan using users_pkey on users u  (cost=0.29..8.30 rows=1 width=28)
        Index Cond: (id = o.user_id)

The plan reads orders and users directly through their primary keys; the view has disappeared. Views are good for hiding a join that everyone repeats, giving a stable shape to clients while tables change underneath, and limiting what a role can see (grant access to the view, not the table).

Functions#

A function takes arguments and returns a value or a set of rows. It can be written in SQL or in PL/pgSQL, PostgreSQL's procedural language with variables, loops and error handling.

sql
create function order_value(p_order int) returns numeric
language plpgsql stable as $$
declare
  v numeric;
begin
  select sum(qty * price) into v from order_items where order_id = p_order;
  return coalesce(v, 0);
end;
$$;
sql
select id, total, order_value(id) as recomputed from orders where id between 1 and 5 order by id;
Result 5 rows; real output from PostgreSQL 18.6 (PGlite 0.5.9)
idtotalrecomputed
1749.67749.67
21149.801149.80
3163.77163.77
4682.36682.36
5469.36469.36

Mark functions IMMUTABLE, STABLE or VOLATILE truthfully: the planner relies on it. Only IMMUTABLE functions can be used in an index expression, and a simple SQL-language function can be inlined into the calling query, while a PL/pgSQL function is a black box the planner cannot see into. A function called once per row in a large query is also a classic hidden cost: here, order_value runs its own query for every order.

Procedures#

A procedure (CREATE PROCEDURE, run with CALL) is like a function that returns nothing, with one important difference: it can COMMIT and ROLLBACK inside its body, when called outside an explicit transaction. That makes it suitable for long maintenance jobs that work in batches and commit as they go.

Triggers#

A trigger runs a function automatically before or after a row is inserted, updated or deleted. A common, sensible use is an audit trail that cannot be forgotten by any code path:

sql
create table price_history (product_id int not null, old_price numeric(10,2), new_price numeric(10,2), changed_at timestamptz not null default now());

create function record_price_change() returns trigger
language plpgsql as $$
begin
  if new.price is distinct from old.price then
    insert into price_history (product_id, old_price, new_price) values (old.id, old.price, new.price);
  end if;
  return new;
end;
$$;

create trigger products_price_audit
after update of price on products
for each row execute function record_price_change();

update products set price = price * 1.10 where category_id = 3;
sql
select count(*) as changes_recorded, min(new_price - old_price) as smallest_rise, max(new_price - old_price) as largest_rise
from price_history;
Result 1 row; real output from PostgreSQL 18.6 (PGlite 0.5.9)
changes_recordedsmallest_riselargest_rise
440.1619.95

One UPDATE changed every product in the category, and the trigger wrote a history row for each one. The application never had to remember.

Trade-offs#

In the databaseIn the application
Enforced for every client and code pathyesonly if every path remembers
Visible to developers reading the app coderarelyyes
Testing, debugging, code review, deploymentharder, separate toolingthe usual tools
Performanceno network round trips; but row-by-row triggers and functions can be slowextra round trips, but easier to scale out than the database

Good uses: constraints the data must never violate, audit trails, keeping a denormalised column in step, and batch jobs close to the data. Poor uses: business rules that change often, anything that calls external services, and long chains of triggers firing triggers, which become very hard to reason about.

In MySQL#

Not run here; from the MySQL reference manual. MySQL has views, stored functions, stored procedures and triggers with similar roles. Stored routines are written in MySQL's own procedural SQL; there is no choice of language as in PostgreSQL. Triggers are row-level only (FOR EACH ROW), with no statement-level triggers.

Sources#

Esc
↑ ↓ to move↵ to open