plpgsql_check
plpgsql_check : Additional validation, profiling, and diagnostics for PL/pgSQL functions
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 3060 | plpgsql_check
|
plpgsql_check
|
2.10.4 |
LANG
|
MIT
|
C
|
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
--s-d--
|
No
|
Yes
|
No
|
Yes
|
no
|
no
|
| Relationships | |
|---|---|
| Requires | plpgsql
|
| See Also | pldbgapi
plprofiler
pglinter
pg_stat_plans
auto_explain
pg_stat_statements
pg_show_plans
pg_store_plans
hypopg
index_advisor
|
Core checks work after CREATE EXTENSION; LOAD or shared_preload_libraries is optional for passive mode and shared profiler features; META says BSD but the shipped LICENSE text is MIT.
Packages
| Type | Repo | Version | PG Major Compatibility | Package Pattern | Dependencies |
|---|---|---|---|---|---|
| EXT | PIGSTY
|
2.10.4 |
18
17
16
15
14
|
plpgsql_check |
plpgsql |
| RPM | PIGSTY
|
2.10.4 |
18
17
16
15
14
|
plpgsql_check_$v |
- |
| DEB | PIGSTY
|
2.10.4 |
18
17
16
15
14
|
postgresql-$v-plpgsql-check |
- |
| Linux / PG | PG18 | PG17 | PG16 | PG15 | PG14 |
|---|---|---|---|---|---|
el8.x86_64
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
el8.aarch64
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
el9.x86_64
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
el9.aarch64
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
el10.x86_64
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
el10.aarch64
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
PIGSTY 2.10.4
|
d12.x86_64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
d12.aarch64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
d13.x86_64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
d13.aarch64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
u22.x86_64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
u22.aarch64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
u24.x86_64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
u24.aarch64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
u26.x86_64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
u26.aarch64
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
PGDG 2.10.4
|
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 14Create this extension with:
CREATE EXTENSION plpgsql_check CASCADE; -- requires plpgsqlUsage
Sources:
- plpgsql_check 2.10.4 README
- plpgsql_check 2.10.4 release
- plpgsql_check 2.10.4 control file
- plpgsql_check 2.10.3 to 2.10.4 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.4 installs SQL extension version 2.10. This release also reports relations referenced as declaration types in dependency output.
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 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:
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.
- 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.4 keeps those fixes and improves dependency reporting.