PostgreSQL Query Catalog

Project on GitHub
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
801 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
802 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 2021-10-25 16:48 MIT License View
803 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 2021-11-04 12:39 MIT License View
804 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 2021-11-05 14:47 MIT License View
805 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 2021-10-31 15:23 MIT License View
806 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 2023-12-13 14:16 MIT License View
807 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 2021-10-25 17:21 MIT License View
808 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 2021-11-05 11:59 MIT License View
809 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 2021-10-25 16:11 MIT License View
810 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 2023-12-22 12:30 MIT License View
811 Is does not return a boolean Find user-defined SQL and PL/pgSQL non-trigger routines that do not return a truth value (for instance, returns an integer or does not return a value at all) although the name suggest that it should return a truth value (TRUE or FALSE). Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-01-06 14:25 MIT License View
812 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 2023-01-14 19:37 MIT License View
813 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
814 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 2023-01-06 14:25 MIT License View
815 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 2021-01-19 11:27 MIT License View
816 All user-defined TOAST-able types Find user-defined types in case of which the system can use the TOAST technique, i.e., save the value in a compressed form or store it in a automatically-created secondary table, which is hidden from the database user (TOAST table). General system catalog base tables only 2020-11-06 14:51 MIT License View
817 User-defined derived tables Find user-defined views and materialized views. Pay attention to the outer join operations. One should use these if and only if there is a real need for them. Otherwise they just reduce performance. On the other hand, pay attention that outer join is used where it is logically needed. General INFORMATION_SCHEMA+system catalog base tables 2023-12-11 15:23 MIT License View
818 User-defined routines that implement UPSERT operation Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations. General INFORMATION_SCHEMA+system catalog base tables 2021-10-25 17:02 MIT License View
819 User-defined routines that produce a temporary table Find user user-defined routines that produce a temporary table General INFORMATION_SCHEMA+system catalog base tables 2021-10-25 16:25 MIT License View
820 Derived tables that present data in json or xml format Find views and materialized views that present data in json or xml format. Instead of recording data in this format in base tables one could generate the data value on the fly based on data that has been recorded in base tables. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View