Filter Queries

Found 997 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
781 Views with the WITH LOCAL CHECK OPTION constraint Find updatable views that have WITH LOCAL CHECK OPTION constraint. The predicate of a view is the conjunction of the predicates of its (directly and indirectly) underlying tables (both base tables and derived tables) as well as the predicate of the view itself. In case of using WITH LOCAL CHECK OPTION constraint "New rows are only checked against the conditions defined directly in the view itself. Any conditions defined on underlying base views are not checked (unless they also specify the CHECK OPTION)." (PostgreSQL manual) Thus, use instead WITH CASCADED CHECK option to instruct the system to check new rows against the entire predicate of the view. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
782 Updatable views with WHERE clause that do not have WITH CHECK OPTION constraint Find updatable views that restrict rows, i.e., have WHERE clause, but do not have WITH CHECK OPTION constraint. WITH CHECK OPTION constraint prevents updates through the view that violate the predicate of the view. Such updates must be prevented. Problem detection INFORMATION_SCHEMA only 2024-01-14 17:11 MIT License View
783 UPDATE triggers where updated columns have not been specified (the trigger could executed too often) Find UPDATE triggers where updated columns are not specified. These triggers could be executed too often because unneeded executions are not prevented. Problem detection INFORMATION_SCHEMA only 2024-12-23 09:53 MIT License View
784 UPDATE triggers where WHEN clause has not been specified (the trigger could executed too often) Find UPDATE triggers where WHEN clause is not specified. These triggers could be executed too often because unneeded executions are not prevented. Problem detection INFORMATION_SCHEMA only 2024-12-23 09:54 MIT License View
785 Grantable usage privileges Find usage privileges that the carrier of the privilege can in turn grant to others, i.e., the privileges have been given WITH GRANT OPTION. The number of privileges that can be passed on should be as small as possible. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-01-07 13:43 MIT License View
786 Unused composite types (for table columns, typed tables, input and output parameters) Find user-defined composite types that are not used in case of any table, column, and routine (input or otput) parameter (as their type). Do not keep in your database elements that are not needed by anybody. These should be put in use or dropped, otherwise these are dead code. Problem detection system catalog base tables only 2021-02-25 17:30 MIT License View
787 User-defined non-trigger routines without parameters Find user-defined non-trigger routines with no parameters. General INFORMATION_SCHEMA+system catalog base tables 2021-11-03 20:05 MIT License View
788 A setter does not update a table Find user-defined non-trigger SQL and PL/pgSQL routines that name starts with "set" (but not with "setting") but do not contain a UPDATE statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-27 18:35 MIT License View
789 The name of the routine does not match with the action of the routine Find user-defined non-trigger SQL and PL/pgSQL routines where the beginning of the name of the routine indicates a certain action inside the routine (INSERT, UPDATE, or DELETE) but there is no such statement in the routine body. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-19 14:37 MIT License View
790 Coalesce/Concat need at least two arguments Find user-defined routines and derived tables (views/materialized views) that have a subquery that contain an invocation of Coalesce or Concat function with just one argument. You should use the Coalesce and Concat functions correctly by invoking these with at least two arguments. If one invokes these functions with one argument, then the functions will return the input value. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-12-08 11:49 MIT License View
791 Perhaps Count(*) is wrongly used Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes Count aggregate function like this - Count(*), uses outer join, and grouping. In case of grouping you do not want to get an answer that an empty group contains one member. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-25 16:40 MIT License View
792 Perahaps Coalesce invocation is missing or Concat should be used Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes || operator but does not use Coalesce function to ensure that the arguments are not NULL. In PostgreSQL expression value || NULL returns NULL. In order to get value as the result, one has to replace NULL with a value (empty string) by using, for instance, Coalesce function (an alternative is to use a CASE expression). Instead of || + Coalesce, one could use Concat, Concat_ws, or Format functions for the concatenation. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-12-08 11:51 MIT License View
793 A routine is invoked only once Find user-defined routines that are invoked by exactly one user-defined routine. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-22 14:53 MIT License View
794 The reference to a database operation is missing from a comment Find user-defined routines that comment does not contain a reference to a database operation that the routine implements. In case of routines that have been created based on the contracts of database operations, one should refer to the short identifier of the operation in the comment of the routine. The operation identifier must be in this case in the form OP, but other forms could be used as well. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-04 11:39 MIT License View
795 Invocation of a system-defined routine without providing any arguments Find user-defined routines that contain an invocation of a system-defined function without providing any argument. The query considers all aggregate functions as well as some popular scalar functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-22 13:00 MIT License View
796 Unnamed columns in routines Find user-defined routines that contain a SQL statement that does not give the name to a column in a SQL statement. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-22 00:04 MIT License View
797 User-defined routines that read data Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query. General INFORMATION_SCHEMA+system catalog base tables 2023-12-21 15:00 MIT License View
798 Updating or deleting data in a routine without restricting rows Find user-defined routines that contain UPDATE or DELETE statement but do not contain any WHERE clause, meaning that at least one UPDATE or DELETE operation influences all the rows of a table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:21 MIT License View
799 Consistency of comments of routines Find user-defined routines that have a comment registered by the COMMENT statement and a comment within the routine body. Make sure that there are no inconsistencies between the comments. General INFORMATION_SCHEMA+system catalog base tables 2024-12-19 10:53 MIT License View
800 Explicit locking is missing (2) Find user-defined routines that have a subquery in a DELETE or UPDATE statement without the FOR UPDATE clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-06 12:15 MIT License View