PostgreSQL Query Catalog

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.

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.

Find routines that body does not contain any action.

Find user-defined routines with the same name and parameters (including the order of parameters) in different schemas.

Make sure that your parameters have appropriate types in order to avoid unnecessary type casting.

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)

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)

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.

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

Find row level BEFORE UPDATE triggers that do not return the new row version. Exclude triggers that raise WARNING/EXCEPTION.

Find rule names that are used in a database in more than one schema. Different things should have different names. But here different rules have the same name. Also make sure that this is not a duplication.

Find names of rules that are used within the same schema more than once. Give different triggers different names.

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;

You cannot use COMMIT and ROLLBACK in a SECURITY DEFINER procedure. Procedures appeared in PostgreSQL 11.

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.

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.

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.

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.

Find foreign key constraints that use a SET NULL compensating action but a foreign key column is mandatory, i.e., does not permit NULLs. Compensatory actions cannot make changes that violate integrity constraints in a database. SET NULL cannot put NULL to a mandatory column (delete a foreign key value).