plpgsql_check extension
Plpgsql_check is a powerful postgres extension that acts as a linter and validator for pl/pgsql functions, catching bugs early by analyzing code structure, variables,missing return statements, semantic errors, performance issues, and potential sql injection vulnerabilities in execute statements
Why do we need this as a must-have extension while developing or migrating to PostgreSQL
As oracle performs both syntax and semantics checks during compilation of pl/sql but as postgresql only does the syntax check so, by performing only syntax check may raises the hidden semantic issues during functional testing during migrations and development
Code having semantic errors may not be catched while compilations but they raises as issue when we run the pl/pgsql code
So to solve this problem of semantic errors plpgsql_check extension comes into picture to perform semantic check for pl/pgsql code, it helps migrations and development
Key features and benefits
Static analysis : validates pl/pgsql code, ensuring sql identifiers and function parameters are correct
Bug detection : identifies unused variables, unreachable code and missing return statements.
Performance optimization : detects potential performance issues such as unnecessary hidden casts and inefficient queries.
Safety checks : checks execute statements to identify sql injection risks
Detailed reporting : provides detailed diagnostics for debugging pl/pgsql code including dependency tracking
How to install and Create Extension
Installation command:
sudo apt update
sudo apt install postgresql-17-plpgsql-checkVerify the installation :
ls /usr/share/postgresql/17/extension/plpgsql_check.control
Output :
/usr/share/postgresql/17/extension/plpgsql_check.controlCreate extension in the postgres :
Connect to your database using psql and run the following command
psql -U username -d database_name
CREATE EXTENSION plpgsql_check;Verify the creation of the extension :
\dx
Output :
testdb=# \dx
List of installed extensions
Name | Version | Default version | Schema | Description
---------------+---------+-----------------+------------+--------------------------------------
plpgsql | 1.0 | 1.0 | pg_catalog | PL/pgSQL procedural language
plpgsql_check | 2.8 | 2.8 | public | extended check for plpgsql functions
(2 rows)How does it works
Plpgsql_check_function : a function that allows users to check the syntax and semantic of a specific PL/pgsql functions
We can perform the semantic check all the schema objects at one go by using a SQL and log the report of plpgsql_check_function into a table
Plpgsql_check_function_tb : this returns human readable tabular results of the check performed by plpgsql_check_function
Example plpgsql_check functionality
Create a table of results to log all the errors into it
CREATE TABLE IF NOT EXISTS plpgsql_check_results (
schema_name text,
object_name text,
args text,
message text,
message_type text,
checked_at timestamp default now()
);Script to fetch the all the semantic errors of functions and procedures in a schema
DO
$$
DECLARE
r RECORD;
m RECORD;
BEGIN
FOR r IN
SELECT n.nspname AS schema_name,
p.proname AS object_name,
pg_get_function_identity_arguments(p.oid) AS args,
p.oid AS obj_oid,
p.prokind
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema', 'pg_toast')
AND n.nspname NOT LIKE 'pg_temp%'
AND l.lanname = 'plpgsql'
AND p.prokind IN ('f','p') -- functions & procedures only
ORDER BY n.nspname, p.proname
LOOP
RAISE NOTICE 'Checking %.%(%):', r.schema_name, r.object_name, r.args;
BEGIN
FOR m IN
SELECT * FROM plpgsql_check_function_tb(r.obj_oid)
LOOP
INSERT INTO plpgsql_check_results(
schema_name,
object_name,
args,
message,
message_type
)
VALUES (
r.schema_name,
r.object_name,
r.args,
m.message,
m.level
);
END LOOP;
EXCEPTION WHEN OTHERS THEN
RAISE WARNING 'Skipping %.%(%): %', r.schema_name, r.object_name, r.args, SQLERRM;
END;
END LOOP;
END;
$$ LANGUAGE plpgsql;Query to fetch the logged error messages from the table
select * from plpgsql_check_results where message_type='error' ;Sample output :
test_db=# SELECT schema_name, object_name, message, message_type, checked_at
FROM plpgsql_check_results
WHERE message_type IN ('error', 'warning')
ORDER BY message_type, object_name;
schema_name | object_name | message | message_type | checked_at
-------------+-----------------------+-------------------------------------------------------------------+--------------+----------------------------
demo | archive_employee | relation "demo.employee_archive" does not exist | error | 2026-03-26 07:27:42.286024
demo | get_department_budget | control reached end of function without RETURN | error | 2026-03-26 07:27:42.286024
demo | get_employee_details | column "emp_code" does not exist | error | 2026-03-26 07:27:42.286024
demo | log_audit_event | relation "demo.audit_log" does not exist | error | 2026-03-26 07:27:42.286024
demo | update_salary | column "salary" is of type numeric but expression is of type text | error | 2026-03-26 07:27:42.286024
demo | calculate_bonus | unused variable "v_department" | warning | 2026-03-26 07:27:42.286024
demo | calculate_bonus | unused variable "v_tax_rate" | warning | 2026-03-26 07:27:42.286024
(7 rows)
We can check the plpgsql_check for individual functions using the following query
select * from plpgsql_check_function(‘object_name with signatures’);This Extension can be used in development, testing, and production environments
Supports various validation modes , including triggering on creation (by_function) or checking all functions at once
Includes simple profiling and racing capabilities, allowing developers to detect unclosed cursors and analyze performance
Conclusion
the plpgsql_check extension is an essential tool that every Postgresql developer and database administrator should have in their toolkit
Validating PL/pgSQL Code During Development unlike Oracle which performs both syntax and semantic checks at compilation time, PostgreSQL only performs syntax checks by default leaving semantic errors hidden until runtime. plpgsql_check bridges this gap, allowing developers to catch and fix issues before they reach production.
Useful While Migrating Databases to PostgreSQL Migration tools often convert code syntactically but leave behind semantic issues such as broken object references, type mismatches, and missing objects. Running plpgsql_check immediately after migration gives a complete picture of all broken objects across every schema — turning weeks of manual testing into an automated validation process.
Catching Errors in Large PostgreSQL-Based Applications In large applications with hundreds of functions and triggers, manually reviewing every object is practically impossible. plpgsql_check scans your entire database in a single script execution, logging all errors and warnings into a results table that can be queried and tracked over time.
Improving Code Quality and Security Beyond catching errors, plpgsql_check detects unused variables, unreachable code, unnecessary type casts, and SQL injection vulnerabilities in dynamic EXECUTE statements — issues that even experienced developers can easily miss during code reviews.
Whether you are building new PostgreSQL applications, managing a large existing system, or migrating from Oracle — plpgsql_check is not optional, it is a must-have. It fills a critical gap in PostgreSQL’s built-in validation and gives you confidence that your code is not just syntactically correct but semantically sound, performant, and secure.
-- Install it. Run it. Fix what it finds.
CREATE EXTENSION plpgsql_check;