Goal Find occurrences of possibly pointless regular expressions - i.e., value contains zero or more symbols.
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Medium (Medium number of false-positive results)
License MIT License
Fixing Suggestion Simplify database and remove fragments of code that do not provide value.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
WITH expressions AS (select 
n.nspname as schema,
c.relname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'TABLE CHECK' AS type
from pg_constraint o inner join pg_class c on c.oid = o.conrelid
inner join pg_namespace n on n.oid=c.relnamespace
where o.contype ='c'   
and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
union select n.nspname as schema,
t.typname || '.' || o.conname as name, 
substring(pg_get_constraintdef(o.oid),7) as expression,
'DOMAIN CHECK' AS type
from pg_constraint o inner join pg_type t on t.oid = o.contypid
inner join pg_namespace n on n.oid=t.typnamespace
where o.contype ='c' and n.nspname not in (select schema_name
from information_schema.schemata
where schema_name<>'public' and
schema_owner='postgres' and schema_name is not null)
UNION SELECT trigger_schema, event_object_table || '.' || trigger_name, action_condition, 'TRIGGER WHEN' AS type
FROM INFORMATION_SCHEMA.triggers
WHERE action_condition IS NOT NULL
UNION SELECT schemaname, tablename || '.' || rulename, definition, 'RULE WHERE' AS type
FROM pg_catalog.pg_rules
WHERE schemaname NOT IN (SELECT schema_name
FROM INFORMATION_SCHEMA.schemata
WHERE schema_name<>'public' AND
schema_owner='postgres' AND schema_name IS NOT NULL)
UNION SELECT table_schema, table_name, view_definition, type
FROM (SELECT  table_schema, table_name, regexp_replace(view_definition,'[\r\n]','
','g') AS view_definition, 'VIEW SUBQUERY' AS type FROM information_schema.views WHERE table_schema NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) UNION SELECT schemaname, matviewname, regexp_replace(definition,'[\r\n]','
','g') AS definition, 'MATERIALIZED VIEW SUBQUERY' AS type FROM pg_catalog.pg_matviews) AS foo WHERE view_definition ~*'^.+([[:space:]]+where[[:space:]]+|[[:space:]]+having[[:space:]]+).+$' UNION SELECT pg_namespace.nspname AS routine_schema, pg_proc.proname || '(' || pg_get_function_identity_arguments(pg_proc.oid) || ')' AS routine_name, regexp_replace(pg_get_functiondef(pg_proc.oid),'[\r\n]','
','g') AS routine_src, 'ROUTINE' AS type FROM pg_catalog.pg_proc, pg_catalog.pg_namespace WHERE pg_proc.pronamespace = pg_namespace.oid AND pg_proc.prokind<>'a' AND pg_proc.proname NOT IN ('f_assume_you_must_use_files', 'f_check_format_comma_separated_list', 'f_check_password', 'f_default_value_with_no_match') AND pg_namespace.nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL) AND NOT EXISTS (SELECT 1 FROM pg_catalog.pg_depend d WHERE EXISTS (SELECT 1 FROM pg_catalog.pg_extension e WHERE d.refobjid=e.oid) AND d.objid=pg_proc.oid)) SELECT schema, name AS object_identifier, type AS object_type, expression AS suspected_expression FROM expressions WHERE expression ~ '[[:space:]]~\*{0,1}[[:space:]]*''\^\.\*\$''' ORDER BY schema, type, name;
Collections

This query belongs to the following collections:

NameDescription
Find problems automaticallyQueries, that results point to problems in the database. Each query in the collection produces an initial assessment. However, a human reviewer has the final say as to whether there is a problem or not .
Categories

This query is classified under the following categories:

NameDescription
Regular expressionsQueries of this catergory provide information about the use of regular expressions.