| 721 | Routine body only in uppercase | Uppercase means screaming and having code entirely in uppercase makes its reading more difficult. On the other hand, it would be a good idea to have keywords in uppercase. Find routines that body contains a SQL data manipulation statement (which shouldn't be entirely in uppercase) but still the body is completely in uppercase. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 20:12 | MIT License | |
| 722 | 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 | |
| 723 | Routine for reading data uses another routine to read some data | Find routines that only read data but invoke some other routine to read some more data. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-12-10 17:10 | MIT License | |
| 724 | Routines that use old syntax for limiting rows | Find PL/pgSQL routines and SQL routines that do not have SQL-standard body that use unstandardized LIMIT clause instead of standardized FETCH FIRST n ROWS clause. The query excludes routines that are a part of an extension. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2023-10-29 11:34 | MIT License | |
| 725 | Routines with BOOLEAN return type that do not have a good name | The prefic of the name should be "is_" or "has_" or "can_" (in English) or "on_" (in Estonian). Worse: check_rights. Better: has_rights. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2024-01-13 20:41 | MIT License | |
| 726 | Routines with INSERT statements that are sensitive towards the order of columns | INSERT statements shouldn't be sensitive towards the order of columns. If one changes the order of columns in a table then these statements must be rewritten. Otherwise the code will not work or works incorrectly. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 16:15 | MIT License | |
| 727 | Routines without an action | Find routines that body does not contain any action. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-05 12:14 | MIT License | |
| 728 | ROW-level BEFORE and INSTEAD OF triggers with RETURN NULL | Such triggers effectively cancel data modification. It might be correct but could also be a mistake. "Row-level triggers fired BEFORE can return null to signal the trigger manager to skip the rest of the operation for this row (i.e., subsequent triggers are not fired, and the INSERT/UPDATE/DELETE does not occur for this row). " (PostgreSQL documentation) "INSTEAD OF triggers (which are always row-level triggers, and may only be used on views) can return null to signal that they did not perform any updates, and that the rest of the operation for this row should be skipped (i.e., subsequent triggers are not fired, and the row is not counted in the rows-affected status for the surrounding INSERT/UPDATE/DELETE). " (PostgreSQL documentation) | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 729 | 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 | |
| 730 | 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 | |
| 731 | 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 | 2021-12-31 17:29 | MIT License | |
| 732 | ROW level BEFORE triggers with RAISE EXCEPTION but without RETURN NULL | Although RAISE EXCEPTION stops the execution it would be a good style to still return. In this case the return should bring back NULL, i.e., the row will not be processed further | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |
| 733 | 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 | |
| 734 | Rules with the same name within the same schema | Find names of rules that are used within the same schema more than once. Give different triggers different names. | Problem detection | system catalog base tables only | 2021-02-25 17:30 | MIT License | |
| 735 | search_path should not be between quotation marks | Write security definer functions securely. Give to the DBMS correctly information about the sequence of schemas that constitute the search path. You shouldn't write search path value between quotation marks or apostrophes. Thus, instead of writing SET search_path = "public, pg_temp"; or SET search_path = 'public, pg_temp'; you should write SET search_path = public, pg_temp; | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-03 21:10 | MIT License | |
| 736 | SECURITY DEFINER procedures cannot end transactions | You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:12 | MIT License | |
| 737 | 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 | |
| 738 | SELECT * in a routine body | SELECT statement should list the columns not use SELECT * to return data from all the columns. Firstly, it ensures, that the query asks only data that is really needed by the routine. It means less data that the DBMS has to fetch and pass to the routine. It could also mean that the DBMS can answer to a query based on an index without reading table blocks. Secondly, it documents the data that is returned by the query. The query does not consider objects that are a part of an extension. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 15:03 | MIT License | |
| 739 | Sequences that are not owned by a table column | Find sequence generators that are not owned by a table column, i.e., if one drops the table or the column, then the sequence generator stays in place. | Problem detection | system catalog base tables only | 2023-10-06 14:40 | MIT License | |
| 740 | SET DEFAULT compensatory action is unsuitable | Find foreign keys with SET DEFAULT compensatory action where the foreign key column does not have a default value. Compensatory actions cannot make changes that violate integrity constraints in a database. SET DEFAULT means that there shoud be a default value at the foreign key column. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-02-25 17:30 | MIT License | |