Move logic into the database — write reusable SQL and PL/pgSQL functions, run multi-step procedures with CALL, and fire automatic actions on data changes with triggers.
Why: a function packages a calculation behind a name so you can reuse it in any query. The simplest kind is written in plain SQL: it takes arguments, runs one query, and returns the result.
CREATE FUNCTION order_count(uid bigint)
RETURNS bigint
LANGUAGE sql
AS $$
SELECT COUNT(*) FROM orders WHERE user_id = uid;
$$;SELECT full_name, order_count(id) FROM users;Why: PL/pgSQL is PostgreSQL’s procedural language — it adds variables, IF/ELSE, and loops on top of SQL, so you can express logic a single query cannot. Note: the body lives between the $$ markers (called "dollar quoting") so you do not have to escape quotes inside it.
CREATE FUNCTION apply_discount(price numeric, pct numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
IF pct < 0 OR pct > 100 THEN
RAISE EXCEPTION 'percentage must be between 0 and 100';
END IF;
RETURN round(price * (1 - pct / 100), 2);
END;
$$;SELECT apply_discount(50, 10); -- 45.00Why: a procedure is like a function but it can manage transactions (COMMIT/ROLLBACK inside it) and returns nothing — it is meant for "do these steps" jobs such as a batch update. You run it with CALL instead of SELECT.
CREATE PROCEDURE deactivate_stale_users()
LANGUAGE plpgsql
AS $$
BEGIN
UPDATE users
SET is_active = false
WHERE last_login < now() - interval '1 year';
END;
$$;CALL deactivate_stale_users();Why: a trigger runs a function automatically whenever rows change, so a rule is enforced no matter which query made the change. The classic use is keeping an updated_at column accurate. Note: a trigger needs a trigger function that returns the special NEW row.
CREATE FUNCTION set_updated_at()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$;CREATE TRIGGER users_set_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();