PostgreSQL Query Catalog

Find regular expressions where choice between alternatives has no brackets. Thus, instead of '(a|b|c)' there is 'a|b|c'. An example: '^a|b|c$' -permits in the string symbol "|" but '^(a|b|c)$' does not permit in the string symbol "|".

Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]].

Find regular expressions where within the same specification of a character class the character class alnum as well as 0-9, \d, A-Z, or a-z has been defined.

Find roles that a member can grant to others, i.e., the role has been granted with ADMIN OPTION. The number of privileges that can be passed on should be as small as possible.

Find routine parameters that based on the name seem to hold truth values. Find parameters that name starts with "is_" or "has_" or "can_" or "on_" and that do not have Boolean type.

Find routine 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.

Find routines that body does not contain any action.

Find routines that contain a statement that sorts rows based on random values but do not limit the number of rows. This is unnecessary because without sorting the rows are returned in a unspecified order. Sorting based on random values is a computationally expensive operation.

Find routines that contain a statement that sorts rows based on random values. This can be used to find a random subset of rows. It is a computationally expensive operation.

Find routines that contain a UPDATE or a DELETE statement that search condition refers to the xmin column. If one uses optimistic approach for dealing with the concurrent modifications of data, then xmin values should be presented by views and used in routines that modify or delete rows.

Find routines that have a parameter (input or output) that can have values from different types. This parameter has one of the PostgreSQL polymorphic types. The set of polymorphic types in PostgreSQL is a proper subset of its pseudo-types. The use of such a parameter allows a single routine definition to operate on many different data types, with the specific data type(s) being determined by the data types actually passed to it in a particular call.

Find routines that have at least one input parameter, use dynamic SQL but do not escape the input arguments at all.

Find routines that subquery contains terms "foo", "bar", "foobar", or "baz".

Find routines that use dynamic SQL. Make sure that dynamic SQL is indeed needed, i.e., the task cannot be solved with static SQL. Make sure that the routine is protected against attacks that use SQL injection method.

Find routines that use temporal functions CURRENT_TIMESTAMP, LOCALTIMESTAMP, or now() that is inconsistent with the default values of the columns that are used by the routine, e.g., function uses a column with the default value LOCALTIMESTAMP but the routine uses function CURRENT_TIMESTAMP or now().

Find routines where comments contain TODO phrase.

Find routines where in SELECT or UPDATE statements references to columns are prefixed with references to the table schema. Referring to schema in this context bloats the code.

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.

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.

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.