PostgreSQL Query Catalog

This query audits user-defined routine invocations to enforce named parameter notation, specifically targeting calls to routines with multiple input parameters. It identifies invocations using positional notation (e.g., func(a, b)), which couples the calling logic to the specific order of the callee's signature. Crucially, the query excludes invocations of routines that accept a single input parameter. In single-arity contexts, positional notation is structurally unambiguous and widely accepted. The focus is strictly on multi-parameter calls where positional notation increases the risk of argument misalignment during refactoring.

Do not create unnecessary sequence generators.

Find columns of base tables that name refers to the possibility that it contains unique values but the column does not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint.

All the keys must be enforced. Find base tables that implement M:N relationship types and that allow multiple relationships of the same type between the same entities.

Find columns of base tables that contain automatically generated unique values but do not belong to any PRIMARY KEY/UNIQUE constraint. If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint.

If something has to be unique, then it must be said to the system so that it could use the information for internal optimizations and enforce the constraint. Find columns that contain Universally Unique Identifiers but are not a part of any simple PRIMARY KEY/UNIQUE constraint and are also not part of a foreign key.

Find surrogate key columns that do not have an associated sequence generator. Surrogate key values must be generated by using the system (the sequence generator mechanism in case of PostgreSQL). If there are no sequence generators, then there is a question as to whether there are no surrogate keys in the database at all (could be possible and OK) or (more probable) developers have forgotten to implement the generation of surrogate keys.

Find simple natural key columns that are associated with a sequence generator.

Find sequence generators that are not associated with any column through the default value mechanism. Please note, that it is also possible to refer to a sequence generator from a routine or from an application. If these are indeed not used, then these should be dropped, otherwise these are dead code.

Find CHECK constraints that use precise comparison (= or <>) with a regular expression or LIKE pattern.

Find columns with a timestamp or a time type where the precision (the permitted maximum number of fractional sections) is bigger than the precision in the default value of the column.

Find regular expressions that do not have predefined character classes between double square brackets, e.g., [:digit:] instead of [[:digit:]].

Find routines with IF/ELSE or SWITCH/CASE statements. If your routine has a multipart IF/CASE statement, then perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles.

Find routines with multiple raise exception commands. Perhaps it has multiple tasks and it violates the separation of concerns and single responsibilities principles.

Find base tables that name starts with a prefix. Do not use prefixes in case of base table names. Derive the names from the names of entity types. Do not use "_", "t_", "tab_", "t11_" etc as prefixes of a table.

Find columns of base tables and foreign tables where one uses a check constraint to prevent values that consist of only spaces. Make sure that this is the correct constraint and there is no need to prevent values that consist of only whitespace characters.

In SQL tables each column has the ordinal position. Find all the base tables where the primary key columns are not the first in the table, i.e., there is at least one non-primary key column that comes before a primary key column. It is easier to grasp the primary key if its columns are the first in the table. It could be that a table inherits from an abstract table where no keys have been defined.

Find privileges on the database and its schemas, domains, types, languages, sequences, foreign data wrappers, and foreign servers that have been granted to a superuser. Superuser can do anything in the database and thus does not need the privileges. The result is a sign that perhaps the executed GRANT statements were incorrect (wrong username) or the grantee later got superuser status (that it shouldn't have).

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

Find publications that do not contain any table.