| 501 | Numeric literals between apostrophes | Placing numeric literals between apostrophes will cause unnecessary type conversions. It could also be that the literal should indeed be textual but the problem is in choosing the values. For instance, table Occupation has column occupation_code with the type VARCHAR(3). However, all the values in the column consist of digits (for instance, 1, 2, 3). Thus, it would have been better to a) use SMALLINT as the column type or b) use different occupation codes that contain additional symbols to digits. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 13:20 | MIT License | |
| 502 | Perhaps incorrect use of 'NULL' | Find Boolean expressions, queries, routines, and default values that refer to value 'NULL'. Perhaps NULL was intended instead. 'NULL' is a string (a value) but NULL is a special marker for denoting missing value. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 13:19 | MIT License | |
| 503 | Polymorphic routines | 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. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 13:12 | MIT License | |
| 504 | SQL functions that use optimistic approach for locking but do not return a value | Find SQL functions that use a hidden column of PostgreSQL tables - xmin - to implement optimistic locking but do not return any information to the invoker of the functions, i.e., whether the update/delete operation succeeded or not. The functions should let their invokers know as to whether the function succeeded in updating or deleting a row. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 13:06 | MIT License | |
| 505 | Invalid character class | PostgreSQL regular expressions do not have character classes word and letter. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:50 | MIT License | |
| 506 | Duplication of case insensitivity specification in a regular expression | Find regular expressions that use both case insensitive search operator ~* and case insensitivity modifier (?i). | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:49 | MIT License | |
| 507 | Routines with type casting | Make sure that your parameters have appropriate types in order to avoid unnecessary type casting. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:42 | MIT License | |
| 508 | 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 | |
| 509 | 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 | |
| 510 | 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 | |
| 511 | Multiple selects from the same table in a routine | Code should be as compact as possible and system should make as little work as necessary in order to solve a task. Thus, instead of selecting different fields of the same row with different SELECT INTO statements one should try to do it with one statement. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:10 | MIT License | |
| 512 | Explicit locking is probably not needed | You do not need explicit locking (LOCK TABLE or SELECT … FOR UPDATE) in case of routines that only search some data but do not modify any data and do not raise any exception. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:08 | MIT License | |
| 513 | No point to have in a procedure COMMIT without ROLLBACK or vice versa | If you end transaction in a procedure, then there should be a possibility to either commit or rollback the transaction based on some condition. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 12:00 | MIT License | |
| 514 | User-defined routines with dynamic SQL that are potential targets of the SQL injection attack | Find routines that have at least one input parameter, use dynamic SQL but do not escape the input arguments at all. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:58 | MIT License | |
| 515 | The use of xmin hidden column in views and routines | Find the number of views and materialized views that have a column with the xid type and the number of 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. | Sofware measure | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:56 | MIT License | |
| 516 | PL/pgSQL routines that use a cursor | Working with sets of rows rather than processing each row separately is more effective. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:54 | MIT License | |
| 517 | Using conditionals to determine the returned value | Use SQL language instead of PL/pgSQL where possible. Instead of using an IF statement, you can check as to whether the data modification succeeded or not by using the RETURNING clause in the data modification statement. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:52 | MIT License | |
| 518 | Procedures cannot have START TRANSACTION and SAVEPOINT | You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:50 | MIT License | |
| 519 | The reference to a database operation is missing from a comment | Find user-defined routines that comment does not contain a reference to a database operation that the routine implements. In case of routines that have been created based on the contracts of database operations, one should refer to the short identifier of the operation in the comment of the routine. The operation identifier must be in this case in the form OP, but other forms could be used as well. | Problem detection | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:39 | MIT License | |
| 520 | User-defined routines that use xmin hidden column | 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. | General | INFORMATION_SCHEMA+system catalog base tables | 2021-11-04 11:30 | MIT License | |