| 441 | User-defined non-trigger SQL and PL/pgSQL routines | Find the non-trigger SQL and PL/pgSQL routines in non-systemic schemas. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-12-23 02:40 | MIT License | |
| 442 | ROW level BEFORE DELETE and INSTEAD OF DELETE triggers that procedures refer to the row variable NEW | Do not write incorrect code. Variable NEW: "Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations." (PostgreSQL documentation) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 14:25 | MIT License | |
| 443 | STATEMENT level triggers that refer to the values of row variables NEW or OLD | Find STATEMENT level triggers that refer to the values of row variables NEW or OLD. NEW and OLD are special variables that can only be used in row-level trigger procedures. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 14:23 | MIT License | |
| 444 | ROW level BEFORE INSERT and INSTEAD OF INSERT triggers that procedures refer to the row variable OLD | Do not write incorrect code. Variable OLD: "Data type RECORD; variable holding the old database row for UPDATE/DELETE operations in row-level triggers. This variable is null in statement-level triggers and for INSERT operations." (PostgreSQL documentation) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 14:22 | MIT License | |
| 445 | Overloading | Make sure that there is genuine overloading instead of duplication or dead code. "In some programming languages, function overloading or method overloading is the ability to create multiple functions of the same name with different implementations." (Wikipedia) In PostgreSQL one can do it automagically by having multiple routines with the same name but different parameters in the same schema. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 11:40 | MIT License | |
| 446 | Perhaps duplicate check of empty strings (ver 2) | Find columns that have a check that prevents the empty string in the column but there is already another check on the column that enforces the constraint. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-20 11:30 | MIT License | |
| 447 | Permitting in a column only empty strings and strings that consist of whitespace characters | Find table CHECK constraints that permit in a column only empty strings or strings that consist of only whitespace characters. | Problem detection | INFORMATION_SCHEMA only | 2021-12-19 15:10 | MIT License | |
| 448 | Duplicate check of empty strings | Find columns that have a check that prevents the empty string in the column but there is already another check on the column that enforces the constraint. If there is a constraint description!~'^[[:space:]]*$', then it covers the constraint description!='' and the latter becomes redundant. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-19 15:08 | MIT License | |
| 449 | Reasonable upper bound to the length of textual values is missing | Find non-foreign key base table columns that are not used to record comments/descriptions/explanations etc. and that have TEXT or VARCHAR type without restrictions to the field size (field size in case of VARCHAR or a CHECK constraint). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-16 12:32 | MIT License | |
| 450 | PL/pgSQL functions with consecutive RETURN clauses | Find PL/pgSQL functions with consecutive RETURN clauses. Only the first RETURN will be used, others are unnecessary. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-16 11:38 | MIT License | |
| 451 | All exclude constraints | Find all exclude constraints. | General | system catalog base tables only | 2021-12-16 11:21 | MIT License | |
| 452 | Consistency of CHECK constraint name and content | Find all CHECK constraints that name contains a digit. Make sure that the name and the Boolean expression are consistent. | General | INFORMATION_SCHEMA only | 2021-12-10 13:21 | MIT License | |
| 453 | System-generated table constraint names (constraints that involve one column) | Find the names of database constraints that have been system-generated. Additional restrictions are that the constraints must involve only one column and are associated directly with a table (not through a domain). Names should follow the same style. If there is a mix of system-generated and user-defined names, then the style is most probably different. | Problem detection | system catalog base tables only | 2021-12-10 12:47 | MIT License | |
| 454 | Mixing Concat and Coalesce | Find cases where different means are used to deal with NULLs in case of concatenating texsts. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-08 14:25 | MIT License | |
| 455 | Perahaps Coalesce invocation is missing or Concat should be used | Find user-defined routines and derived tables (views/materialized views) that have a subquery that invokes || operator but does not use Coalesce function to ensure that the arguments are not NULL. In PostgreSQL expression value || NULL returns NULL. In order to get value as the result, one has to replace NULL with a value (empty string) by using, for instance, Coalesce function (an alternative is to use a CASE expression). Instead of || + Coalesce, one could use Concat, Concat_ws, or Format functions for the concatenation. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-08 11:51 | MIT License | |
| 456 | 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 | |
| 457 | Mixing Concat and || | Find cases where different means are used to concatenate text within the same object. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-12-03 14:36 | MIT License | |
| 458 | Cycles in relationships | Find as to whether foreign key constraints between tables form a cycle. For instance, if table C refers to table B, table B refers to table A, table A refers to table C and all the foreign key columns are mandatory, then one cannot add data to the tables. Is cycle value in the result should not contain value TRUE. One should check manually as to whether the foreign key columns are mandatory or optional. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-28 15:15 | MIT License | |
| 459 | Each table is both referencing and referenced table (perhaps there is a cycle in relationships) | Find as to whether the relationships between tables form a complete bidirected graph. Tables are vertices in the graph. There is a directed edge between two vertices if one of the tables refers to another through foreign key relationship on mandatory columns. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-28 14:04 | MIT License | |
| 460 | Short cycle (columns) | Find cases where two candidate keys of the same table that are also foreign keys reference to each other. | Problem detection | system catalog base tables only | 2021-11-28 02:08 | MIT License | |