plpgsql_check: finding the PL/pgSQL errors CREATE FUNCTION accepts

A linter that reuses the server's own parser

plpgsql_check is a linter tool, a source code static analyser, for the PL/pgSQL language, which is the native language for PostgreSQL stored procedures.

The mechanism that makes it credible is stated in the first paragraph of the README: it uses only the internal PostgreSQL parser and evaluator, so you see exactly the errors that would occur at runtime. That is a much stronger claim than pattern matching over function bodies. The checker is not guessing at what a routine will do, it is asking the same engine the server uses, with the same catalogue, before the routine is ever called.

On top of that it does something CREATE PROCEDURE and CREATE FUNCTION do not. It parses the SQL inside your routines and finds errors that are not usually found during the creation command, because at creation time the statements are only parsed, not analysed against live objects.

The project's origin is worth knowing, because it explains the shape. The author says he founded the project because he wanted to publish the code he wrote for two years while trying to write enhanced checking for PostgreSQL upstream. That attempt was not fully successful, since integration into upstream requires some larger PL/pgSQL refactoring, but the code is fully functional and can be used in production, and he says it is.

Development happens in the open through a Google group for PostgreSQL extension hacking, and the README invites ideas, patches and bug reports.

The list of things it finds

The features section is a checklist of the failure modes that PL/pgSQL authors actually hit, and it is the most useful part of the README for deciding whether the tool is worth installing.

It checks the fields of referenced database objects and types inside embedded SQL, so a record field or a column that does not exist is reported. It validates that you are using the correct types for function parameters. It identifies unused variables and function arguments, and OUT arguments that are never modified. It does partial detection of dead code, which it describes as code after a RETURN command, and it detects a missing RETURN command in a function, which it calls common after exception handlers and complex logic.

Two of the entries go beyond correctness. It tries to identify unwanted hidden casts, which it notes can be a performance issue like unused indexes, since an implicit cast in a WHERE clause can prevent an index from being used. And it can check EXECUTE statements against SQL injection vulnerability.

The other two are utilities rather than checks: the ability to collect the relations and functions used by a function, which is the kind of dependency map that makes refactoring a stored procedure bearable, and the check of EXECUTE statements.

A warning about hidden casts deserves emphasis. That class of finding is a performance issue rather than a bug, and the README points at the same mechanism behind both: the analyser sees the cast the planner will have to undo.

A record field that does not exist, found without running anything

The README works through an example in a session, and it is the clearest demonstration of what the tool buys you.

The setup is a table with two columns, and a function that loops over it and raises a notice referring to a third column that does not exist:

postgres=# CREATE EXTENSION plpgsql_check;
CREATE EXTENSION
postgres=# CREATE TABLE t1(a int, b int);
CREATE TABLE

Calling the function does not reveal the problem, because the table is empty and the loop body never runs. The checker finds it anyway, with the line number, the SQL state and a message:

postgres=# select * from plpgsql_check_function_tb('f1()');

The result identifies the function, the line, the statement, the sqlstate 42703, the message that the record has no field c, and the level, which is error. The function source is then shown with the offending line marked, so the developer sees the exact reference rather than a stack trace.

That is the value proposition in one example. The bug is invisible to execution because the data is not there yet, and visible to static analysis because the analyser resolves the record field against the row type regardless of whether any rows exist.

Warning categories as arguments, not configuration

The check functions take the level of warnings as parameters, and the categories are unusually well chosen for a linter because they separate correctness from advice from performance from security.

The mandatory argument identifies the function, as a regprocedure or as a name for the text overload. Any function can be specified by Oid or by name or by signature, so you can use a regprocedure parameter with a literal like 'fx()'::regprocedure or an Oid like 16799::regprocedure, or a name only when that name is unique. If the name is not unique, or the function does not exist, it raises an error rather than guessing.

The optional arguments are the switches. fatal_errors stops on the first error, which prevents massive error reports, and defaults to true. other_warnings shows the ordinary ones, such as a different number of attributes in an assignment on the left and right side, a variable overlapping a function parameter, unused variables and unwanted casting. extra_warnings adds missing RETURN, shadowed variables, dead code, a parameter that is never read, unmodified variables and modified auto variables.

Then the three that are off by default. performance_warnings covers a declared type with a type modifier, casting, and implicit casts in a WHERE clause, which the README notes can be the reason why an index is not used. security_warnings covers SQL injection vulnerability detection. compatibility_warnings covers obsolete explicit setting of internal cursor names in refcursor or cursor variables.

The design decision is that the correctness checks are loud by default and the judgement calls are opt in, which is the right way round for a linter that runs in continuous integration.

Text, json and xml output

The function plpgsql_check_function has three possible output formats: text, json or xml, and the choice is a parameter.

The text format is the one shown in the README, and it is designed to be read by a person and grepped by a script. Each issue is a line with the SQL state, the line number, the statement, and the message, followed by the offending query and a caret pointing at the position in it. Setting fatal_errors to false makes the function return every issue rather than stopping at the first, and the example returns seven rows for a function with three distinct problems.

The XML format wraps each issue in an Issue element with the level, the SQL state, the message, the statement and its line number, and the query with its position, all inside a Function element carrying the function oid.

The JSON format is not shown in detail in the README, but it is named, and for a tool that is meant to run in continuous integration the machine readable option is the one that matters. The three formats together suggest a workflow where a human reads the text output during development and a pipeline consumes the structured output later.

There is also a second entry point, plpgsql_check_function_tb, which returns a table with one row per issue and named columns for the function id, the line number, the statement, the SQL state, the message, the detail, the hint, the level and the position. That tabular form is the one to use from SQL, where the text form has to be parsed.

Packaging for fourteen to twenty, PGXS and Meson

The repository shows two things about how this project is maintained: it supports a wide range of PostgreSQL versions, and it takes the build system seriously enough to carry two of them.

The supported range is PostgreSQL 14 through 20, and the packaging follows it. There is a separate RPM spec file for each major version from postgresql14-plpgsql_check.spec through postgresql20-plpgsql_check.spec, and the extension control file and the extension SQL script are versioned to match, with plpgsql_check--2.10.sql as the current one.

The Makefile is the PostgreSQL extension Makefile. It resolves the PGXS path from pg_config, includes the standard global makefiles, and lists the regression tests explicitly: about twenty five of them, covering the passive and active modes per major version, the profiler, the pragma generator, the statement walker, the tracer, the report, the parser and several specific behaviours. The expected/ directory holds the expected output for each, which is how a PostgreSQL extension regression suite works.

Alongside that there is a meson.build and a README.meson_msvc, so the extension can also be built with Meson, and an install_bindist.py for packaging a binary distribution. There is a small detail in the Makefile worth noting: an override of CFLAGS adds -Wno-error=incompatible-pointer-types, described as a temporary fix of compilation with gcc 15.

The source layout is the usual one, with src/ holding the C code, sql/ the extension scripts, typedefs_gen.py generating plpgsql_check.typedefs for pgindent, and an examples/ directory with a custom_scan_function example in both Markdown and SQL.

Where plpgsql_check is the wrong tool

Three cases point elsewhere.

The first is a codebase whose logic lives outside routines. This linter analyses PL/pgSQL. SQL in views, in functions written in SQL, in stored procedures written in other languages, and in the application layer are outside its reach, and the plain SQL inside a routine is only analysed as far as the checker can resolve it in the current database.

The second is a project that has moved its logic out of the database. As more applications put business logic in application code and treat the database as a store, the population of PL/pgSQL worth linting shrinks, and a linter for stored procedures becomes a smaller part of the problem.

The third is a team that wants the security warnings to be the main event. The SQL injection check is scoped to EXECUTE statements, which is the case where a value is concatenated into a statement string, and it is off by default. It is a useful check but it does not replace parameterised queries, and treating it as a reason to keep building strings by concatenation would be a misunderstanding of what the tool does.

One last practical note. The extension must be installed into the database you are checking, which means the checker sees only the objects that exist in that database. A function that references a table in another schema or another database will be analysed against a catalogue that does not contain it, and the results have to be read with that in mind.

Editorial conclusion

Adopt plpgsql_check if you maintain stored procedures in PL/pgSQL and want the runtime errors caught in review rather than in production, especially for functions whose behaviour only shows up when the underlying tables have rows. Do not adopt it expecting it to check SQL outside routines, since the SQL it analyses is the SQL embedded inside PL/pgSQL code, and do not treat its security warnings as a substitute for parameterising your statements. Verify three things first: that the extension builds against the PostgreSQL major version you run, since support covers 14 through 20 and the packaging is versioned per major; which warning categories you enable in CI, since performance and security warnings are off by default while extra and other warnings are on; and how you consume the results, because the check functions return text, json or xml and the text format is the easiest to grep but the least pleasant to parse. The extension is a C implementation with a custom licence file to review, the last push was on 2026-10-12, and the newest release is v2.10.15 from the same day.

Frequently asked questions

Official sources

1. Issues
2. okbob/plpgsql_check on GitHub
3. Project website
4. README
5. Releases