Stored Procedures & CALL

Multi-step logic inside the database. Production-oriented PostgreSQL guidance.

Recipe

CREATE OR REPLACE PROCEDURE app.transfer_credit(p_from bigint, p_to bigint, p_amount numeric)
LANGUAGE plpgsql AS $$
BEGIN
  UPDATE app.wallets SET balance = balance - p_amount WHERE account_id = p_from;
  UPDATE app.wallets SET balance = balance + p_amount WHERE account_id = p_to;
END;
$$;

When to reach for this: You implement or review SQL using this concept in PostgreSQL 18.

Working Example

CALL app.transfer_credit(1, 2, 10.00);

What this demonstrates:

• Idiomatic PostgreSQL 18 SQL
• Patterns suitable for EXPLAIN review
• Safe defaults for OLTP workloads

Deep Dive

Mechanism

• Parser builds query tree.
• Executor runs plan nodes.
• MVCC provides snapshot isolation per statement or transaction.

Practice

• Keep examples readable.
• Test at realistic row counts.

Gotchas

• Long transactions - Block vacuum and increase bloat.. Fix: Commit quickly; set timeouts.
• Missing indexes - Seq scans under load.. Fix: Index join and filter columns.
• Implicit locks - Surprise blocking.. Fix: Use explicit FOR UPDATE when needed.
• Wrong isolation assumption - Anomalies under concurrent writes.. Fix: Pick level deliberately.
• Untested migrations - DDL lock or invalid objects.. Fix: Stage migrations with production-like data.

Alternatives

FAQs

Related

• Functions Basics - SQL vs PL/pgSQL

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.

pbqrthvqrf.vb|ptvb987|202607