plpgsql_check
plpgsql_check : extended check for plpgsql functions
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 3060 | plpgsql_check
|
plpgsql_check
|
2.10.2 |
LANG
|
MIT
|
C
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--sLd--
|
No
|
Yes
|
Yes
|
Yes
|
no
|
no
|
| Relationships | |
|---|---|
| Requires | plpgsql
|
| See Also | pldbgapi
plprofiler
pg_hint_plan
pgtap
auto_explain
plv8
plperl
plpython3u
|
PIGSTY RPM and DEB packages are aligned at 2.10.1 for PostgreSQL 14 through 18.
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PIGSTY
|
2.10.2 |
18
17
16
15
14
|
plpgsql_check |
plpgsql |
| RPM | PIGSTY
|
2.10.2 |
18
17
16
15
14
|
plpgsql_check_$v |
- |
| DEB | PIGSTY
|
2.10.2 |
18
17
16
15
14
|
postgresql-$v-plpgsql-check |
- |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
el8.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
el9.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
el9.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
el10.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
el10.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
d12.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
d12.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
d13.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
d13.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
u22.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
u22.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
u24.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
u24.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
u26.x86_64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
u26.aarch64
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
PGDG 2.10.2
|
Source
pig build pkg plpgsql_check; # build rpm/debInstall
Make sure PGDG and PIGSTY repo available:
pig repo add pgsql -u # add both repo and update cacheInstall this extension with pig:
pig install plpgsql_check; # install via package name, for the active PG version
pig install plpgsql_check -v 18; # install for PG 18
pig install plpgsql_check -v 17; # install for PG 17
pig install plpgsql_check -v 16; # install for PG 16
pig install plpgsql_check -v 15; # install for PG 15
pig install plpgsql_check -v 14; # install for PG 14Config this extension to shared_preload_libraries:
shared_preload_libraries = 'plpgsql_check';Create this extension with:
CREATE EXTENSION plpgsql_check CASCADE; -- requires plpgsqlUsage
Sources:
- plpgsql_check 2.10.1 README
- plpgsql_check 2.10.1 release
- plpgsql_check 2.10.1 control file
- plpgsql_check 2.9.2 to 2.10.1 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.
Package release 2.10.1 installs SQL extension version 2.10. PostgreSQL 14-18 are supported; the upstream source also contains compatibility work for later PostgreSQL development branches.
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_warningscovers missing returns, dead code, shadowed variables, and unused arguments.performance_warningscovers hidden casts, type modifiers, and patterns that can block index usage.security_warningsincludes dynamic SQL and SQL-injection risk checks.compatibility_warningsreports 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 = falseProfiler
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;Version 2.10 adds plpgsql_make_pragma(regprocedure), which plans CREATE TEMP TABLE ... AS SELECT|VALUES|TABLE statements without executing them and returns table pragmas that can be supplied to a check:
SELECT *
FROM plpgsql_check_function(
'public.refresh_stage()'::regprocedure,
pragmas => ARRAY(
SELECT plpgsql_make_pragma('public.refresh_stage()'::regprocedure)
)
);Caveats
plpgsql_checkrequiresplpgsql.- 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.
- Release 2.10.1 fixes shared-memory LWLock tranche registration on PostgreSQL 14; use it instead of the initial 2.10.0 build when preloading profiler/tracer state there.