Chapter 15 · Views, Routines, and Database Automation
Stored Procedures and User-Defined Functions
Database routines can centralize reusable computations and atomic operations, but they also create deployment, privilege, observability, and portability obligations. Their contracts must be designed as carefully as application APIs.
Learning outcomes
Design routines as typed, observable database APIs
Distinguish a value-returning function from a command-oriented procedure.
Define routine arguments, results, NULL behavior, volatility, and side effects.
Use PostgreSQL SQL functions and procedures for appropriate tasks.
Explain how SQLite exposes application-defined scalar, aggregate, and window functions.
Identify security and portability risks before placing logic in a routine.
Function and procedure are different contracts
| Routine | Typical invocation | Primary contract | Transaction behavior |
|---|---|---|---|
| Function | Used in an expression or SELECT | Return a scalar, row, or relation | Usually participates in the caller’s statement/transaction; side effects should be tightly controlled. |
| Procedure | Invoked with CALL | Perform an operation through input/output parameters and side effects | Vendor-specific rules may allow transaction control in limited call contexts. |
| Application-defined SQLite function | Called like a SQL function after host registration | Execute a host callback for scalar, aggregate, or window behavior | Exists per connection/registration and inherits the statement transaction. |
Routine contract dimensions
Types
Name every input and result type; avoid implicit conversions that make overload resolution surprising.
Nullability
Specify whether NULL inputs produce NULL, an error, or a meaningful value.
Volatility
Declare whether repeated calls can change or observe database state; optimizers rely on this promise.
Privileges
Choose invoker or definer execution deliberately and constrain object resolution.
Idempotency
A retryable command should not duplicate effects when the same request is submitted again.
Observability
Expose errors, affected identifiers, and execution metrics rather than hiding all outcomes.
PostgreSQL SQL function
A pure calculation is a strong function candidate. Marking it immutable promises that equal inputs always produce equal outputs.
CREATE OR REPLACE FUNCTION money_with_tax( subtotal_cents bigint, tax_rate numeric)RETURNS bigintLANGUAGE sqlIMMUTABLERETURNS NULL ON NULL INPUTRETURN round(subtotal_cents * (1 + tax_rate))::bigint;SELECT money_with_tax(10000, 0.0825);Set-returning function
A routine can also define a reusable relation. Keep its grain and ordering contract explicit; callers should still use their own ORDER BY.
CREATE OR REPLACE FUNCTION customer_orders( requested_customer_id bigint, minimum_total_cents bigint DEFAULT 0)RETURNS TABLE ( order_id bigint, status text, ordered_at timestamp, total_cents bigint)LANGUAGE sqlSTABLEAS $$ SELECT o.order_id, o.status, o.ordered_at, o.total_cents FROM sales_order AS o WHERE o.customer_id = requested_customer_id AND o.total_cents >= minimum_total_cents$$;SELECT *FROM customer_orders(42, 5000)ORDER BY ordered_at DESC, order_id DESC;Procedure for a command
A procedure is more suitable when the purpose is an operation rather than a value expression. The example below rejects duplicate request identifiers before performing an atomic state transition.
CREATE TABLE command_receipt ( request_id uuid PRIMARY KEY, completed_at timestamptz NOT NULL DEFAULT current_timestamp);CREATE OR REPLACE PROCEDURE cancel_order( requested_order_id bigint, request_id uuid)LANGUAGE plpgsqlAS $$BEGIN INSERT INTO command_receipt (request_id) VALUES (request_id) ON CONFLICT DO NOTHING; IF NOT FOUND THEN RETURN; END IF; UPDATE sales_order SET status = 'cancelled', updated_at = current_timestamp WHERE order_id = requested_order_id AND status IN ('draft', 'submitted'); IF NOT FOUND THEN RAISE EXCEPTION 'order % is not cancellable', requested_order_id; END IF;END;$$;CALL cancel_order(104, '80e75439-90ff-4e58-a765-bc32cfbd71fd');Volatility is an optimizer promise
| PostgreSQL category | Meaning | Suitable example |
|---|---|---|
IMMUTABLE | Same arguments always yield the same result; no database dependence | Unit conversion or deterministic formatting |
STABLE | Result may depend on the database but remains stable within one statement snapshot | Read-only lookup or current-transaction timestamp |
VOLATILE | May change on every call or perform writes | Random values, sequences, or side-effecting logic |
Overstating purity can lead the optimizer to reuse or precompute a result incorrectly. Understating it can prevent useful optimization. The declaration must describe actual behavior.
SQLite extends SQL through the host application
SQLite does not provide a SQL CREATE FUNCTION or stored-procedure language. Applications register callbacks through the sqlite3_create_function() family (or a language binding such as Python’s Connection.create_function). Registration is connection-scoped.
function_name: normalize_emailarguments: 1 text valueresult: trimmed lowercase text or NULLclassification: deterministicside_effects: noneregistration_scope: each database connectionschema_use: disabled unless explicitly trusted-- The host application must register normalize_email first.SELECT normalize_email(' Ada@Example.COM ');SELECT customer_id, emailFROM customerWHERE normalize_email(email) = normalize_email(:requested_email);Security-definer routines require defensive configuration
A routine that executes with its owner’s privileges can become a privilege-escalation path. Schema-qualify objects, restrict the search path, validate inputs, avoid dynamic SQL when possible, and grant execution only to intended roles.
CREATE OR REPLACE FUNCTION reporting.total_paid_revenue()RETURNS bigintLANGUAGE sqlSTABLESECURITY DEFINERSET search_path = pg_catalog, reportingAS $$ SELECT COALESCE(SUM(o.total_cents), 0) FROM app.sales_order AS o WHERE o.status = 'paid'$$;REVOKE ALL ON FUNCTION reporting.total_paid_revenue() FROM PUBLIC;GRANT EXECUTE ON FUNCTION reporting.total_paid_revenue() TO report_reader;Test routines like public APIs
| Test dimension | Example |
|---|---|
| Nominal | Typical inputs return expected values or rows. |
| Boundary | Zero, maximum values, empty sets, and date edges. |
| NULL | Every nullable argument and result path. |
| Error | Invalid state, unauthorized caller, or missing object. |
| Concurrency | Two callers target the same row or request identifier. |
| Retry | The same command executes more than once without duplicate effects. |
| Plan | Set-returning functions and predicates remain performant at scale. |
Check your understanding
- When is a function a better fit than a procedure?
- Why is volatility classification not merely documentation?
- How do SQLite custom functions differ operationally from PostgreSQL functions?
- What makes
SECURITY DEFINERdangerous?
Review the answers
Use a function for a value or relation that composes with SQL, especially when it is pure or read-only. The optimizer uses volatility promises. SQLite functions are callbacks registered by the host, commonly per connection, rather than schema-defined SQL routines. Definer rights can expose owner privileges through unsafe object resolution or inputs.
Summary and references
- Functions return values or relations; procedures represent commands.
- Types, NULL behavior, volatility, privileges, side effects, and retry semantics form the routine contract.
- SQLite custom functions live in the application/extension boundary rather than a stored SQL language.
- Security-definer code must constrain privileges and name resolution.
- Version, test, observe, and deploy routines like application APIs.