Functions and procedures

A function packages logic inside the database so you can reuse it by name, with parameters, instead of repeating the same query everywhere. You create one with CREATE FUNCTION (part of DDL). It takes zero or more inputs and returns a value or a table.

PostgreSQL, not the sandbox

Functions and procedures are a server-side feature, and the details differ sharply by engine. The examples here are PostgreSQL and do not run in this site's SQLite sandbox - SQLite has no CREATE FUNCTION in SQL (its user functions are registered through the host program). Read these as reference; run them against a real PostgreSQL when you have one.

SQL functions

The simplest kind wraps a query and is written in plain SQL (LANGUAGE sql). Since PostgreSQL 14, the standard BEGIN ATOMIC body is preferred over the old string-quoted form - the server parses it at creation time and tracks dependencies:

CREATE FUNCTION order_total(cust_id int)
RETURNS numeric
LANGUAGE sql
BEGIN ATOMIC
  SELECT COALESCE(SUM(total), 0)
  FROM orders
  WHERE customer_id = cust_id;
END;

Call it like any built-in function, including inside a query:

SELECT name, order_total(id) AS spent
FROM customers;

RETURNS declares the result type - a scalar (numeric, text), or a set of rows with RETURNS TABLE(...). Parameters have modes: IN passes a value in (the default), OUT returns one, and INOUT does both.

Procedural functions (PL/pgSQL)

When the logic needs variables, conditionals, or loops, plain SQL is not enough. PostgreSQL's procedural language PL/pgSQL (and others - PL/Python, PL/Perl, PL/v8) adds real control flow and exception handling:

CREATE FUNCTION spend_tier(cust_id int)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  total numeric;
BEGIN
  SELECT COALESCE(SUM(total), 0) INTO total
  FROM orders WHERE customer_id = cust_id;

  IF total > 50 THEN
    RETURN 'high';
  ELSIF total > 0 THEN
    RETURN 'low';
  ELSE
    RETURN 'none';
  END IF;
END;
$$;

The $$ marks a quoted code block. Unlike an SQL function, a PL/pgSQL body runs statement by statement and can branch, loop, and catch errors.

Functions vs stored procedures

PostgreSQL 11 added true stored procedures (CREATE PROCEDURE, invoked with CALL). The practical difference in 2026:

A function returns a value and is used inside a query (SELECT f(x)). It cannot commit or roll back.
A procedure is called on its own with CALL, returns nothing by default, and can manage transactions - COMMIT and ROLLBACK inside it - which makes it the right tool for multi-step batch jobs.

CALL archive_old_orders(DATE '2025-01-01');

For a quick, throwaway block with no name, use DO - an anonymous block that runs once and cannot be called again:

DO $$
BEGIN
  RAISE NOTICE 'runs once, has no name';
END;
$$;

View, SQL function, or procedural function?

All three let you package and reuse query logic. Pick by what the job needs - parameters, control flow, or just a saved query.

	View	SQL function	Procedural function
Created with	`CREATE VIEW`	`CREATE FUNCTION`	`CREATE FUNCTION`
Body	one `SELECT`	one or more SQL statements	full procedural code
Parameters	none	yes	yes
Control flow	none	none	loops, `IF`/`ELSE`
Error handling	none	none	yes
Logic	SQL	SQL	procedural
Use it when	a reusable, always-live query shape	reusable logic that takes parameters	logic too complex for SQL - branching, calculations, exceptions

Rule of thumb: reach for a view when you just need a named, parameter-free query; an SQL function when the same logic needs an argument; a procedural function only when you genuinely need branching, loops, or error handling.

Quick quiz