PostgreSQL Query Catalog

This query identifies complex derived tables (views and materialized views) that perform data integration operations. Specifically, it filters for views whose definition involves joining two or more distinct tables. This distinguishes non-trivial views—which encapsulate relationship logic and data aggregation—from simple projection views that merely mirror a single base table. The result highlights the core reporting and data integration layer of the schema.

Find regular expressions where a predefined character class is incorrectly specified, e.g. [digit] instead of [:digit:].

Fing regular expression patterns that use [^\S] instead of \s or [^\D] instead of \d or [^\W] instead of \w.

Find as to whether regular expressions use inconsistently references to character classes: [^\s], [^\d], [^\w], [^[:space:]], [^[:digit:]], [^[:word:]] vs [^\S], [^\D], [^\W].

Find as to whether PL/pgSQL routines and SQL routines that do not have SQL-standard body use different syntax for casting (cast function vs :: operator).

Find as to whether the subqueries of derived tables use both older join syntax (join condition in the where clause) and newer syntax (join condition in the from clause).

Find PL/pgSQL routines that use comparison operators =< or =>.

Find user-defined routines that possibly use incorrect name of a system-defined function (currenttimestamp (correct is current_timestamp), currentdate (correct is current_date), currenttime (correct is current_time), local_time (correct is localtime), local_timestamp (correct is localtimestamp),localdate (there is no such function),local_date (there is no such function), sessionuser (correct is session_user), ucase (correct is upper), lcase (correct is lower)). The problem can arise only if the routine uses dynamic SQL. In case of static SQL the DBMS checks the SQL statemen at the creation time and finds out that for instance, SELECT Count(*) AS cnt FROM Emp WHERE hiredate<=currentdate; is incorrect statement because currentdate is not a function name and there is no column currentdate in the table Emp.

Find the use of regular expressions where logical or is incorrectly specified, i.e., (| or |).

PostgreSQL regular expressions do not have character classes word and letter.

Find regular expression patterns that use (?i) modifier in any other place than at the beginning of the pattern or (?-i) in any place of the pattern. Such use of the modifiers is not supported by PostgreSQL.

Find user-defined routines that contain an invocation of a system-defined function without providing any argument. The query considers all aggregate functions as well as some popular scalar functions.

Find expressions that use LIKE (including ILIKE) predicate with a regular expression patterns. In a LIKE pattern one can use only _ and % metasymbols to construct a pattern.

Find regular expressions where the names of character classes are not completely in lowercase. For instance, incorrect is to write [[:UPPER:]] or [[:Upper:]] and correct is [[:upper:]].

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]]+$'.

Find user-defined routines that use != operator to test as to whether two values are not equal.

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.

Character classes are surrounded by two pairs of square brackets.

This query identifies derived tables (views) that utilize the explicit ANSI SQL-92 join syntax with the ON clause. It specifically targets views where join conditions are defined within the FROM clause but explicitly excludes those using the simplified USING syntax. This helps to identify derived tables that subquery could be simplified.

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