Functions and Procedures in PostgreSQL: SQL vs PL/pgSQL, RETURNS TABLE, Volatility, SECURITY DEFINER and Transaction Control with CALL

Version note: The examples in this post were run on PostgreSQL 18.7 (the official postgres:18 Docker image) right after loading the Bookstore schema; the .NET example was built and run with .NET 10, Npgsql 10.0.3 and Dapper 2.1.89.

Guess First

Bookstore, the online bookstore used as the shared example across this site's series, keeps the current customer of a session in the app.customer_id setting; the RLS policy in post 14 read the same setting. Ayşe wrote a small function that reads it, and Emre wrote the same one with a one-word difference. Both return the same value:

CREATE FUNCTION app_customer_id() RETURNS int
LANGUAGE plpgsql
AS $$ BEGIN RETURN current_setting('app.customer_id')::int; END $$;
CREATE FUNCTION app_customer_id_stable() RETURNS int
LANGUAGE plpgsql STABLE
AS $$ BEGIN RETURN current_setting('app.customer_id')::int; END $$;
SET app.customer_id = '2';
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT count(*) FROM orders WHERE customer_id = app_customer_id();
EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT count(*) FROM orders WHERE customer_id = app_customer_id_stable();

orders has an index on customer_id. Do both queries use it?

                       QUERY PLAN
---------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=21 read=10310 written=1
   ->  Seq Scan on orders (actual rows=17359.00 loops=1)
         Filter: (customer_id = app_customer_id())
         Rows Removed by Filter: 982641
         Buffers: shared hit=21 read=10310 written=1
 Planning:
   Buffers: shared hit=68 read=3 dirtied=1
 Planning Time: 0.567 ms
 Execution Time: 235.939 ms

                                         QUERY PLAN
--------------------------------------------------------------------------------------------
 Aggregate (actual rows=1.00 loops=1)
   Buffers: shared hit=18 read=1
   ->  Index Only Scan using ix_orders_customer_id on orders (actual rows=17359.00 loops=1)
         Index Cond: (customer_id = app_customer_id_stable())
         Heap Fetches: 0
         Index Searches: 1
         Buffers: shared hit=18 read=1
 Planning:
   Buffers: shared hit=6
 Planning Time: 0.130 ms
 Execution Time: 0.752 ms

They do not. A function without a marker counts as VOLATILE: PostgreSQL assumes its result may change on every call, so it cannot compute the value once and look it up in the index. It called the function for each of the million rows, read 10,331 buffers and took 236 ms. The copy marked STABLE promises "within one query I return the same result for the same input"; the planner takes the value once and goes to the index: 19 buffers, 0.75 ms. One word, a factor of 300. In this post we look at writing functions and procedures in PostgreSQL: the difference between LANGUAGE sql and PL/pgSQL, volatility, moving the ISBN check digit promised in post 18 into a CHECK constraint, stock reservation, transaction control with CALL, SECURITY DEFINER and calling all this from .NET.

LANGUAGE sql: Inlined Functions, RETURNS TABLE and OUT Parameters

Written with LANGUAGE sql and no marker at all, the same function behaves differently:

CREATE FUNCTION app_customer_id_sql() RETURNS int
LANGUAGE sql
AS $$ SELECT current_setting('app.customer_id')::int $$;
EXPLAIN (COSTS OFF)
SELECT count(*) FROM orders WHERE customer_id = app_customer_id_sql();

                                       QUERY PLAN
-----------------------------------------------------------------------------------------
 Aggregate
   ->  Index Only Scan using ix_orders_customer_id on orders
         Index Cond: (customer_id = (current_setting('app.customer_id'::text))::integer)

The plan has no function name; the body was expanded into the query (inlined). The planner substitutes a simple SQL function made of a single SELECT expression like a macro, then looks at the volatility of the expression itself: current_setting is STABLE, so the index could be used. The conditions for inlining are narrow: the body must be a single expression without a subquery, FROM or aggregate, the function must not have SECURITY DEFINER or a SET option, and its marker must not be "safer" than its body. Set-returning SQL functions (below) can also be expanded into the query. PL/pgSQL functions are never inlined; to the planner they are a box it cannot look into.

A function that returns rows is the counterpart of a table-valued function in SQL Server. For a customer's latest orders:

CREATE FUNCTION customer_recent_orders(p_customer_id int, p_limit int DEFAULT 5)
RETURNS TABLE (order_number varchar, status text, created_at timestamptz, total_amount numeric)
LANGUAGE sql STABLE
AS $$
    SELECT o.order_number, o.status, o.created_at, o.total_amount
    FROM orders AS o
    WHERE o.customer_id = p_customer_id
    ORDER BY o.created_at DESC
    LIMIT p_limit
$$;
SELECT * FROM customer_recent_orders(2, p_limit => 3);
SELECT c.customer_id, c.first_name, r.order_number, r.total_amount
FROM customers AS c
CROSS JOIN LATERAL customer_recent_orders(c.customer_id, 1) AS r
WHERE c.customer_id IN (2, 3);

 order_number | status  |       created_at       | total_amount
--------------+---------+------------------------+--------------
 BK-00999989  | Pending | 2026-10-20 20:56:00+00 |        67.36
 BK-00999983  | Pending | 2026-10-20 20:44:00+00 |        56.75
 BK-00999934  | Pending | 2026-10-20 19:06:00+00 |        61.89

 customer_id | first_name | order_number | total_amount
-------------+------------+--------------+--------------
           2 | Linus      | BK-00999989  |        67.36
           3 | Margaret   | BK-00999864  |        39.37

RETURNS TABLE is the short form of RETURNS SETOF record with OUT parameters; RETURNS SETOF orders does the same with the row type of an existing table. A parameter with a DEFAULT can be left out of the call or passed by name with p_limit => 3. LATERAL calls the function for each customer row; it is SQL Server's CROSS APPLY. The p_ prefix on parameter names is not a habit but a necessity: the column names in RETURNS TABLE are variables inside the function and clash with table columns, which gives the error we will see below. For one row with several values, OUT parameters:

CREATE FUNCTION book_stock(p_book_id int, OUT on_hand int, OUT available int)
LANGUAGE sql STABLE
AS $$
    SELECT quantity_on_hand, quantity_on_hand - quantity_reserved FROM stock_items WHERE book_id = p_book_id
$$;
CREATE FUNCTION book_available(p_book_id int) RETURNS int
LANGUAGE sql STABLE
RETURN (SELECT quantity_on_hand - quantity_reserved FROM stock_items WHERE book_id = p_book_id);
SELECT * FROM book_stock(1);
BEGIN;
ALTER TABLE stock_items RENAME COLUMN quantity_reserved TO reserved;
SELECT book_available(1);
SELECT * FROM book_stock(1);
ROLLBACK;
DROP TABLE stock_items;

 on_hand | available
---------+-----------
      36 |        36

 book_available
----------------
             36

ERROR:  column "quantity_reserved" does not exist
LINE 2:     SELECT quantity_on_hand, quantity_on_hand - quantity_res...
                                                        ^
QUERY:
    SELECT quantity_on_hand, quantity_on_hand - quantity_reserved FROM stock_items WHERE book_id = p_book_id

CONTEXT:  SQL function "book_stock" statement 1

ERROR:  cannot drop table stock_items because other objects depend on it
DETAIL:  function book_available(integer) depends on table stock_items
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

The two function bodies are stored differently. A body inside $$ ... $$ is a string: PostgreSQL parses it at call time, and after a column rename it fails on the first call. The SQL-standard form introduced in PostgreSQL 14 (RETURN ... or BEGIN ATOMIC ... END) is parsed when it is created and stored as a tree: when the column is renamed the function follows the new name, and dropping the table gives a dependency error like the views in post 21. For small functions that can be written in plain SQL this form is safer. A PL/pgSQL body is always a string.

A note: in PostgreSQL a function name alone is not an identity; the name together with the parameter types is (overloading). When you write DROP FUNCTION, ALTER FUNCTION or GRANT you have to give the signature, for example line_total(int, numeric); creating a second function with the same name and different types does not fail, it silently creates a new function.

Volatility and PARALLEL SAFE: A Promise to the Planner

Every function is in one of three categories, and without a marker it is in the worst one, VOLATILE:

PostgreSQL does not check the promise; it only requires IMMUTABLE in index definitions and generated columns. In post 19 we saw that the two-argument date_trunc cannot go into an index. What happens when you mark your own function wrongly, shown with a function that returns the order date as a day:

CREATE FUNCTION order_day(p_created_at timestamptz) RETURNS date
LANGUAGE sql
RETURN p_created_at::date;
CREATE INDEX ix_orders_order_day ON orders (order_day(created_at));
CREATE OR REPLACE FUNCTION order_day(p_created_at timestamptz) RETURNS date
LANGUAGE sql IMMUTABLE
RETURN p_created_at::date;
CREATE INDEX ix_orders_order_day ON orders (order_day(created_at));
SET TimeZone = 'Europe/Istanbul';
SELECT count(*), min(created_at), max(created_at) FROM orders WHERE order_day(created_at) = '2026-10-01';
SET enable_indexscan = off;
SET enable_bitmapscan = off;
SELECT count(*), min(created_at), max(created_at) FROM orders WHERE order_day(created_at) = '2026-10-01';

ERROR:  functions in index expression must be marked IMMUTABLE

CREATE INDEX

 count |          min           |          max
-------+------------------------+------------------------
   720 | 2026-10-01 03:00:00+03 | 2026-10-02 02:58:00+03

 count |          min           |          max
-------+------------------------+------------------------
   720 | 2026-10-01 00:00:00+03 | 2026-10-01 23:58:00+03

Converting a timestamptz to a date depends on the session's TimeZone setting, so this function is really STABLE. Marked IMMUTABLE, the index was created, but it holds the days as computed in a session running in UTC. In a session running in Istanbul time the same query returned the UTC day through the index (03:00 to 02:58 the next day in Istanbul time) and the Istanbul day with the index turned off. No error, no warning; the answer changes when the plan changes. The right way is the one from post 19: make the time zone an argument; (p_created_at AT TIME ZONE 'Europe/Istanbul')::date really is IMMUTABLE.

Next to volatility there is one more promise: parallel safety. Functions are PARALLEL UNSAFE by default, and if a query contains such a function the planner does not consider a parallel plan at all. We summed the 2.5 million rows of order_items with a function that computes a line total with a 5% discount for three or more copies:

CREATE FUNCTION line_total(p_quantity int, p_unit_price numeric) RETURNS numeric
LANGUAGE plpgsql IMMUTABLE
AS $$
BEGIN
    IF p_quantity >= 3 THEN
        RETURN round(p_quantity * p_unit_price * 0.95, 2);
    END IF;
    RETURN p_quantity * p_unit_price;
END
$$;
EXPLAIN (COSTS OFF) SELECT sum(line_total(quantity, unit_price)) FROM order_items;
SELECT sum(line_total(quantity, unit_price)) FROM order_items;
ALTER FUNCTION line_total(int, numeric) PARALLEL SAFE;
EXPLAIN (COSTS OFF) SELECT sum(line_total(quantity, unit_price)) FROM order_items;
SELECT sum(line_total(quantity, unit_price)) FROM order_items;

          QUERY PLAN
-------------------------------
 Aggregate
   ->  Seq Scan on order_items

     sum
-------------
 78426823.79
Time: 665.607 ms

                     QUERY PLAN
----------------------------------------------------
 Finalize Aggregate
   ->  Gather
         Workers Planned: 2
         ->  Partial Aggregate
               ->  Parallel Seq Scan on order_items

     sum
-------------
 78426823.79
Time: 224.062 ms

With PARALLEL SAFE the query was split across two workers and got three times faster. PARALLEL SAFE means the function writes no data, advances no sequence and changes no session state; if that is not true you get errors or wrong results in parallel queries. RESTRICTED is the middle option: in a parallel plan it runs only in the leader process. The rule: mark pure computations IMMUTABLE PARALLEL SAFE, functions that read tables STABLE PARALLEL SAFE, and leave the ones that change data at the default.

PL/pgSQL: The ISBN Check Digit and a CHECK Constraint

In post 18 we computed the ISBN-13 check digit in a single query with generate_series and CASE. In PL/pgSQL the same rule becomes a readable function:

CREATE FUNCTION isbn13_is_valid(p_isbn text) RETURNS boolean
LANGUAGE plpgsql IMMUTABLE STRICT PARALLEL SAFE
AS $$
DECLARE
    v_sum int := 0;
BEGIN
    IF p_isbn !~ '^97[89][0-9]{10}$' THEN
        RETURN false;
    END IF;
    FOR i IN 1..13 LOOP
        v_sum := v_sum + substr(p_isbn, i, 1)::int * CASE WHEN i % 2 = 0 THEN 3 ELSE 1 END;
    END LOOP;
    RETURN v_sum % 10 = 0;
END
$$;
SELECT isbn, isbn13_is_valid(isbn) AS valid
FROM (VALUES ('9780441172719'), ('9780441172718'), ('978044117271X'), (NULL)) AS v(isbn);
SELECT count(*) FILTER (WHERE isbn13_is_valid(isbn)) AS valid, count(*) AS total FROM books;

     isbn      | valid
---------------+--------
 9780441172719 | t
 9780441172718 | f
 978044117271X | f
 (null)        | (null)

 valid | total
-------+-------
  4000 | 40000
Time: 40.550 ms

The skeleton of a PL/pgSQL block is DECLARE (variables with their initial values) and BEGIN ... END. FOR i IN 1..13 LOOP is a counting loop; i does not need to be declared. Because the format is checked first with IF, a value containing "X" never reaches the ::int conversion. STRICT (RETURNS NULL ON NULL INPUT) returns NULL for a NULL argument without running the function. Checking 40,000 books took 41 ms and gives the same result as in post 18: 4,000 valid. Now let us attach the rule to the table:

ALTER TABLE books ADD CONSTRAINT ck_books_isbn13 CHECK (isbn13_is_valid(isbn));
ALTER TABLE books ADD CONSTRAINT ck_books_isbn13 CHECK (isbn13_is_valid(isbn)) NOT VALID;
INSERT INTO books (isbn, title, author_id, category, price, published_year)
VALUES ('9780441172718', 'Dune #40001', 1, 'SciFi', 24.90, 1965);
UPDATE books SET price = price + 1 WHERE book_id = 1;
UPDATE books SET price = price + 1 WHERE book_id = 2;
ALTER TABLE books VALIDATE CONSTRAINT ck_books_isbn13;

ERROR:  check constraint "ck_books_isbn13" of relation "books" is violated by some row

ALTER TABLE

ERROR:  new row for relation "books" violates check constraint "ck_books_isbn13"
DETAIL:  Failing row contains (40001, 9780441172718, Dune #40001, 1, SciFi, 24.90, 1965).

ERROR:  new row for relation "books" violates check constraint "ck_books_isbn13"
DETAIL:  Failing row contains (1, 9780000000001, Emma #1, 1818, Classic, 33.77, 1885).

UPDATE 1

ERROR:  check constraint "ck_books_isbn13" of relation "books" is violated by some row

Because of the 36,000 invalid ISBNs in the table the constraint could not be added directly. Added with NOT VALID, the existing rows were not checked and new rows are: the new book with an invalid number was rejected. The third command is the one to watch: a price update that does not touch the ISBN also failed on an old row with an invalid ISBN (book 1), because an UPDATE writes a new row version and the check applies to it. Book 2, which has a valid ISBN, was updated without trouble. So NOT VALID does not let old data off the hook; it only postpones the bulk check at the time the constraint is added. In a real catalog the order is: add the constraint NOT VALID, fix the data, then run VALIDATE CONSTRAINT; that step scans the table under a lock that does not block writes.

A warning: PostgreSQL does not check whether a function used in a CHECK really is IMMUTABLE, and it does not re-check existing rows when you change the function with CREATE OR REPLACE. If you change the rule, drop the constraint and add it again. For the same reason a function that queries another table (for example "is the author active?") is the wrong tool; rules like that belong in a foreign key or a trigger.

Bookstore: reserve_stock and Error Handling

In post 04 we reserved stock for a single book with a conditional UPDATE. The first attempt at a function that reserves all lines of an order in one call:

CREATE FUNCTION reserve_stock(p_order_id int)
RETURNS TABLE (book_id int, quantity int, available int)
LANGUAGE plpgsql
AS $$
DECLARE
    v_item record;
BEGIN
    FOR v_item IN
        SELECT book_id, sum(quantity)::int AS quantity
        FROM order_items
        WHERE order_id = p_order_id
        GROUP BY book_id
    LOOP
        RAISE NOTICE 'book %', v_item.book_id;
    END LOOP;
END
$$;
SELECT * FROM reserve_stock(1000000);

CREATE FUNCTION

ERROR:  column reference "book_id" is ambiguous
LINE 1: SELECT book_id, sum(quantity)::int AS quantity
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT book_id, sum(quantity)::int AS quantity
        FROM order_items
        WHERE order_id = p_order_id
        GROUP BY book_id
CONTEXT:  PL/pgSQL function reserve_stock(integer) line 5 at FOR over SELECT rows

Two lessons. CREATE FUNCTION succeeded: PL/pgSQL checks only the syntax of the body when it is created and parses and plans the SQL inside on first execution. The error shows up in production, the first time that line runs, so every function needs at least one test (the plpgsql_check extension helps find this kind of error without running the code). Second, the book_id in RETURNS TABLE is a variable inside the function and clashes with the book_id column of the table. The fix is to qualify columns with table aliases:

CREATE OR REPLACE FUNCTION reserve_stock(p_order_id int)
RETURNS TABLE (book_id int, quantity int, available int)
LANGUAGE plpgsql
AS $$
DECLARE
    v_status text;
    v_item   record;
BEGIN
    SELECT o.status INTO v_status FROM orders AS o WHERE o.order_id = p_order_id FOR UPDATE;
    IF NOT FOUND THEN
        RAISE EXCEPTION 'order % not found', p_order_id USING ERRCODE = 'no_data_found';
    ELSIF v_status <> 'Pending' THEN
        RAISE EXCEPTION 'order % is %, not Pending', p_order_id, v_status USING ERRCODE = 'BK002';
    END IF;

    FOR v_item IN
        SELECT oi.book_id, sum(oi.quantity)::int AS quantity
        FROM order_items AS oi
        WHERE oi.order_id = p_order_id
        GROUP BY oi.book_id
        ORDER BY oi.book_id
    LOOP
        UPDATE stock_items AS s
        SET quantity_reserved = s.quantity_reserved + v_item.quantity,
            updated_at = now()
        WHERE s.book_id = v_item.book_id
          AND s.quantity_on_hand - s.quantity_reserved >= v_item.quantity
        RETURNING s.book_id, v_item.quantity, s.quantity_on_hand - s.quantity_reserved
        INTO book_id, quantity, available;

        IF NOT FOUND THEN
            RAISE EXCEPTION 'not enough stock for book % (order %)', v_item.book_id, p_order_id
                USING ERRCODE = 'BK001',
                      DETAIL = format('requested %s', v_item.quantity),
                      HINT = 'The order stays Pending; nothing was reserved.';
        END IF;
        RETURN NEXT;
    END LOOP;

    UPDATE orders AS o SET status = 'PaymentPending' WHERE o.order_id = p_order_id;
END
$$;
SELECT * FROM reserve_stock(1000000);
SELECT * FROM reserve_stock(1000000);
SELECT * FROM reserve_stock(999893);
SELECT book_id, quantity_on_hand, quantity_reserved FROM stock_items WHERE book_id IN (2332, 2956, 5809) ORDER BY book_id;

 book_id | quantity | available
---------+----------+-----------
    1583 |        1 |       108
    7800 |        1 |        42

ERROR:  order 1000000 is PaymentPending, not Pending
CONTEXT:  PL/pgSQL function reserve_stock(integer) line 10 at RAISE

ERROR:  not enough stock for book 5809 (order 999893)
DETAIL:  requested 3
HINT:  The order stays Pending; nothing was reserved.
CONTEXT:  PL/pgSQL function reserve_stock(integer) line 29 at RAISE

 book_id | quantity_on_hand | quantity_reserved
---------+------------------+-------------------
    2332 |              187 |                 0
    2956 |              107 |                 0
    5809 |                0 |                 0

Let us read the function step by step. SELECT ... INTO ... FOR UPDATE reads the order and locks its row; two requests cannot reserve the same order at the same time. FOUND is a special variable that says whether the last SQL command found a row. FOR v_item IN query LOOP walks through the rows of the query one by one. Processing the lines in book_id order is deliberate: if two orders lock the same books in different orders they deadlock (post 09); when everyone locks in the same order they do not. The conditional UPDATE ... RETURNING ... INTO checks and increases the stock and writes the result straight into the RETURNS TABLE variables; RETURN NEXT adds those values to the result set as a row. To return the whole result of one query there is also RETURN QUERY SELECT ....

Of the three books in order 999893, the first two had stock and the third did not. The function reserved the first two and stopped at the third with RAISE EXCEPTION. In the stock table, quantity_reserved of the first two books is still 0: the error rolled back the statement that called the function, with all its changes. So that the application can tell errors apart, we gave them our own SQLSTATE codes: no stock BK001, wrong status BK002. Pick a prefix for your own codes that does not clash with the standard classes; without one, RAISE EXCEPTION uses P0001 and the application has to parse the message text. RAISE NOTICE, on the other hand, is not an error but a message to the client; psql prints it and Npgsql delivers it as an event.

DO Blocks and EXCEPTION

DO, which we used in post 18, is an anonymous PL/pgSQL block that runs once: it cannot return a value or take parameters, but for one-off maintenance jobs it saves you from creating a function. A block that reserves all pending orders of 20 October and reports the ones without enough stock:

DO $$
DECLARE
    v_order_id int;
    v_ok       int := 0;
    v_failed   int := 0;
    v_message  text;
    v_detail   text;
BEGIN
    FOR v_order_id IN
        SELECT order_id FROM orders
        WHERE status = 'Pending' AND created_at >= '2026-10-20'
        ORDER BY order_id
    LOOP
        BEGIN
            PERFORM reserve_stock(v_order_id);
            v_ok := v_ok + 1;
        EXCEPTION
            WHEN SQLSTATE 'BK001' THEN
                GET STACKED DIAGNOSTICS v_message = MESSAGE_TEXT, v_detail = PG_EXCEPTION_DETAIL;
                v_failed := v_failed + 1;
                IF v_failed <= 3 THEN
                    RAISE NOTICE '%, %', v_message, v_detail;
                END IF;
        END;
    END LOOP;
    RAISE NOTICE 'reserved: %, not enough stock: %', v_ok, v_failed;
END
$$;
SELECT status, count(*) FROM orders WHERE created_at >= '2026-10-20' GROUP BY status ORDER BY status;

NOTICE:  not enough stock for book 2057 (order 999378), requested 1
NOTICE:  not enough stock for book 28303 (order 999439), requested 1
NOTICE:  not enough stock for book 2057 (order 999535), requested 1
NOTICE:  reserved: 604, not enough stock: 10
DO

     status     | count
----------------+-------
 Cancelled      |    25
 PaymentPending |   605
 Pending        |    10

604 orders were reserved (one more, 1000000, had already been reserved above), 10 orders did not have enough stock and stayed Pending. An inner block with an EXCEPTION part works like a savepoint: when an error occurs inside it, only that block's changes are rolled back and the outer loop continues. GET STACKED DIAGNOSTICS reads the message, detail, SQLSTATE and the line where the caught error happened (PG_EXCEPTION_CONTEXT). Catch only the code you expect: WHEN OTHERS swallows every error, even deadlocks and cancelled queries. There is a cost, too: every block with an EXCEPTION part opens a subtransaction on entry, and the documentation says this is markedly more expensive. Such a block for every row in a loop over a million rows is slow and also burns through subtransaction IDs quickly.

Procedures and CALL: Transaction Control

A function always runs inside the transaction of the statement that calls it; it cannot COMMIT. A procedure, added in PostgreSQL 11, is invoked with CALL and can end its transaction and start a new one inside. That is exactly what a nightly job needs: a procedure that moves old delivered shipment records to the archive schema in chunks of 10,000 and commits after each chunk:

CREATE SCHEMA archive;
CREATE TABLE archive.shipments (LIKE public.shipments, PRIMARY KEY (shipment_id));
CREATE PROCEDURE archive_shipments(p_delivered_before timestamptz, p_batch_size int DEFAULT 10000)
LANGUAGE plpgsql
AS $$
DECLARE
    v_moved int;
    v_total int := 0;
BEGIN
    LOOP
        WITH batch AS (
            SELECT shipment_id FROM public.shipments
            WHERE delivered_at < p_delivered_before
            ORDER BY shipment_id
            LIMIT p_batch_size
        ), moved AS (
            DELETE FROM public.shipments AS s
            USING batch AS b
            WHERE s.shipment_id = b.shipment_id
            RETURNING s.*
        )
        INSERT INTO archive.shipments SELECT * FROM moved;

        GET DIAGNOSTICS v_moved = ROW_COUNT;
        EXIT WHEN v_moved = 0;
        v_total := v_total + v_moved;
        COMMIT;
        RAISE NOTICE '% rows moved, % in total', v_moved, v_total;
    END LOOP;
END
$$;
CALL archive_shipments('2023-03-01');
BEGIN;
CALL archive_shipments('2023-04-01');
ROLLBACK;
CREATE FUNCTION commit_inside() RETURNS void LANGUAGE plpgsql AS $$ BEGIN COMMIT; END $$;
SELECT commit_inside();
SELECT (SELECT count(*) FROM shipments) AS live, (SELECT count(*) FROM archive.shipments) AS archived;

NOTICE:  10000 rows moved, 10000 in total
NOTICE:  10000 rows moved, 20000 in total
NOTICE:  10000 rows moved, 30000 in total
NOTICE:  7336 rows moved, 37336 in total
CALL
Time: 356.868 ms

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function archive_shipments(timestamp with time zone,integer) line 23 at COMMIT

ERROR:  invalid transaction termination
CONTEXT:  PL/pgSQL function commit_inside() line 1 at COMMIT

  live  | archived
--------+----------
 917419 |    37336

37,336 records were moved in four transactions. Because each chunk is committed separately, locks stay short, autovacuum can start cleaning up the deleted rows before the job ends (post 08), and if the job is interrupted the moved chunks are permanent and the rest continues the next night. The CTE that feeds DELETE ... RETURNING into an INSERT does the move in a single statement: a row changes in both tables or in neither. We did not create the archive table with LIKE ... INCLUDING ALL; that option also copies the identity, and writing a value into a GENERATED ALWAYS column fails.

The two errors show the limits of a procedure. When CALL runs inside an explicit transaction block (BEGIN), the COMMIT inside the procedure is rejected: the caller owns the outer transaction. COMMIT inside a function never works. A procedure cannot return rows; if it has to return something, it uses INOUT parameters (OUT since PostgreSQL 14). Hooking this job up to pg_cron is one line, as in post 21: cron.schedule('archive-shipments', '30 2 * * *', $$CALL archive_shipments(now() - interval '3 years')$$).

SECURITY DEFINER and search_path

By default a function runs with the privileges of its caller (SECURITY INVOKER). SECURITY DEFINER makes it run with the privileges of the function's owner, like EXECUTE AS OWNER in SQL Server. This lets the application role write to a table through one controlled path without having direct write access. A function that corrects stock after a warehouse count only with a reason, and records it:

CREATE TABLE stock_adjustments
(
    stock_adjustment_id int GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    book_id     int         NOT NULL REFERENCES books (book_id),
    delta       int         NOT NULL,
    reason      text        NOT NULL,
    adjusted_by text        NOT NULL,
    adjusted_at timestamptz NOT NULL DEFAULT now()
);
REVOKE INSERT, UPDATE, DELETE ON stock_items, stock_adjustments FROM bookstore_app;
CREATE FUNCTION adjust_stock(p_book_id int, p_delta int, p_reason text) RETURNS int
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_temp
AS $$
DECLARE
    v_on_hand int;
BEGIN
    IF p_reason IS NULL OR length(trim(p_reason)) = 0 THEN
        RAISE EXCEPTION 'a reason is required' USING ERRCODE = 'BK003';
    END IF;
    UPDATE stock_items
    SET quantity_on_hand = quantity_on_hand + p_delta, updated_at = now()
    WHERE book_id = p_book_id
    RETURNING quantity_on_hand INTO v_on_hand;
    INSERT INTO stock_adjustments (book_id, delta, reason, adjusted_by)
    VALUES (p_book_id, p_delta, p_reason, session_user);
    RETURN v_on_hand;
END
$$;
SELECT proacl IS NULL AS default_acl FROM pg_proc WHERE proname = 'adjust_stock';
REVOKE EXECUTE ON FUNCTION adjust_stock(int, int, text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION adjust_stock(int, int, text) TO bookstore_app;
SELECT proname, prosecdef, proconfig, proacl FROM pg_proc WHERE proname = 'adjust_stock';

 default_acl
-------------
 t

   proname    | prosecdef |            proconfig            |                               proacl
--------------+-----------+---------------------------------+---------------------------------------------------------------------
 adjust_stock | t         | {"search_path=public, pg_temp"} | {bookstore_owner=X/bookstore_owner,bookstore_app=X/bookstore_owner}

We opened the same session as the application role:

UPDATE stock_items SET quantity_on_hand = 500 WHERE book_id = 1;
SELECT adjust_stock(1, 5, 'warehouse count 2026-10-21');
SELECT adjust_stock(1, -2, '');
SELECT book_id, delta, reason, adjusted_by FROM stock_adjustments;

ERROR:  permission denied for table stock_items

 adjust_stock
--------------
           41

ERROR:  a reason is required
CONTEXT:  PL/pgSQL function adjust_stock(integer,integer,text) line 6 at RAISE

 book_id | delta |           reason           |  adjusted_by
---------+-------+----------------------------+---------------
       1 |     5 | warehouse count 2026-10-21 | bookstore_app

The application role could not update the table directly, but could do so with a reason through the function; the call without a reason was rejected. Inside the function current_user is the owner, which is why the record keeps the caller with session_user. Three details are essential for the safety of functions like this. First, SET search_path: which schemas code running with the owner's privileges searches for table and function names must not be left to the caller's session settings; the documentation recommends leaving out schemas that callers can write to and putting pg_temp last (if it is not listed, temporary tables are searched first). Second, privileges: the EXECUTE privilege of a new function goes to PUBLIC by default (that is what an empty proacl means), so everyone who can connect to the database can call it. Close it with REVOKE ... FROM PUBLIC and a targeted GRANT; the documentation recommends doing this in the same transaction that creates the function, which is how migrations run anyway. Third, scope: the function should do only the job it is for and should not run a table name passed in by the caller through dynamic SQL.

reserve_stock also writes to stock_items, so it got the same treatment: ALTER FUNCTION reserve_stock(int) SECURITY DEFINER SET search_path = public, pg_temp, followed by REVOKE and GRANT. The general model of roles and privileges is in post 14.

Calling from Npgsql and Dapper; Comparison with SQL Server

On the application side, a set-returning function is an ordinary query: SELECT ... FROM reserve_stock(@orderId). A procedure is called with CALL, and the RAISE NOTICE messages arrive through the connection's Notice event:

public sealed record ReservedLine(int BookId, int Quantity, int Available);

public static async Task ReserveAsync(NpgsqlDataSource dataSource, int orderId)
{
    DefaultTypeMap.MatchNamesWithUnderscores = true;
    await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync();
    try
    {
        IEnumerable<ReservedLine> lines = await connection.QueryAsync<ReservedLine>(
            "SELECT book_id, quantity, available FROM reserve_stock(@orderId)", new { orderId });
        foreach (ReservedLine line in lines)
        {
            Console.WriteLine($"order {orderId}: book {line.BookId} x{line.Quantity}, available {line.Available}");
        }
    }
    catch (PostgresException ex) when (ex.SqlState is "BK001" or "BK002")
    {
        Console.WriteLine($"order {orderId}: {ex.SqlState} {ex.MessageText}");
        Console.WriteLine($"  detail: {ex.Detail ?? "-"}, hint: {ex.Hint ?? "-"}");
    }
}

public static async Task ArchiveAsync(NpgsqlDataSource dataSource, DateTime deliveredBefore)
{
    await using NpgsqlConnection connection = await dataSource.OpenConnectionAsync();
    connection.Notice += (_, e) => Console.WriteLine($"NOTICE: {e.Notice.MessageText}");

    await using NpgsqlCommand call = new("CALL archive_shipments(@deliveredBefore, @batchSize)", connection);
    call.Parameters.AddWithValue("deliveredBefore", deliveredBefore);
    call.Parameters.AddWithValue("batchSize", 5_000);
    await call.ExecuteNonQueryAsync();

    // CommandType.StoredProcedure: Npgsql 7+ sends CALL, even for a function
    await using NpgsqlCommand asProcedure = new("reserve_stock", connection)
    {
        CommandType = CommandType.StoredProcedure,
    };
    asProcedure.Parameters.AddWithValue("p_order_id", 999360);
    try
    {
        await asProcedure.ExecuteNonQueryAsync();
    }
    catch (PostgresException ex)
    {
        Console.WriteLine($"{ex.SqlState}: {ex.MessageText} | {ex.Hint}");
    }
}

dotnet run -- reserve 999360 999893 1000000 with the application role, dotnet run -- archive with the owner role that runs the nightly job (shipments delivered before 2023-04-01):

order 999360: book 4810 x2, available 180
order 999360: book 36521 x1, available 2
order 999893: BK001 not enough stock for book 5809 (order 999893)
  detail: Detail redacted as it may contain sensitive data. Specify 'Include Error Detail' in the connection string to include this information., hint: The order stays Pending; nothing was reserved.
order 1000000: BK002 order 1000000 is PaymentPending, not Pending
  detail: -, hint: -

NOTICE: 5000 rows moved, 5000 in total
NOTICE: 5000 rows moved, 10000 in total
NOTICE: 5000 rows moved, 15000 in total
NOTICE: 5000 rows moved, 20000 in total
NOTICE: 1425 rows moved, 21425 in total
42883: procedure reserve_stock(p_order_id => integer) does not exist | No procedure matches the given name and argument types. You might need to add explicit type casts.

Three things stand out. Our own SQLSTATE codes arrived in PostgresException.SqlState and were told apart with catch ... when; no need to parse the message text. DETAIL, however, was hidden: Npgsql does not show error details by default because they may contain sensitive data; with Include Error Detail=true in the connection string the same call printed detail: requested 3. Turn it on in development and remember that it can carry customer data into production logs. The last line is where people coming from SQL Server trip most often: since Npgsql 7.0, CommandType.StoredProcedure generates CALL to invoke a procedure; it used to generate SELECT for functions. Calling a function this way gives error 42883. The Npgsql.EnableStoredProcedureCompatMode AppContext switch brings the old behavior back, but a plain SELECT ... FROM f(...) is both explicit and works directly with Dapper. General use of Npgsql and Dapper is in post 11.

Side by side with SQL Server's stored procedures:

There is a difference on the plan side as well: SQL Server keeps a procedure's plan in the server-wide plan cache; PL/pgSQL prepares the plan of each statement per connection, the first time it runs on that connection, and reuses it on the same connection. In a connection pool each physical connection does this preparation once. The counterpart of SQL Server's inline table-valued function is an inlined LANGUAGE sql function; the counterpart of a multi-statement function is PL/pgSQL, which the planner cannot look into.

In Production

Functions are part of the schema: manage them in EF Core migrations with migrationBuilder.Sql(...) or in separate, versioned SQL files. CREATE OR REPLACE cannot change the return type or the parameter names; when they change you need DROP and CREATE, and the privileges (GRANT) have to be given again.

If you want the queries inside functions to show up in pg_stat_statements, you need pg_stat_statements.track = all; the default top counts only the statement the client sent (post 07). With track_functions = pl, pg_stat_user_functions keeps the call count and total time of each function. To see the plan inside a slow function there is the log_nested_statements setting of auto_explain from post 07.

In long-running procedures a new snapshot is taken after every COMMIT, so changes made by other sessions become visible in the middle of the loop. For chunked moves this is fine, but if you calculate from "the state at the start of the job", read the values into a variable before the loop.

Common Pitfalls

Leaving a function that should be STABLE unmarked: as in "Guess First", the index is lost and the function is called for every row. The opposite is worse: marking a STABLE function IMMUTABLE silently produces wrong results.

Not pinning search_path in a SECURITY DEFINER function and not taking EXECUTE away from PUBLIC. Both are open by default; write both in every SECURITY DEFINER function.

Column names in RETURNS TABLE clashing with table columns: the error appears not at creation but on the first call. Qualify columns with aliases or choose different output names.

Opening an EXCEPTION block for every row and swallowing every error with WHEN OTHERS: it is slow and hides real problems (deadlocks, cancellations).

Moving business logic into the database without restraint. For a rule like stock reservation, which lives close to the data and has to be consistent in one transaction, a function is a good place. Rules that change often and need testing, such as discount campaigns, notifications or talking to a payment provider, should stay in application code: the debugger, unit test tooling and deployment process of PL/pgSQL are not as comfortable as C#'s, and the database is the hardest layer to scale out.

A Small Experiment

★☆☆ Rewrite isbn13_is_valid with LANGUAGE sql and RETURN, using the generate_series expression from post 18. Compare the time of the two versions on 40,000 books with \timing; should you expect it to be inlined? Remember that the body has a subquery.

★★☆ Write the opposite of reserve_stock: release_stock(p_order_id) releases the reservations of a cancelled order and sets it to Cancelled. What should happen when the same order is cancelled twice? Give it your own SQLSTATE code and catch it on the .NET side.

★★★ Try archive_shipments with two psql sessions: while A runs the procedure with p_batch_size => 2000, B locks the row of one of the shipments to be moved with FOR UPDATE and waits 10 seconds. Watch A's behavior through wait_event in pg_stat_activity; what changes if you add FOR UPDATE SKIP LOCKED (post 09) to the batch CTE?

In the next post we move on to triggers: BEFORE and AFTER, row and statement level, transition tables, updated_at and audit triggers, an INSTEAD OF trigger that makes the view we could not update in post 21 writable, and an event trigger that records DDL commands.