beginnerintermediateadvanced

PostgreSQL · Section 13

PostgreSQL Functions and Procedures

Level: intermediate
Read: 30 min
Concepts: 5

The real distinction between a SQL function (one statement, inlineable by the planner) and a PL/pgSQL function (a full procedural block with variables, control flow and exception handling), the parameters/return-type/control-flow vocabulary PL/pgSQL uses, the procedure's unique and otherwise-impossible ability to COMMIT/ROLLBACK mid-body, and the judgment calls around when database-side logic is the right tool versus when it quietly turns PostgreSQL into an unnecessarily complex application runtime.

This section

How to write SQL Functions in PostgreSQL FAST | Full Tutorial | Step-by-StepSoftware Nuggets

What is true here

A SQL function's body is one statement and can be inlined by the planner; a PL/pgSQL function is a full procedural block, always called as an opaque unit.
DECLARE for locals, IF/CASE/LOOP/WHILE/FOR for control flow, EXCEPTION WHEN condition THEN ... for errors — each EXCEPTION block is its own subtransaction.
Only a procedure (CALL, not SELECT) can run COMMIT/ROLLBACK inside its body, and only from a top-level CALL or an unbroken chain of nested CALL/DO.
Database-side logic fits invariants and set-based bulk operations; application-layer logic fits business rules that change often or touch external systems.
A long trigger chain or a PL/Python function doing network I/O is a warning sign, not a feature — it recreates application concerns with worse tooling.

What you will be able to do

Choose between a SQL function and a PL/pgSQL function based on whether the logic needs a decision or loop
Write a PL/pgSQL function with typed parameters, local variables, control flow and a targeted EXCEPTION handler
Explain why only a procedure can COMMIT mid-body, and under what conditions that is actually allowed
Judge, per operation, whether logic belongs in the database or the application layer

From a single statement to a self-managing batch job

SQL function

one statement, inlineable

PL/pgSQL function

variables, control flow, exceptions

Procedure

CALL — can COMMIT mid-body

Functions

SQL vs PL/pgSQL, and the parameter/control-flow/exception vocabulary the procedural language uses.

SQL Functions and PL/pgSQL Functions

coreintermediate

A SQL function's body is a single SQL statement (or a short sequence of them) — no branching, no loops, no local variables. A PL/pgSQL function's body is a full procedural block with IF/CASE, loops, variables and exception handling, written in PostgreSQL's own procedural language.

Think of it as

A SQL function is a named, parameterized SQL statement — the planner can often inline it directly into the calling query, the same way a view is folded in. A PL/pgSQL function is a small program: it has a BEGIN/END block, local variables, control flow and its own exception mechanism, and the planner treats it as an opaque call rather than something it can see inside of. Reach for a SQL function when the whole job is "run this query with these parameters"; reach for PL/pgSQL the moment the job needs a decision, a loop, or more than one statement whose result feeds the next.

sql

-- SQL function: body is one statement
CREATE FUNCTION full_name(first text, last text)
RETURNS text
LANGUAGE sql
AS $$
    SELECT first || ' ' || last;
$$;

-- PL/pgSQL function: body is a procedural block
CREATE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql
AS $$
BEGIN
    IF b = 0 THEN
        RETURN NULL;
    END IF;
    RETURN a / b;
END;
$$;

What we're doing: Write the same "clamp a value between 0 and 100" logic as a SQL function (via CASE) and as a PL/pgSQL function (via IF), and compare.

clamp_functions.sqlsql

CREATE FUNCTION clamp_sql(v numeric)
RETURNS numeric LANGUAGE sql AS $$
    SELECT LEAST(GREATEST(v, 0), 100);
$$;

CREATE FUNCTION clamp_plpgsql(v numeric)
RETURNS numeric LANGUAGE plpgsql AS $$
BEGIN
    IF v < 0 THEN
        RETURN 0;
    ELSIF v > 100 THEN
        RETURN 100;
    ELSE
        RETURN v;
    END IF;
END;
$$;

SELECT clamp_sql(150), clamp_plpgsql(150);

1–4: The SQL function's entire body is one SELECT — no BEGIN/END, no variables.
6–16: The PL/pgSQL function needs a procedural block just to express the same three-way branch.

Output

 clamp_sql | clamp_plpgsql
-----------+---------------
       100 |           100

Why this works: Both produce the same result here, which is precisely the point: when the logic really is "one expression," a SQL function says that directly and stays eligible for planner inlining, while a PL/pgSQL function forces the planner to treat it as an opaque black box even though nothing procedural is actually happening. The choice should follow what the logic needs, not habit.

Defaulting to PL/pgSQL for logic that is really just one SQL expression

Wrong

sql

CREATE FUNCTION full_name(first text, last text)
RETURNS text LANGUAGE plpgsql AS $$
BEGIN
    RETURN first || ' ' || last;
END;
$$;

Better

sql

CREATE FUNCTION full_name(first text, last text)
RETURNS text LANGUAGE sql AS $$
    SELECT first || ' ' || last;
$$;

What you see: A query that calls full_name() in its WHERE clause or SELECT list performs worse than the equivalent inline expression would, and EXPLAIN shows it as an opaque function call rather than expanded into the plan.

Why: A single-statement SQL function is a candidate for inlining — PostgreSQL can fold it directly into the surrounding query the same way it folds in a simple view, letting the planner reason about it like ordinary SQL. A PL/pgSQL function of any complexity is always called as an opaque unit, even when its body is trivial, which forecloses that optimization for no procedural benefit.

A named statement vs a small program

SQL function

+Body is one or more plain SQL statements
+No variables, no control flow
+Can be inlined by the planner into the caller

PL/pgSQL function

−Body is a procedural block (DECLARE/BEGIN/END)
−IF, CASE, LOOP, variables, exceptions
−Always an opaque call to the planner

SQL function vs PL/pgSQL function

SQL function vs PL/pgSQL function
Property	SQL function	PL/pgSQL function
Body	one or more plain SQL statements	a procedural block (DECLARE/BEGIN/END)
Control flow	none	IF, CASE, LOOP, WHILE, FOR
Local variables	none	yes, via DECLARE
Planner visibility	can be inlined into the caller	opaque function call
Good fit	a single parameterized query	multi-step logic, branching, error handling

Remember: SQL function = a named SQL statement, inlineable by the planner. PL/pgSQL function = a small program with variables, control flow and exceptions, called as an opaque unit. Pick based on whether the logic needs a decision or a loop, not by default.

Parameters, Return Types, Variables, Control Flow and Exceptions

coreintermediate

A PL/pgSQL function declares typed IN/OUT/INOUT parameters, a return type (a scalar, a row type, or a set via RETURNS TABLE/SETOF), local variables via DECLARE, control flow via IF/CASE/LOOP/WHILE/FOR, and errors are caught with a BEGIN ... EXCEPTION WHEN condition THEN ... END block.

Think of it as

Think of a PL/pgSQL function body as an ordinary small program that happens to live inside the database: DECLARE is where you name your local state, the body is where you branch and loop over it, and EXCEPTION is a catch block scoped to whichever BEGIN it is attached to — not the whole function unless that BEGIN wraps the whole function. That scoping matters because each BEGIN block with its own EXCEPTION clause is implemented as a subtransaction, which is not free.

sql

CREATE FUNCTION order_summary(order_id int, OUT total numeric, OUT item_count int)
LANGUAGE plpgsql AS $$
DECLARE
    v_status text;
BEGIN
    SELECT status INTO v_status FROM orders WHERE id = order_id;

    SELECT sum(price * qty), count(*)
      INTO total, item_count
      FROM order_items
     WHERE order_items.order_id = order_summary.order_id;
EXCEPTION
    WHEN no_data_found THEN
        total := 0;
        item_count := 0;
END;
$$;

What we're doing: Write a function that divides two numbers, using a local variable and an EXCEPTION block to turn a division-by-zero error into a NULL result instead of an aborted transaction.

safe_divide.sqlsql

CREATE FUNCTION safe_divide(a numeric, b numeric)
RETURNS numeric
LANGUAGE plpgsql AS $$
DECLARE
    result numeric;
BEGIN
    result := a / b;
    RETURN result;
EXCEPTION
    WHEN division_by_zero THEN
        RAISE NOTICE 'division by zero, returning NULL';
        RETURN NULL;
END;
$$;

SELECT safe_divide(10, 2), safe_divide(10, 0);

4: DECLARE introduces a typed local variable, scoped to this function.
6: The division that can fail — this is what the EXCEPTION block below is guarding.
9–12: WHEN division_by_zero catches specifically that error condition, not every possible error.

Output

 safe_divide | safe_divide
-------------+-------------
           5 |            
(NOTICE:  division by zero, returning NULL)

Why this works: Wrapping the risky statement in a BEGIN block with a targeted EXCEPTION WHEN clause lets the function recover from one specific, expected error condition (division_by_zero) while still letting any other, truly unexpected error propagate and abort the transaction as normal — catching every possible error indiscriminately would hide real bugs.

Wrapping the entire function body in EXCEPTION WHEN OTHERS to "be safe"

Wrong

sql

CREATE FUNCTION process_order(order_id int)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    UPDATE orders SET status = 'processing' WHERE id = order_id;
    INSERT INTO audit_log (order_id, event) VALUES (order_id, 'processed');
EXCEPTION
    WHEN OTHERS THEN
        NULL;  -- swallow it and move on
END;
$$;

Better

sql

CREATE FUNCTION process_order(order_id int)
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    UPDATE orders SET status = 'processing' WHERE id = order_id;
    INSERT INTO audit_log (order_id, event) VALUES (order_id, 'processed');
    -- no blanket EXCEPTION block: a real error should abort the transaction,
    -- not be silently discarded
END;
$$;

What you see: An order's status update silently fails to run (a constraint violation, a deadlock, a typo in a later version of the function) but the function reports success anyway, because WHEN OTHERS caught and discarded the error.

Why: WHEN OTHERS matches every error PostgreSQL can raise, including ones the author never anticipated and would have wanted to see — a foreign key violation, a serialization failure, an out-of-disk-space error. Catching a broad class of errors and doing nothing with them converts a loud, correct failure into a silent, incorrect success, which is strictly worse than letting the transaction abort.

The shape of a PL/pgSQL function body

CREATE FUNCTION safe_divide(a numeric, b numeric) RETURNS numeric LANGUAGE plpgsql AS $$ DECLARE result numeric; BEGIN result := a / b; RETURN result; EXCEPTION WHEN division_by_zero THEN RETURN NULL; END; $$;

a numeric, b numeric

IN parameters — typed, read-only inside the function

DECLARE result numeric;

Local variable — scoped to this function body

result := a / b; RETURN result;

Control flow — the ordinary body — can branch or loop

EXCEPTION WHEN division_by_zero THEN RETURN NULL;

Exception block — catches one specific condition — its own subtransaction

Parameter modes

Parameter modes
Mode	Meaning
IN (default)	value passed into the function, read-only inside it
OUT	value the function sets and returns; omit an explicit RETURNS clause when every return value is OUT
INOUT	combines IN and OUT — passed in, and the function may change it before returning

Remember: IN/OUT/INOUT parameters, DECLARE for locals, IF/CASE/LOOP/WHILE/FOR for control flow, and BEGIN ... EXCEPTION WHEN condition THEN ... END scoped to its own block (and its own subtransaction) for error handling — catch specific conditions, not OTHERS, unless you genuinely mean every error.

Procedures

The one capability that separates a procedure from a function: managing its own transaction boundary.

Procedures and Transaction-Related Differences

coreintermediate

A PROCEDURE is invoked with CALL rather than SELECT, does not return a value the way a function does (though it can have OUT parameters), and — unlike a function — its body may run COMMIT and ROLLBACK, starting a new transaction each time, as long as nothing outside a chain of nested CALL/DO invocations intervenes.

Think of it as

A function always executes inside whatever transaction the caller is already in — it has no power to commit or roll back that transaction out from under the caller. A procedure, invoked directly via CALL (or nested only inside other CALL/DO invocations with nothing else in between), can commit and start a fresh transaction mid-body. That single capability is the entire reason procedures exist as a separate object: some operations — batched maintenance work, a long loop that needs to commit progress periodically — genuinely need to manage their own transaction boundaries, and a function is structurally unable to do that.

sql

CREATE PROCEDURE archive_old_orders()
LANGUAGE plpgsql AS $$
DECLARE
    r record;
BEGIN
    FOR r IN SELECT id FROM orders WHERE created_at < now() - interval '1 year' LOOP
        INSERT INTO orders_archive SELECT * FROM orders WHERE id = r.id;
        DELETE FROM orders WHERE id = r.id;
        COMMIT;  -- allowed here: CALL at the top level, nothing intervening
    END LOOP;
END;
$$;

CALL archive_old_orders();

What we're doing: Show a procedure committing progress every other row inside a loop — something a function cannot do — and confirm the mid-loop commits actually took effect.

procedure_commit_loop.sqlsql

CREATE TABLE counters (id int PRIMARY KEY, n int);

CREATE PROCEDURE bump_counters()
LANGUAGE plpgsql AS $$
DECLARE
    i int;
BEGIN
    FOR i IN 1..4 LOOP
        INSERT INTO counters VALUES (i, i * 10);
        IF i % 2 = 0 THEN
            COMMIT;
        END IF;
    END LOOP;
END;
$$;

CALL bump_counters();
SELECT * FROM counters ORDER BY id;

8–12: Each loop iteration inserts one row; every even iteration commits — a fresh transaction starts automatically right after.
17: All four rows are visible, committed across what were actually several separate transactions.

Output

CALL
 id | n
----+----
  1 | 10
  2 | 20
  3 | 30
  4 | 40

Why this works: Committing partway through a long-running procedure releases locks and makes progress durable incrementally, which matters for batch jobs that would otherwise hold locks and accumulate an enormous amount of uncommitted work for a long time — a function has no way to express this at all, since a function's COMMIT/ROLLBACK is categorically disallowed.

Trying to COMMIT inside a function to "save progress" in a long-running loop

Wrong

sql

CREATE FUNCTION bump_counters()
RETURNS void LANGUAGE plpgsql AS $$
BEGIN
    FOR i IN 1..4 LOOP
        INSERT INTO counters VALUES (i, i * 10);
        COMMIT;  -- ERROR: invalid transaction termination
    END LOOP;
END;
$$;

Better

sql

CREATE PROCEDURE bump_counters()
LANGUAGE plpgsql AS $$
BEGIN
    FOR i IN 1..4 LOOP
        INSERT INTO counters VALUES (i, i * 10);
        COMMIT;  -- allowed: this is a procedure, called with CALL
    END LOOP;
END;
$$;

What you see: CREATE FUNCTION ... COMMIT ... fails outright at call time with "invalid transaction termination," or the author avoids the error by wrapping the whole loop in one giant transaction that holds locks and accumulates undo/vacuum pressure for its entire duration.

Why: A function always runs inside the caller's existing transaction and has no independent transaction boundary to commit — that restriction is fundamental to what a function is, not a missing feature. When the actual requirement is "commit progress periodically inside a loop," a procedure is the object built for that; reaching for a function and fighting the restriction (or working around it by never committing at all) is solving the wrong problem with the wrong tool.

Only one of these can manage its own transaction boundary

FUNCTION — SELECT func(...)

+Always runs inside the caller's existing transaction
+COMMIT/ROLLBACK inside the body: never allowed
+Returns a value, usable in a SELECT list

PROCEDURE — CALL proc(...)

−Can commit and start a fresh transaction mid-body
−Only from a top-level CALL, or nested CALL/DO with nothing between
−No return value the way a function has (OUT params still work)

PROCEDURE vs FUNCTION

PROCEDURE vs FUNCTION
Property	PROCEDURE	FUNCTION
Created with	CREATE PROCEDURE	CREATE FUNCTION
Invoked with	CALL proc(...)	SELECT func(...)
Returns a value	no RETURNS clause (OUT params still work)	yes, via RETURNS
COMMIT/ROLLBACK in body	allowed, under the CALL-chain rule	never allowed
Usable in a SELECT list / WHERE clause	no	yes

Remember: CALL invokes a procedure, SELECT invokes a function. Only a procedure's body can run COMMIT/ROLLBACK — and only when reached via a top-level CALL or a chain of nested CALL/DO with nothing else in between — because a function always executes inside the caller's existing transaction.

The judgment call

When database-side logic earns its keep, and when it quietly becomes an unnecessarily complex application runtime.

When Database-Side Logic Is Appropriate

standardintermediate

Database-side logic (a function or procedure) is a good fit when the operation is set-based, must be atomic with the data it touches, or needs to run close to the data for performance — enforcing an invariant, a bulk computation, a trigger-driven side effect. Application-layer logic is usually clearer for business rules that change often, involve external systems, or benefit from the application's own testing, version control and code review workflow.

Think of it as

The real question is not "can this be a database function" — almost anything can — but "does moving it into the database buy something the application layer cannot get more cheaply." It usually can, for operations that are fundamentally about data (an invariant every writer must obey, a set-based transformation cheaper to do next to the rows than after shipping them over the network) and usually cannot, for logic that is really about business process (pricing rules, workflow states, anything that calls an external API) where the application's tooling is simply better suited.

sql

-- good fit: a data invariant, enforced regardless of which application writes
ALTER TABLE accounts ADD CONSTRAINT balance_non_negative CHECK (balance >= 0);

-- poor fit: business logic that changes often and calls an external system
-- (send a welcome email on signup) -- belongs in the application layer, not a trigger

What we're doing: Contrast a genuinely good use of a database function (a set-based bulk price update) with logic that only looks convenient in the database but is a poor fit (charging a customer's card).

good_vs_poor_fit.sqlsql

-- GOOD FIT: set-based, purely data, no external calls
CREATE PROCEDURE apply_seasonal_discount(pct numeric)
LANGUAGE sql AS $$
    UPDATE products SET price = price * (1 - pct / 100.0);
$$;
CALL apply_seasonal_discount(10);

-- POOR FIT: this "trigger" would need to call an external payment API,
-- which PL/pgSQL cannot do safely or transactionally --
-- charging a card belongs in the application layer, not the database
-- CREATE TRIGGER charge_card_after_insert ...  (do not do this)

1–6: One SQL statement, no external dependency, atomic with the table it modifies — a genuinely good fit for the database.
8–11: Charging a card requires network I/O to an external service with its own retry/idempotency concerns — something a database trigger is structurally unsuited to do safely.

Output

CALL

Why this works: The bulk price update is fast, atomic, and needs nothing outside the database to complete correctly — exactly the shape of work a set-based UPDATE (wrapped in a procedure for reuse) is good at. Charging a card needs retries, idempotency keys, and a response from an external system the database cannot reason about, which is why that logic belongs in application code that already has the tooling for it.

Moving business logic into triggers because "it keeps the database consistent"

Wrong

sql

CREATE FUNCTION notify_low_stock() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.quantity < 10 THEN
        PERFORM pg_notify('low_stock', NEW.product_id::text);
        -- application is expected to listen and then call an external
        -- alerting service, email, Slack webhook, etc. from inside a trigger
        -- indirectly -- the logic is now split across two systems
    END IF;
    RETURN NEW;
END;
$$;

Better

sql

-- database: just the invariant/data fact
CREATE FUNCTION notify_low_stock() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.quantity < 10 THEN
        PERFORM pg_notify('low_stock', NEW.product_id::text);
    END IF;
    RETURN NEW;
END;
$$;
-- application: owns what "low stock" actually means to do (Slack, email, dashboard)
-- by subscribing to the notification, not by the trigger reaching further out itself

What you see: Debugging "why didn't the low-stock alert fire" now means reading PL/pgSQL, checking database logs, AND reading application code — the business logic for what an alert means is split across two languages and two deployment pipelines with no single place to see it.

Why: pg_notify() is a reasonable, purely-database way to surface a fact ("stock dropped below 10"); deciding what to DO about that fact (send an email, page someone, adjust a dashboard) is business process that changes independently of the schema and benefits enormously from the application's own code review, tests and deploy cadence. Keeping the split at "database states a fact, application decides what to do about it" avoids duplicating business logic in two places that can drift out of sync.

Leaning database-side vs application-side

Leaning database-side vs application-side
Signal	Lean database-side	Lean application-side
Must hold regardless of caller	yes — a constraint or trigger	no — a UI-specific rule
Changes frequently	no	yes
Touches external systems	no	yes — email, payment gateway, third-party API
Shape of the work	set-based, bulk	per-request business process
Needs strong testability/versioning in app tooling	less critical	critical

Remember: Reach for database-side logic for invariants and set-based bulk operations that must be atomic with the data; reach for the application layer for business rules that change often or touch external systems — most healthy systems use both, deliberately, not one exclusively.

Avoiding an Unnecessarily Complex Database Runtime

standardintermediate

It is possible to build entire application workflows out of PL/pgSQL functions, triggers calling other triggers, and PL/Python or PL/Perl for arbitrary logic — but doing so trades away the application layer's tooling (tests, version control diffing, code review, observability, horizontal scaling) for a language and environment that has none of that, purely because the code happens to run close to the data.

Think of it as

The database is exceptionally good at storage, integrity, indexing and transactions — it was never designed to be a general-purpose application server, and stretching it into one (deep call chains of triggers, PL/Python doing HTTP requests, business logic that only exists as a function nobody outside the DBA team can read) recreates all the problems application code has already solved, but inside an environment with worse debugging tools, weaker test frameworks, and no independent horizontal scaling. A small number of well-chosen database functions is a tool; a large fraction of the application's logic living there is a distinct architecture with real costs.

sql

-- a small, well-scoped use: enforcing a derived value stays correct
CREATE FUNCTION set_updated_at() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    NEW.updated_at := now();
    RETURN NEW;
END;
$$;
CREATE TRIGGER touch_updated_at BEFORE UPDATE ON orders
    FOR EACH ROW EXECUTE FUNCTION set_updated_at();

What we're doing: Show a chain of triggers implementing an entire order-processing workflow inside the database, and why it is hard to reason about compared to the same workflow in application code.

trigger_chain_smell.sqlsql

-- order insert triggers inventory deduction...
CREATE TRIGGER deduct_inventory AFTER INSERT ON orders
    FOR EACH ROW EXECUTE FUNCTION deduct_inventory_fn();

-- ...which triggers a low-stock check...
CREATE TRIGGER check_low_stock AFTER UPDATE ON inventory
    FOR EACH ROW EXECUTE FUNCTION check_low_stock_fn();

-- ...which triggers a reorder function that calls a supplier API via PL/Python...
CREATE TRIGGER trigger_reorder AFTER INSERT ON stock_alerts
    FOR EACH ROW EXECUTE FUNCTION call_supplier_api_fn();

-- one INSERT INTO orders now silently fans out through 3 triggers and
-- an external HTTP call, none of it visible from the application code
-- that ran the original INSERT

1–3: The first trigger is plausible on its own — deducting inventory on an order.
9–11: By the third hop, an ordinary INSERT is making an outbound network call the application developer has no visibility into.

Output

-- (no direct output -- the point is the invisible fan-out, not a runnable result)

Why this works: Each individual trigger looks reasonable in isolation, but the chain as a whole means a simple INSERT INTO orders can silently trigger a real HTTP call to a supplier — an application developer reading the INSERT has no way to know that, application-level logging never sees it, and a failure deep in the chain (the supplier API times out) surfaces as a confusing error on an INSERT statement that looks like it should be simple.

Reaching for a database trigger chain because "it keeps everything in one place"

Wrong

sql

-- entire order workflow implemented as a chain of AFTER triggers,
-- including a PL/Python function that calls an external supplier API
CREATE TRIGGER trigger_reorder AFTER INSERT ON stock_alerts
    FOR EACH ROW EXECUTE FUNCTION call_supplier_api_fn();  -- PL/Python, does an HTTP request

Better

sql

-- database: state the fact
CREATE FUNCTION check_low_stock() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
    IF NEW.quantity < 10 THEN
        PERFORM pg_notify('low_stock', NEW.product_id::text);
    END IF;
    RETURN NEW;
END;
$$;
-- application: owns the reorder workflow, listens for the notification,
-- calls the supplier API with its own retry/timeout/observability tooling

What you see: A production incident where an ordinary order INSERT hangs or fails, and the on-call engineer has to trace through three layers of database triggers before discovering the real cause is an external API timeout inside a PL/Python function nobody remembered was there.

Why: Application code calling an external API has retries, timeouts, circuit breakers, structured logging and monitoring built around it as a matter of course; a trigger doing the same thing generally has none of that, and a slow or failing external call inside a trigger holds the triggering transaction's locks open the entire time. Keeping the database's job to stating facts (via pg_notify or a status column) and letting the application own the actual workflow keeps each failure mode visible in the tooling built to handle it.

Remember: A database function or trigger is a targeted tool for invariants and set-based operations, not a substitute application runtime — long trigger chains, PL/Python doing network I/O, and business logic invisible to code review are signs the database is doing the application's job with worse tooling for it.