plpgsql_check
A PostgreSQL extension that performs static analysis and linting for PL/pgSQL stored procedures.
What is plpgsql_check?
plpgsql_check is a PostgreSQL extension that performs static code analysis on PL/pgSQL stored procedures and functions. It identifies semantic errors, performance issues, security vulnerabilities, and code quality problems before runtime execution. The tool leverages PostgreSQL's internal parser to ensure findings match what would actually occur during function execution.
PostgreSQL database developers and administrators who write and maintain PL/pgSQL stored procedures, particularly those working on large codebases where manual code review is insufficient.
Unlike basic syntax checking, plpgsql_check performs deep semantic analysis using PostgreSQL's own evaluation engine, catching errors that CREATE FUNCTION commands miss. It provides configurable warning levels, pragma directives for fine-grained control, and supports both active checking via function calls and passive checking during development.
Overview
plpgsql_check is a linter tool (does source code static analyze) for the PostgreSQL language plpgsql (the native language for PostgreSQL store procedures).
Use Cases
Best For
- Identifying semantic errors in PL/pgSQL functions before deployment
- Finding performance issues like implicit casts that prevent index usage
- Detecting SQL injection vulnerabilities in dynamic EXECUTE statements
- Maintaining large PL/pgSQL codebases with consistent quality standards
- Checking trigger functions with proper relation context
- Analyzing function dependencies and impact of schema changes
Not Ideal For
- Projects heavily relying on dynamic SQL or refcursors, as static analysis cannot verify runtime-assembled queries
- Environments where shared memory cannot be allocated (e.g., restricted cloud databases), limiting profiler functionality
- Teams needing zero-configuration, out-of-the-box linting without modifying PostgreSQL settings or using pragmas
- Applications that create and use temporary tables within PL/pgSQL function execution, which plpgsql_check cannot statically validate
Pros & Cons
Pros
Leverages PostgreSQL's internal parser to check SQL statements for column existence, type correctness, and relation references, ensuring errors found would actually occur at runtime.
Flags unwanted implicit casts that could prevent index usage and impact query performance, helping optimize code before deployment.
Attempts to identify SQL injection vulnerabilities in EXECUTE statements, though the README cautions it cannot catch all issues and is not a security audit substitute.
Allows granular control via function parameters (e.g., fatal_errors, performance_warnings) and pragma directives to tailor checks for specific code sections.
Includes built-in profiler for execution time analysis and tracer for debugging, reducing reliance on external tools for performance tuning.
Cons
Cannot analyze queries assembled at runtime or refcursors, leading to false positives or missed errors, requiring workarounds like pragmas or disabling checks.
Checking trigger functions requires manually specifying relation IDs and transition tables, adding overhead and potential for mistakes in multi-trigger environments.
Profiler requires shared memory allocation via shared_preload_libraries, which may not be feasible in all deployments, limiting profiling to session scope when unavailable.
The extension does not support updates; it must be dropped and reinstalled for new versions, causing downtime and complicating version management in production.
Frequently Asked Questions
Related Projects
CitusDistributed PostgreSQL as an extension
ZomboDBMaking Postgres and Elasticsearch work together like it's 2023
AGEGraph database optimized for fast analysis and real-time data processing. It is provided as an extension to PostgreSQL.
pg_cronRun periodic jobs in PostgreSQL
Found a gem we're missing?
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.