Learn PL/pgSQL In Action
// Kaufman skill map, operational mental model, and production decision framework for using PL/pgSQL deliberately instead of accidentally.
This overview is designed to help you choose the right entry point quickly. Follow the full track from lesson one, continue from your last checkpoint, or jump straight into a phase that matches what you need right now.
Curriculum Map
Navigate by phase, then choose the lesson that matches your current depth.
Kaufman Skill Map and Operational Mental Model
20 minKaufman skill map, operational mental model, and production decision framework for using PL/pgSQL deliberately instead of accidentally.
PL/pgSQL Runtime Model and Execution Boundaries
15 minDeep operational model of how PL/pgSQL routines are invoked, interpreted, planned, cached, and bounded by PostgreSQL execution semantics.
Function, Procedure, Trigger, and DO Block Design
19 minProduction-grade decision framework for choosing between functions, procedures, trigger functions, triggers, and anonymous DO blocks in PostgreSQL.
Block Structure, Scope, Labels, and Defensive Declarations
13 minDefensive use of PL/pgSQL block structure, labels, scope, declarations, constants, nullability, aliases, and local variable discipline.
Type System: Record, Rowtype, Domain, and Composite Usage
13 minType system discipline for production PL/pgSQL: scalar variables, %TYPE, %ROWTYPE, record, domains, composite types, result contracts, and schema drift control.
Variable Substitution, Name Resolution, and Ambiguity Control
13 minProduction discipline for PL/pgSQL variable substitution, SQL name resolution, ambiguity control, naming conventions, labels, aliases, and dynamic SQL boundaries.
Basic Statements: PERFORM, SELECT INTO, RETURNING, and FOUND
13 minPractical production guide to PL/pgSQL basic statements: PERFORM, SELECT INTO, STRICT, DML RETURNING, FOUND, ROW_COUNT, and statement-level contracts.
Control Flow: Conditionals, Loops, and Query Iteration
12 minProduction guide to PL/pgSQL control flow: IF, CASE, LOOP, WHILE, FOR, FOREACH, EXIT, CONTINUE, query iteration, labels, and workflow-safe branching.
Returning Values, Tables, Sets, and Stream-Like Results
10 minPractical production guide to returning scalar values, rows, tables, sets, and stream-like results from PL/pgSQL functions without hiding cardinality, memory, or API contracts.
Procedure Calls, Transaction Control, and Server-Side Workflows
10 minPractical production guide to PostgreSQL procedures, CALL semantics, transaction control, COMMIT/ROLLBACK boundaries, and server-side workflow orchestration in PL/pgSQL.
Error Handling: Exceptions, SQLSTATE, and Domain Failures
17 minError handling production-grade di PL/pgSQL: exception boundary, SQLSTATE, RAISE, domain failure, retryability, dan pola error contract yang defensible.
Observability: RAISE, Logging, Context, and Debug Signals
15 minObservability production-grade untuk PL/pgSQL: RAISE levels, NOTICE/DEBUG/WARNING, diagnostic context, logging discipline, correlation id, dan debug signals yang aman.
Assertions, Contracts, and Runtime Guardrails
11 minAssertion, contract, dan runtime guardrail untuk PL/pgSQL production-grade: kapan memakai ASSERT, kapan memakai RAISE, bagaimana merancang precondition, postcondition, invariant, dan kill-switch.
Dynamic SQL: EXECUTE, FORMAT, Quote Ident, and USING
10 minDynamic SQL production-grade di PL/pgSQL: EXECUTE, FORMAT, quote_ident, quote_nullable, USING, identifier/value separation, injection safety, plan behavior, dan metadata-driven automation.
Plan Caching, Generic vs Custom Plans, and Performance Traps
17 minPlan caching, generic plans, custom plans, EXECUTE replanning, parameter-sensitive queries, dan jebakan performa PL/pgSQL di production.
Cursors, Portals, Batching, and Large Result Processing
15 minCursor, portal, refcursor, batching, large result processing, transaction boundary, dan pola pemrosesan data besar yang aman di PL/pgSQL.
Trigger Functions, Data Change Triggers, and Row State Machines
17 minTrigger function, data-change trigger, dan row state machine untuk implementasi invariant, audit, validasi, dan workflow boundary di PostgreSQL production-grade.
Event Triggers, DDL Guardrails, and Schema Governance
14 minEvent trigger untuk DDL guardrail, schema governance, migration safety, dan kontrol perubahan database di PostgreSQL production-grade.
Transition Tables, Audit Capture, and Change Reasoning
13 minTransition tables, audit capture, change reasoning, dan compliance-grade data-change observability dengan PL/pgSQL trigger di PostgreSQL.
SECURITY DEFINER/INVOKER, Search Path, and Privilege Boundaries
12 minSECURITY DEFINER, SECURITY INVOKER, search_path hardening, privilege boundaries, role design, dan secure PL/pgSQL function deployment di PostgreSQL.
Concurrency, Locking, Advisory Locks, and Race Condition Control
10 minRace condition control, explicit locking, advisory locks, SKIP LOCKED queues, deadlock prevention, dan concurrency-safe PL/pgSQL routines di PostgreSQL.
Transaction Isolation, Retryability, and Consistency Patterns
11 minTransaction isolation, MVCC snapshots, retryable failures, serialization errors, deadlocks, dan consistency pattern untuk PL/pgSQL production routines.