• LANG

• C

• MIT

• PGDG

plpgsql_check : Additional validation, profiling, and diagnostics for PL/pgSQL functions

Overview

Packages

Source

Install

Make sure PGDG repo available:

Install this extension with pig:

Create this extension with:

Usage

Sources:

• README.md
• Control
• Release
• Changes

plpgsql_check is a PL/pgSQL checker, linter, profiler, tracer, and coverage tool. It analyzes PL/pgSQL function bodies with PostgreSQL’s own parser and executor infrastructure, so many problems that would otherwise appear only at runtime can be found during development or CI.

Upstream distribution 2.10.14 installs SQL extension version 2.10. The README covers PostgreSQL 14–20, including development targets; this is not a binary-package availability claim.

Check a Function

The table-returning variant is easier to filter, store, or use in CI output:

Output formats include text, JSON, and XML:

Trigger Functions

Trigger functions need the relation they operate on:

Warning Levels

• extra_warnings covers missing returns, dead code, shadowed variables, and unused arguments.
• performance_warnings covers hidden casts, type modifiers, and patterns that can block index usage.
• security_warnings includes dynamic SQL and SQL-injection risk checks.
• compatibility_warnings reports obsolete or version-sensitive PL/pgSQL patterns.

Batch Checks

Use this pattern in migration pipelines to catch changed dependencies, dropped columns, unsafe casts, and PL/pgSQL mistakes before release.

Passive Checking

Passive mode checks functions when they start. It is useful in development and preproduction, but it adds overhead.

Common settings:

Profiler

For shared profiler statistics and reliable early initialization, preload plpgsql before plpgsql_check:

Without shared preload, profiler data is limited to the active session.

Tracer and Coverage

Tracing emits notices for function and statement entry/exit and can expose variable values. It is disabled by default and must be enabled by a superuser-controlled setting.

Pragmas

Use pragma calls inside functions to describe dynamic SQL, temporary tables, inferred record types, or local check settings:

Version 2.10 adds plpgsql_make_pragma(regprocedure), which plans temporary-table creation without executing it and returns table pragmas that can be supplied to a check. Release 2.10.2 expands this to additional CREATE TABLE statement forms:

Caveats

• plpgsql_check requires plpgsql.
• Preloading is optional for active checks, but required for shared profiler storage and robust tracer/profiler initialization.
• Tracer output can include function arguments and local variable values; do not enable it broadly on sensitive production workloads.
• The checker cannot perfectly understand every dynamic SQL string. Use pragmas to document expected dynamic objects and reduce false positives.
• Releases 2.10.2 and 2.10.3 fixed possible crashes in composite-parameter analysis and in coverage or profiler reports for functions with polymorphic arguments. Version 2.10.14 retains those earlier fixes and corrects further false alarms.

The distribution META labels the license BSD, while its shipped LICENSE contains the MIT text; the catalog follows the shipped license file.

Changes in 2.10.14

Dynamic-SQL query IDs in profiler reports are now disabled by default. Enable plpgsql_check.profiler_show_dynquery_query_id only when needed. This release also improves format-string and polymorphic-type checking and fixes plan-retention and retry leaks. Branch coverage distinguishes failure while evaluating an IF/ELSIF condition from entering the implicit ELSE branch.

The SQL version remains 2.10: replacing the compatible library does not itself require an extension migration. Reconnect sessions using the old library; shared-preloaded installations require a restart. Rehearse upgrades before changing a live server.