PostgreSQL Query Catalog

Project on GitHub
Filter Queries

Found 191 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
1 A getter does not return a value Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (starts with "get"). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-01-06 14:24 MIT License View
2 All parameters with DEFAULT values Find parameters of user-defined routines that have a default value. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
3 All table functions Find all functions that return a set of rows. General INFORMATION_SCHEMA+system catalog base tables 2023-10-29 11:39 MIT License View
4 AND takes precedence over OR Make sure that Boolean expressions take into account precedence rules of Boolean operators. AND operator has precedence over OR operator. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
5 A non-parameterized table function instead of a view Find table functions that do not have any parameters. Prefer simpler and more portable solutions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-03 21:16 MIT License View
6 A predefine character class has been incorrectly specified Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:]. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-23 12:09 MIT License View
7 Are there enough routines that implement database operations? Find user-defined routines that implement database operations (comment refers to an operation) but show these only if there are at least eight such routines. Contracts of database operations are specified in the system analysis documentation. The contracts apply the idea of design by contract in the field of databases. General INFORMATION_SCHEMA+system catalog base tables 2021-11-03 20:07 MIT License View
8 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
9 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
10 A table has the same name as a routine Find table names that are the same as some routine name. Use different names to avoid confusion. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
11 Case insensitive search Refers to the column pg_proc.prokind and thus works starting from PostgreSQL 11. Find user-defined routines and derived tables (views/materialized views) that have a subquery with case insensitive search (by using the upper or lower function or ILIKE predicate or (?i) modifier of a regular expression pattern). General INFORMATION_SCHEMA+system catalog base tables 2021-10-25 16:55 MIT License View
12 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
13 Comments of routines Find comments of user-defined routines (functions or procedures) that are registered in the system catalog witht a COMMENT statement. Make sure that the comments give relevant, useful, and correct information. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
14 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
15 Coverage by routines that have the SQL-standard body Find for each base table the list of routines (functions and procedures) that refer to the base table. If the database is used through the public database interface (virtual data layer), then, ideally, each table is referred from at least one routine. General INFORMATION_SCHEMA+system catalog base tables 2021-11-05 17:00 MIT License View
16 Database objects of the same type and case insensitive name in the same container Find database objects with the same type and case insensitive name in the same container. It can only happen if some of the names are case insensitive and others are case sensitive. For instance, the same schema can contain the table "Client" and client Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-21 17:13 MIT License View
17 Database object that belong to the public interface (virtual data layer) and that names contain the letters õäöüÕÄÖÜ Find database object that belong to the database public interface (virtual data layer - consists of routines and derived tables) and that names contain the letters õäöüÕÄÖÜ (Estonian letters with a diacritic). These letters belong to the Estonian alphabet but do not belong to the ASCII character set. Although permitted by the DBMS, such letters might make it more difficult to use the interface by other programs. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:30 MIT License View
18 Derived table uses a function to get data from another table Find views that use a function to get data from another table. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-24 14:36 MIT License View
19 Deterministic (immutable) functions that do not have input parameters Find deterministic functions that do not have any input parameters. Make sure that it is correct because in general a deterministic function must calculate a value based on input. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-09 19:53 MIT License View
20 Deterministic (immutable) functions that do not return a value Find deterministic (immutable) functions that do not return a value. This goes against the idea of deterministic functions. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-20 19:33 MIT License View