PostgreSQL Query Catalog

You should not give wrong information to the database management system.

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.

You should follow the principle of least privilege and thus not have in your database tables that usage privileges are granted to the pseudo-role PUBLIC, i.e., to all the database users now and in the future.

You must give rights to use routines to the users/roles that correspond to applications.

You must give privileges to use views to the users/roles that correspond to applications

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

You cannot use a START TRANSACTION or a SAVEPOINT statement in a procedure. Procedures appeared in PostgreSQL 11.

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;

Write expressions correctly. Find code fragments that have unbalanced brackets, i.e., the number of opening brackets is not the same as the number of closing brackets.

Write correct regular expressions. For instance, if there is a rule that code must consist of one or more digits, then correct expression is code~'^[0-9]+$', not code~'^[[0-9]]+$'.

Write correct constraints. Usually the constraint expression should refer to at least one column. A domain constraint expression should refer to the stub VALUE. For instance, the constraint CHECK(1=0) that is associated with a table T would prevent adding any rows to T. The value of the Boolean expression of this constraint is always FALSE.

Write correct code "The return value of a row-level trigger fired AFTER or a statement-level trigger fired BEFORE or AFTER is always ignored; it might as well be null." (PostgreSQL documentation)

Write correct code. If you pass arguments to a trigger function, then the function should use the arguments. TG_ARGV[]: "Data type array of text; the arguments from the CREATE TRIGGER statement. The index counts from 0. Invalid indexes (less than 0 or greater than or equal to tg_nargs) result in a null value." (PostgreSQL documentation)

Write code that is simple to understand and not confusing. A double negative is a grammatical construction occurring when two forms of negation are used in the same expression (https://en.wikipedia.org/wiki/Double_negative). Double negatives in Boolean expressions make it more difficult to understand and maintain the code.

Write code that is easy to understand and not unnecessarily long. A routine cannot have two or more parameters with the same name. In this case using longer identifier in the form routine_name.parameter name is unnecessary.

Working with sets of rows rather than processing each row separately is more effective.

What is the percentage of optional columns (that permit NULLs) in case of each base table? It is better to prohibit the use of NULLs in as many columns as possible. Otherwise the results of queries may be misleading.

What are the pairs of base tables that have at least two columns with the same names and data types. The tables might violate the principle of orthogonal design and hence might facilitate uncontrolled data redundancy over different tables.

What are the base tables and foreign tables without any associated (directly or through domains) check constraints? A NOT NULL constraint is a kind of CHECK constraint. However, this query does not take into account NOT NULL constraints.

Using PL/pgSQL may cause context switching between declarative SQL and procedural PL/pgSQL. Thus use PL/pgSQL only if you truly need some of its constructs. You can create table functions by using SQL.