11.2 Stored Functions & Procedures — The Complete SQL Course

Stored functions and procedures put reusable logic inside PostgreSQL instead of repeating it in every query or every application. Use them when the database is the right owner of the rule — and keep them small enough that the next developer can still find the rule in review.

Views named queries in 11.1. Functions and procedures name behavior.

SQL functions: one calculation, one contract

A function has a name, parameters, a return type, and a body. A scalar SQL-language function is the lightest form: the body is SQL, and the function can be used inside a query anywhere a value of its return type would fit.

This first function names the ticket-line calculation from the cafe examples:

CREATE TABLE cafe_tickets (
  ticket_id integer PRIMARY KEY,
  branch_name text NOT NULL,
  opened_at timestamptz NOT NULL,
  status text NOT NULL CHECK (status IN ('paid', 'void'))
);
 
CREATE TABLE ticket_lines (
  ticket_id integer NOT NULL REFERENCES cafe_tickets(ticket_id),
  line_no integer NOT NULL,
  item_name text NOT NULL,
  quantity integer NOT NULL CHECK (quantity > 0),
  charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
  PRIMARY KEY (ticket_id, line_no)
);
 
INSERT INTO cafe_tickets (ticket_id, branch_name, opened_at, status)
VALUES
  (101, 'center', TIMESTAMPTZ '2026-07-01 08:10:00+00', 'paid'),
  (102, 'center', TIMESTAMPTZ '2026-07-01 09:30:00+00', 'paid'),
  (103, 'harbor', TIMESTAMPTZ '2026-07-01 10:05:00+00', 'paid'),
  (104, 'harbor', TIMESTAMPTZ '2026-07-02 08:45:00+00', 'void');
 
INSERT INTO ticket_lines (ticket_id, line_no, item_name, quantity, charged_unit_price)
VALUES
  (101, 1, 'Latte', 2, 4.50),
  (101, 2, 'Cookie', 1, 3.00),
  (102, 1, 'Cold Brew', 1, 4.75),
  (103, 1, 'Latte', 1, 4.50),
  (103, 2, 'Cookie', 2, 3.00),
  (104, 1, 'Cold Brew', 2, 4.75);
 
CREATE FUNCTION line_total(p_quantity integer, p_unit_price numeric)
RETURNS numeric
LANGUAGE SQL
AS $$
  SELECT p_quantity * p_unit_price;
$$;
 
SELECT tl.ticket_id,
       tl.item_name,
       line_total(tl.quantity, tl.charged_unit_price) AS line_total
FROM ticket_lines AS tl
WHERE tl.ticket_id IN (101, 103)
ORDER BY tl.ticket_id, tl.line_no;

CREATE TABLE
CREATE TABLE
INSERT 0 4
INSERT 0 6
CREATE FUNCTION
 ticket_id | item_name | line_total
-----------+-----------+------------
       101 | Latte     |       9.00
       101 | Cookie    |       3.00
       103 | Latte     |       4.50
       103 | Cookie    |       6.00
(4 rows)

The function is not a stored result. It is a named expression with a type contract: two inputs, one numeric output. If the cafe changes the line-total rule later, the rule has one database home.

RETURNS TABLE: query-shaped output

Functions do not have to return one scalar value. RETURNS TABLE makes a function return rows with named columns. That is useful when the reusable behavior is a parameterized query: "sales for this day," "open tickets for this branch," "current inventory below this threshold."

CREATE FUNCTION sales_for_day(p_sales_day date)
RETURNS TABLE (
  branch_name text,
  ticket_count bigint,
  gross_sales numeric
)
LANGUAGE SQL
AS $$
  SELECT ct.branch_name,
         count(DISTINCT ct.ticket_id) AS ticket_count,
         sum(line_total(tl.quantity, tl.charged_unit_price)) AS gross_sales
  FROM cafe_tickets AS ct
  JOIN ticket_lines AS tl
    ON tl.ticket_id = ct.ticket_id
  WHERE ct.status = 'paid'
    AND CAST(ct.opened_at AS date) = p_sales_day
  GROUP BY ct.branch_name;
$$;
 
SELECT branch_name, ticket_count, gross_sales
FROM sales_for_day(DATE '2026-07-01')
ORDER BY branch_name;

CREATE FUNCTION
 branch_name | ticket_count | gross_sales
-------------+--------------+-------------
 center      |            2 |       16.75
 harbor      |            1 |       10.50
(2 rows)

This looks close to a view, but the parameter changes the shape of the tool. A view is a named query. A table-returning function is a named query with inputs.

PL/pgSQL: branching near the data

When the body needs variables, IF branches, loops, or messages, PostgreSQL's procedural language is PL/pgSQL. It still runs in the database, but the body is block-shaped instead of one SQL expression.

Here the function computes a ticket total into a variable, then returns a label. The RAISE NOTICE line is not part of the result grid; it is a message from the function while it runs.

CREATE FUNCTION ticket_size_label(p_ticket_id integer)
RETURNS text
LANGUAGE plpgsql
AS $$
DECLARE
  v_total numeric;
BEGIN
  SELECT sum(line_total(tl.quantity, tl.charged_unit_price))
  INTO v_total
  FROM ticket_lines AS tl
  WHERE tl.ticket_id = p_ticket_id;
 
  IF v_total IS NULL THEN
    RAISE NOTICE 'ticket % was not found', p_ticket_id;
    RETURN 'missing';
  ELSIF v_total >= 10 THEN
    RAISE NOTICE 'ticket % total is %', p_ticket_id, v_total;
    RETURN 'large';
  ELSE
    RETURN 'regular';
  END IF;
END;
$$;
 
SELECT ticket_id,
       ticket_size_label(ticket_id) AS size_label
FROM cafe_tickets
WHERE ticket_id IN (101, 102)
ORDER BY ticket_id;

CREATE FUNCTION
NOTICE:  ticket 101 total is 12.00
 ticket_id | size_label
-----------+------------
       101 | large
       102 | regular
(2 rows)

The same function can report a missing ticket:

SELECT ticket_size_label(999) AS size_label;

NOTICE:  ticket 999 was not found
 size_label
------------
 missing
(1 row)

That is the power and the risk. The logic is close to the data and reusable from every client. It is also invisible if your team only reviews application code. Treat function definitions as source code: version them, test them, and keep the behavior boring enough to inspect.

Procedures: CALL and side effects

A function returns a value and can appear inside a SELECT. A procedure is called with CALL and is built for doing work: loading a staging table, recording an operational event, running a maintenance step.

CREATE TABLE shift_openings (
  opening_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  branch_name text NOT NULL,
  opened_at timestamptz NOT NULL,
  note text NOT NULL
);
 
CREATE PROCEDURE record_shift_opening(
  p_branch_name text,
  p_opened_at timestamptz,
  p_note text DEFAULT 'opened'
)
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO shift_openings (branch_name, opened_at, note)
  VALUES (p_branch_name, p_opened_at, p_note);
 
  RAISE NOTICE 'recorded opening for %', p_branch_name;
END;
$$;
 
CALL record_shift_opening('center', TIMESTAMPTZ '2026-07-03 07:55:00+00');
CALL record_shift_opening('harbor', TIMESTAMPTZ '2026-07-03 08:05:00+00', 'manager verified');
 
SELECT opening_id,
       branch_name,
       CAST(opened_at AS date) AS opened_day,
       note
FROM shift_openings
ORDER BY opening_id;

CREATE TABLE
CREATE PROCEDURE
NOTICE:  recorded opening for center
CALL
NOTICE:  recorded opening for harbor
CALL
 opening_id | branch_name | opened_day |       note
------------+-------------+------------+------------------
          1 | center      | 2026-07-03 | opened
          2 | harbor      | 2026-07-03 | manager verified
(2 rows)

Procedures are also the PostgreSQL object that can manage transaction boundaries in allowed calling contexts, such as when CALL is not inside an active transaction block. Functions cannot be used inside a query and then quietly commit halfway through that query. This lesson keeps the live demo to one-statement CALLs because Section 12 owns transaction choreography: BEGIN, COMMIT, ROLLBACK, savepoints, and what happens when more than one session is involved.

Where server-side logic earns its keep

Put logic in the database when the database is the honest owner: a calculation shared by many queries, a parameterized report used by multiple apps, a security boundary, or a maintenance routine that belongs next to the tables it touches.

Be slower to store business workflows there. A 200-line PL/pgSQL function can hide behavior from application tests, deployment review, tracing, and the people debugging at 2 a.m. The professional compromise is not "never use functions." It is: keep them narrow, name their inputs and return types clearly, and make migrations carry their definitions the same way they carry table changes.

sql — playgroundlive

CREATE TABLE cafe_tickets (
ticket_id integer PRIMARY KEY,
branch_name text NOT NULL,
opened_at timestamptz NOT NULL,
status text NOT NULL CHECK (status IN ('paid', 'void'))
);

CREATE TABLE ticket_lines (
ticket_id integer NOT NULL REFERENCES cafe_tickets(ticket_id),
line_no integer NOT NULL,
item_name text NOT NULL,
quantity integer NOT NULL CHECK (quantity > 0),
charged_unit_price numeric(5,2) NOT NULL CHECK (charged_unit_price >= 0),
PRIMARY KEY (ticket_id, line_no)
);

INSERT INTO cafe_tickets (ticket_id, branch_name, opened_at, status)
VALUES
(101, 'center', TIMESTAMPTZ '2026-07-01 08:10:00+00', 'paid'),
(102, 'center', TIMESTAMPTZ '2026-07-01 09:30:00+00', 'paid'),
(103, 'harbor', TIMESTAMPTZ '2026-07-01 10:05:00+00', 'paid');

INSERT INTO ticket_lines (ticket_id, line_no, item_name, quantity, charged_unit_price)
VALUES
(101, 1, 'Latte', 2, 4.50),
(101, 2, 'Cookie', 1, 3.00),
(102, 1, 'Cold Brew', 1, 4.75),
(103, 1, 'Latte', 1, 4.50),
(103, 2, 'Cookie', 2, 3.00);

CREATE FUNCTION line_total(p_quantity integer, p_unit_price numeric)
RETURNS numeric
LANGUAGE SQL
AS $$
SELECT p_quantity * p_unit_price;
$$;

CREATE FUNCTION sales_for_day(p_sales_day date)
RETURNS TABLE (
branch_name text,
ticket_count bigint,
gross_sales numeric
)
LANGUAGE SQL
AS $$
SELECT ct.branch_name,
       count(DISTINCT ct.ticket_id) AS ticket_count,
       sum(line_total(tl.quantity, tl.charged_unit_price)) AS gross_sales
FROM cafe_tickets AS ct
JOIN ticket_lines AS tl
  ON tl.ticket_id = ct.ticket_id
WHERE ct.status = 'paid'
  AND CAST(ct.opened_at AS date) = p_sales_day
GROUP BY ct.branch_name;
$$;

SELECT branch_name, ticket_count, gross_sales
FROM sales_for_day(DATE '2026-07-01')
ORDER BY branch_name;

-- break it: uncomment — PostgreSQL must know a function's return type
-- CREATE FUNCTION missing_return()
-- LANGUAGE SQL
-- AS $$
--   SELECT 1;
-- $$;

⌘/Ctrl + Enter to run

Functions and procedures are named behavior. Next, 11.3 shows the version that runs without being called directly: triggers.

Checkpoint

Answer all three to mark this lesson complete

Stored Functions & Procedures