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
621 Routine body with ordering the query result based on positional references Find routines where the query result is sorted based on the column number in the SELECT clause. Such query is sensitive towards changing the order of columns in the SELECT clause, i.e., if one changes the order of columns in the SELECT clause, then one must change the numbers in the ORDER BY clause as well, otherwise the query will produce undesired order of rows. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-10-31 15:23 MIT License View
622 Routines that can be invoked with a variable number of arguments Find routines with a VARIADIC parameter. These are routines that take as input an undefined number of arguments where the argument that is an undefined number are all of the same type and are the last input arguments. General INFORMATION_SCHEMA+system catalog base tables 2021-11-04 12:37 MIT License View
623 Prefer Polymorphism to If/Else or Switch/Case Find routines with IF/ELSE or SWITCH/CASE statements. If your routine has a multipart IF/CASE statement, then perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:31 MIT License View
624 Prefer Polymorphism to If/Else or Switch/Case (2) Find routines with multiple raise exception commands. Perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-11-29 13:39 MIT License View
625 Perhaps updating of modification time is missing Find routines with SQL-standard body that seem to update data in a table that has a column for modification time but the routine does not seem to update the modification time while updating the row and the table does not seem to have an UPDATE trigger that changes the modification time. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-23 11:55 MIT License View
626 User-defined routines with the same parameters (same name and type) regardless of the order of parameters Find routines with the same parameters (same name and type) regardless of the order of parameters. Make sure that there is no accidental duplication. The query helps users to group together routines that probably have related tasks. General INFORMATION_SCHEMA+system catalog base tables 2020-11-06 14:51 MIT License View
627 Perhaps a reference to the variable OLD is missing Find row level before delete triggers that only task is not to raise an exception and where the variable OLD is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-21 19:29 MIT License View
628 Perhaps a reference to the variable NEW is missing Find row level before insert and before update triggers that only task is not to raise an exception and where the variable NEW is not used in the trigger routine outside the RETURN clause. Problem detection INFORMATION_SCHEMA+system catalog base tables 2023-12-21 20:34 MIT License View
629 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 2024-12-16 12:41 MIT License View
630 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 2022-12-08 15:59 MIT License View
631 Perhaps incorrect WHEN clause Find row level triggers that have action condition (WHEN clause) but the Boolean expression in its specifications does not refer to neither NEW nor OLD variable. Problem detection INFORMATION_SCHEMA only 2021-02-25 17:30 MIT License View
632 Row level triggers that update or delete data Find row level triggers that update or delete data. Problem detection INFORMATION_SCHEMA+system catalog base tables 2024-12-11 12:27 MIT License View
633 Incorrect password hash update Find row level update triggers that incorrectly implement update of password hash. It should not be that the new password hash is calculated based on the existing hash. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-02-25 17:29 MIT License View
634 Rules with the same name in different schemas Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication. General system catalog base tables only 2020-11-06 14:51 MIT License View
635 Gratuitous context in the names of schema objects Find schema objects that name starts with the schema name and then has at least one more symbol. "Shorter names are generally better than longer ones, so long as they are clear. Add no more context to a name than is necessary" (Robert C. Martin, Clean Code) Shema is a namespace. There cannot be in the same schema two schema objects that belong to the same name class and have the same name. Problem detection system catalog base tables only 2024-12-21 17:30 MIT License View
636 PUBLIC has the USAGE privilege of a schema Find schemas where PUBLIC has the usage privilege. Problem detection INFORMATION_SCHEMA+system catalog base tables 2021-11-23 17:52 MIT License View
637 Empty schemas Find schemas without schema objects. Problem detection INFORMATION_SCHEMA+system catalog base tables 2022-11-04 15:30 MIT License View
638 All non-unique indexes Find secondary indexes that have been created in the database. General INFORMATION_SCHEMA+system catalog base tables 2020-12-23 11:50 MIT License View
639 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 2021-11-04 10:44 MIT License View
640 Potentially unused sequence generators Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code. Problem detection INFORMATION_SCHEMA only 2021-03-07 20:56 MIT License View