PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 1040 queries.

  • All the queries about database objects contain a subcondition to exclude from the result information about the system catalog.
  • Although the statements use SQL constructs (common table expressions; NOT in subqueries) that could cause performance problems in case of large datasets it shouldn't be a problem in case of relatively small amount of data, which is in the system catalog of a database.
  • Statistics about the catalog content and project home in GitHub that has additional information.

# Name Goal Type Data source ▲ Last update License
661 Routines with BOOLEAN return type that do not have a good name 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-13 13:53 MIT License View
662 Routines with INSERT statements that are sensitive towards the order of columns 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
663 Routines with non-deterministic side effects and static return values 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-15 10:59 MIT License View
664 Routines without an action Find routines that body does not contain any action. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
665 Routines with the same name and parameters in different schemas Find user-defined routines with the same name and parameters (including the order of parameters) in different schemas. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
666 Routines with type casting Make sure that your parameters have appropriate types in order to avoid unnecessary type casting. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
667 ROW level BEFORE DELETE and INSTEAD OF DELETE triggers that procedures refer to the row variable NEW 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) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
668 ROW level BEFORE INSERT and INSTEAD OF INSERT triggers that procedures refer to the row variable OLD 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) Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
669 Row-level BEFORE triggers on base tables with RETURN NULL cancellation logic 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-20 12:29 MIT License View
670 ROW level BEFORE triggers that do not return a row if a check succeeds 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. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
671 ROW level BEFORE triggers with RAISE EXCEPTION but without RETURN NULL 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 Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
672 ROW level BEFORE UPDATE triggers that do not return the new row Find row level BEFORE UPDATE triggers that do not return the new row version. Exclude triggers that raise WARNING/EXCEPTION. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
673 Row level triggers that update or delete data This query identifies row-level triggers that contain UPDATE or DELETE statements within their execution body. Embedding data modification logic directly within row-level triggers introduces implicit side effects that can complicate transaction management and debugging. Furthermore, this pattern significantly increases the risk of causing cascading trigger chains or infinite recursion loops, potentially degrading system performance and stability. Such logic should be carefully audited to ensure it is strictly necessary and correctly implemented. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-12-14 12:20 MIT License View
674 Row-level triggers with RETURN NULL cancellation logic This query identifies row-level BEFORE and INSTEAD OF triggers that explicitly RETURN NULL. In PostgreSQL's trigger execution model, this return value acts as a cancellation signal. For BEFORE triggers on tables, it aborts the operation for the current row, preventing the INSERT, UPDATE, or DELETE and suppressing subsequent triggers. For INSTEAD OF triggers on views, it signals that no modification was performed. While this behavior can be used for conditional logic (e.g., silently ignoring invalid rows), it presents a risk of unintended data loss or logic errors if used incorrectly. These triggers should be audited to ensure the cancellation behavior is intentional and correctly implemented. General INFORMATION_SCHEMA+system catalog base tables 2025-11-20 11:41 MIT License View
675 search_path should not be between quotation marks Write security definer functions securely. Give to the DBMS correctly information about the sequence of schemas that constitute the search path. You shouldn't write search path value between quotation marks or apostrophes. Thus, instead of writing SET search_path = "public, pg_temp"; or SET search_path = 'public, pg_temp'; you should write SET search_path = public, pg_temp; Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
676 SECURITY DEFINER procedures cannot end transactions You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
677 SECURITY INVOKER routines that access data Find SECURITY INVOKER routines that read rows from a table, add rows to a table, update rows in a table, or delete rows from a table. Better to have for these purposes SECURITY DEFINER routines, which make it possible to give to the users privileges to only execute routines without having rights to access their underlying tables. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
678 SELECT * in a routine body SELECT statement should list the columns not use SELECT * to return data from all the columns. Firstly, it ensures, that the query asks only data that is really needed by the routine. It means less data that the DBMS has to fetch and pass to the routine. It could also mean that the DBMS can answer to a query based on an index without reading table blocks. Secondly, it documents the data that is returned by the query. The query does not consider objects that are a part of an extension. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
679 Sequence generators not needed Find possible classifier tables that have a column with a sequence generator. Such tables should have natural keys instead of surrogate keys. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
680 SET DEFAULT compensatory action is unsuitable Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View