plpgsql_check

Overview

Version

Build

You can build the RPM packages for plpgsql_check using pig build:

pig build pkg plpgsql_check         # build RPM packages

Install

You can install plpgsql_check directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:

pig repo add pgsql -u          # Add repo and update cache

Install the extension using pig or apt/yum/dnf:

• Install
• pig
• dnf
• apt

pig install plpgsql_check;          # Install for current active PG version

pig ext install -y plpgsql_check -v 18  # PG 18
pig ext install -y plpgsql_check -v 17  # PG 17
pig ext install -y plpgsql_check -v 16  # PG 16
pig ext install -y plpgsql_check -v 15  # PG 15
pig ext install -y plpgsql_check -v 14  # PG 14

dnf install -y plpgsql_check_18       # PG 18
dnf install -y plpgsql_check_17       # PG 17
dnf install -y plpgsql_check_16       # PG 16
dnf install -y plpgsql_check_15       # PG 15
dnf install -y plpgsql_check_14       # PG 14

apt install -y postgresql-18-plpgsql-check   # PG 18
apt install -y postgresql-17-plpgsql-check   # PG 17
apt install -y postgresql-16-plpgsql-check   # PG 16
apt install -y postgresql-15-plpgsql-check   # PG 15
apt install -y postgresql-14-plpgsql-check   # PG 14

Preload:

shared_preload_libraries = 'plpgsql_check';

Create Extension:

CREATE EXTENSION plpgsql_check CASCADE;  -- requires: plpgsql

Usage

Sources:

• PGXN plpgsql_check 2.9.2
• plpgsql_check README
• plpgsql_check control file
• Local package metadata

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.

The PGXN distribution version is 2.9.2, while the extension control file still declares SQL default_version = '2.9'. PostgreSQL 14-18 are documented as supported in the upstream README.

CREATE EXTENSION IF NOT EXISTS plpgsql_check;

Check a Function

SELECT *
FROM plpgsql_check_function('public.refresh_totals()');

SELECT *
FROM plpgsql_check_function('public.refresh_totals(int, text)', fatal_errors := false);

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

SELECT functionid, lineno, statement, sqlstate, message, level
FROM plpgsql_check_function_tb('public.refresh_totals()');

Output formats include text, JSON, and XML:

SELECT * FROM plpgsql_check_function('fx()', format := 'text');
SELECT * FROM plpgsql_check_function('fx()', format := 'json');
SELECT * FROM plpgsql_check_function('fx()', format := 'xml');

Trigger Functions

Trigger functions need the relation they operate on:

SELECT *
FROM plpgsql_check_function('public.audit_trigger()', 'public.accounts');

SELECT *
FROM plpgsql_check_function(
  'public.audit_trigger()',
  'public.accounts',
  newtable := 'new_rows',
  oldtable := 'old_rows'
);

Warning Levels

SELECT *
FROM plpgsql_check_function(
  'fx()',
  extra_warnings         := true,
  performance_warnings   := true,
  security_warnings      := true,
  compatibility_warnings := true
);

• 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

SELECT n.nspname, p.proname, c.*
FROM pg_catalog.pg_namespace n
JOIN pg_catalog.pg_proc p ON p.pronamespace = n.oid
JOIN pg_catalog.pg_language l ON l.oid = p.prolang
CROSS JOIN LATERAL plpgsql_check_function_tb(p.oid) AS c
WHERE l.lanname = 'plpgsql'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema');

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.

LOAD 'plpgsql_check';
SET plpgsql_check.mode = 'fresh_start';

Common settings:

plpgsql_check.mode = disabled | by_function | fresh_start | every_start
plpgsql_check.fatal_errors = yes | no
plpgsql_check.show_nonperformance_warnings = false
plpgsql_check.show_performance_warnings = false

Profiler

SELECT plpgsql_check_profiler(true);

SELECT public.refresh_totals();

SELECT lineno, exec_stmts, total_time, avg_time, source
FROM plpgsql_profiler_function_tb('public.refresh_totals()');

SELECT stmtid, parent_stmtid, lineno, exec_stmts, stmtname
FROM plpgsql_profiler_function_statements_tb('public.refresh_totals()');

SELECT * FROM plpgsql_profiler_functions_all();
SELECT plpgsql_profiler_reset_all();

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

shared_preload_libraries = 'plpgsql,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.

SET plpgsql_check.enable_tracer = on;
SELECT plpgsql_check_tracer(true, 'terse');

SELECT * FROM plpgsql_coverage_statements('public.refresh_totals()');
SELECT * FROM plpgsql_coverage_branches('public.refresh_totals()');

Pragmas

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

CREATE OR REPLACE FUNCTION fx(anyelement) RETURNS text AS $$
DECLARE
  r record;
BEGIN
  PERFORM plpgsql_check_pragma('type: r (id int, processed bool)');
  RETURN $1::text;
END;
$$ LANGUAGE plpgsql;

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.