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
681 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 2025-11-07 10:11 MIT License View
682 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 2025-11-07 10:11 MIT License View
683 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 2025-11-07 10:11 MIT License View
684 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 2025-11-07 10:11 MIT License View
685 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 2025-11-07 10:11 MIT License View
686 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 2025-11-07 10:11 MIT License View
687 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 2025-11-07 10:11 MIT License View
688 Incorrect reference to a system-defined function in the routine body Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
689 User-defined routines that use dynamic SQL to execute data manipulation statements Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
690 User-defined routines that use keyword DECLARE but do not declare anything Find user-defined routines that use keyword DECLARE but do not declare anything. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
691 User-defined routines that use md5 hash for other purposes than generating test data Find user-defined routines that use md5 hashes for the security purposes. Nowadays such hashes can be calculated too quickly and its use should be avoided at least for hashing passwords. Exclude routines that invoke both md5 function and generate_series function and are thus probably used to generate test data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
692 Not equals check in unstandardized way Find user-defined routines that use != operator to test as to whether two values are not equal. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
693 Too few rounds in case of calculating the hash Find user-defined routines where gen_salt function is used with the number of rounds that is smaller than 12. The number should be adjusted based on the hardware where the system resides. Password hashing should take at least 250 ms. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
694 Duplicate user-defined routines Find user-defined routines with the exact duplicate body and parameters. There should not be multiple routines with exactly the same body and parameters (name, type). Having such duplicates is redundancy. Do remember that the same task can usually be solved in multiple different ways. Thus, the exact copies of routine bodies are not the only possible duplication. Moreover, it could be that different routines that solve the same task have different parameter names (but the parameters have the same types, ordinal positions, default values or the routines have different order of parameters). Thus, the query does not find all the duplications. Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
695 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
696 All rules Find user-defined rules for rewriting data manipulation language statements. Rules should be used only for the tasks that cannot be achieved in a declarative manner, i.e., for example, by declaring a constraint. General system catalog base tables only 2025-11-07 10:11 MIT License View
697 Transform method does not return Find user-defined SQL and PL/pgSQL routines that do not return a value although the name suggest that it should return a value (contains "_to_"). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
698 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 2025-11-07 10:11 MIT License View
699 Validation method does not confirm 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 "validate" or "check"). Problem detection INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View
700 All user triggers that are associated with tables Find user-defined triggers that react to data modifications in tables. Triggers should be used only for the tasks that cannot be achieved in a declarative manner, i.e., by declaring a constraint. Triggers of the same table with the same event_manipulation, action_timing, and action_orientation are sorted based on the trigger name. This is the order of execution of triggers. General INFORMATION_SCHEMA+system catalog base tables 2025-11-07 10:11 MIT License View