PostgreSQL Query Catalog

This query identifies superfluous trim() function calls within CHECK constraints where the validation is performed by a regular expression that disallows whitespace-only strings. A constraint using the pattern column !~ '^[[:space:]]*$' already provides comprehensive validation against empty or whitespace-only strings by anchoring the check to the start (^) and end ($) of the string. The trim() function is a pre-processing step that does not alter the boolean outcome of this specific regex match, making the expression trim(column) !~ '^[[:space:]]*$' functionally equivalent to the simpler column !~ '^[[:space:]]*$'. Removing the unnecessary function call improves clarity and simplifies the constraint.

Find base table columns that have the DECIMAL/NUMERIC type, but do not have precision and scale specified. "Specifying: NUMERIC without any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale."

This query identifies columns intended to store registration or modification timestamps that are currently defined as optional (nullable). Since creation and update times are crucial for data auditing, these columns should typically have a NOT NULL constraint.

Find expressions that use a regular expression with a like predicate pattern.

Keywords in uppercase improve readability.

Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase.

Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows.

This query analyzes the call graph of user-defined routines to identify nested data retrieval patterns. It flags routines that are operationally read-only (performing no DML) but rely on invoking other routines to access additional data. This indicates a layered architecture where data access logic is encapsulated and chained. Identifying these routines is essential for performance profiling, as the total cost of execution is distributed across the call stack rather than being contained within a single procedure body.

This query identifies user-defined routines that may be susceptible to concurrency anomalies due to a lack of explicit locking. PostgreSQL utilizes Multi-Version Concurrency Control (MVCC), where SELECT statements do not block data modification operations. Consequently, routines that read data to inform subsequent modifications without acquiring row-level locks (e.g., FOR UPDATE, FOR SHARE) or using isolation levels higher than READ COMMITTED are prone to race conditions. This query flags such routines for review to ensure transactional integrity is maintained.

This query identifies PL/pgSQL and SQL routines with no SQL-standard bodies that use the non-standard LIMIT clause for row limitation. It flags these routines because the official, cross-platform SQL standard specifies FETCH FIRST n ROWS ONLY for this purpose. Adhering to the standard improves code portability and maintainability. To ensure relevance, the query intelligently excludes routines that are part of installed extensions, focusing only on user-defined code.

This query audits the naming conventions of routines that return a BOOLEAN data type. It identifies routines that do not adhere to the recommended semantic prefix convention (i.e., starting with is_, has_, can_, or on_). A function name should represent a state or a question (e.g., has_rights) rather than an action (e.g., check_rights). Enforcing this standard makes the routine's purpose and return value immediately obvious from its name.

INSERT statements shouldn't be sensitive towards the order of columns. If one changes the order of columns in a table then these statements must be rewritten. Otherwise the code will not work or works incorrectly.

This query identifies SQL routines that exhibit a dangerous combination of state-changing side effects (DML) and a static return value (either a constant literal or an unmodified input parameter). The function's name and signature often imply that the return value is the result of its operations (e.g., a new balance, a generated ID). However, the static return value contradicts this, creating a semantic disconnect between the routine's name and its contract. This is a significant design flaw that can lead to subtle but critical bugs, as the calling code may act on a return value that does not accurately reflect the database state after the routine's execution.

Find routines that body does not contain any action.

Do not write incorrect code. Variable NEW: "Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations." (PostgreSQL documentation)

Do not write incorrect code. Variable OLD: "Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations." (PostgreSQL documentation)

This query identifies row-level BEFORE triggers on base tables that execute a RETURN NULL statement without raising a corresponding exception. In PostgreSQL, returning NULL from a BEFORE trigger silently aborts the pending INSERT, UPDATE, or DELETE operation for the current row. Unlike an exception, which alerts the calling application to the failure, a silent cancellation allows the transaction to proceed as if successful, but with the data modification discarded. This behavior is often unintentional (e.g., a forgotten RETURN NEW) and poses a significant risk of data loss and difficult-to-debug application logic errors.

Find ROW level BEFORE triggers that check a condition based on other rows, raise an exception but do not return the row if the condition check succeeds, i.e., exception is not raised.

Although RAISE EXCEPTION stops the execution it would be a good style to still return. In this case the return should bring back NULL, i.e., the row will not be processed further

Find row level BEFORE UPDATE triggers that do not return the new row version. Exclude triggers that raise WARNING/EXCEPTION.