PostgreSQL Query Catalog

Find user-defined non-trigger routines with no parameters.

You should follow the principle of least privilege and thus not have in your database user-defined routines that execution privilege is granted to PUBLIC, i.e., to all the database users now and in the future. By default, PostgreSQL gives routine execution privileges to PUBLIC.

Avoid creating user-defined routines that have the same name as some system-defined routine because it may cause confusion.

Find user-defioned routines that implement UPSERT operation. Make sure that it is consistent with the contracts of database operations.

Find user user-defined routines that produce a temporary table

Find user-defined routines that contain SELECT … FROM or PERFORM … FROM operations. PostgreSQL uses multiversion concurrency control (MVCC). Therefore, SELECTs do not block modifications and vice versa. One has to take steps to achieve correct behaviour of data access code. In addition, one should not ask data with multiple queries if it is possible to achieve the result with only one query.

Find user-defined routines that use dynamic SQL to execute data manipulation statements (SELECT, INSERT, UPDATE, DELETE).

Find user-defined routines that use keyword DECLARE but do not declare anything.

Find user-defined routines that use md5 hashes for the security purposes. Nowadays such hashes can be calculated too quickly and its use should be avoided at least for hashing passwords. Exclude routines that invoke both md5 function and generate_series function and are thus probably used to generate test data.

Use parameter names instead of positional references to improve code evolvability.

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 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 have at least one input parameter, use dynamic SQL but do not escape the input arguments at all.

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.

Find textual columns that potentially contain usernames (including columns that potentially contain e-mail addresses) that do not have a unique constraint or a unique index that involves only this column.

Do not let the system to do extra work. Checking a constraint with an AFTER trigger means that the trigger procedure will be executed after the data modification and if the check fails, then the system has to do extra work to roll back the changes.

Find base table columns that use type name that is used in system catalog tables. It is not a problem if the column is meant for recording identifiers of database objects.

Do not let the system to do extra work. Logging changes with a BEFORE trigger means extra work for rolling back the changes in case the logged data modification fails.

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.

"Names in software are 90 percent of what make software readable. You need to take the time to choose them wisely and keep them relevant. Names are too important to treat carelessly. Names should not cause confusion." (Robert C. Martin, Clean Code) Names should not cause confusion. Find the distinct names (identifiers) of user-defined objects that are SQL keywords that are not completely unreserved in PostgreSQL, i.e., these either never cannot be used as regular identifiers or cannot be used in case of some type of database objects. In PostgreSQL "there are several different classes of tokens ranging from those that can never be used as an identifier to those that have absolutely no special status in the parser as compared to an ordinary identifier. " (PostgreSQL manual) Moreover, such identifiers are often too general, i.e., do not provide enough information about the named object.