WITH derived_tables AS (SELECT n.nspname AS schema_name,
c.relname AS object_name,
CASE WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
END AS object_type,
pg_get_viewdef(c.oid, true) AS object_definition,
regexp_replace(pg_get_viewdef(c.oid, true),'[\r\n]',' ','g') AS object_def
FROM pg_namespace AS n INNER JOIN pg_authid AS a ON n.nspowner=a.oid
INNER JOIN pg_class AS c ON n.oid=c.relnamespace
WHERE (n.nspname='public' OR a.rolname<>'postgres')
AND relkind IN ('v','m')),
routines AS (SELECT
pg_namespace.nspname AS schema_name,
pg_proc.proname || '(' || pg_get_function_identity_arguments(pg_proc.oid) || ')' AS object_name,
CASE WHEN pg_proc.prokind='f' THEN 'FUNCTION'
WHEN pg_proc.prokind='p' THEN 'PROCEDURE'
WHEN pg_proc.prokind='w' THEN 'WINDOW FUNCTION' END AS object_type,
pg_get_functiondef(pg_proc.oid) AS object_definition,
regexp_replace( regexp_replace(pg_get_functiondef(pg_proc.oid),'<','<','g'),'[\r\n]',' ','g') AS object_def
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)),
checked_elements AS (SELECT schema_name, object_name, object_type, object_definition, object_def
FROM derived_tables
UNION SELECT schema_name, object_name, object_type, object_definition, object_def
FROM routines),
stats AS (SELECT Count(*) FILTER (WHERE object_definition ~*'[[:space:]](concat|concat_ws)[(]') AS nr_of_concat_function,
Count(*) FILTER (WHERE object_definition ~*'[[:space:]]format[(]') AS nr_of_format_function,
Count(*) FILTER (WHERE object_definition ~*'[[:space:]]\|\|[[:space:]]') AS nr_of_concat_operator
FROM checked_elements
WHERE object_definition ~*'([[:space:]](concat|concat_ws|format)[(]|[[:space:]]\|\|[[:space:]])'),
problematic_elements AS (SELECT schema_name, object_name, object_type, object_definition, object_def
FROM checked_elements
WHERE object_definition ~*'([[:space:]](concat|concat_ws|format)[(]|[[:space:]]\|\|[[:space:]])')
SELECT schema_name, object_name, object_type, object_definition, object_def
FROM problematic_elements
WHERE 1<>(SELECT CASE WHEN nr_of_concat_function>0 THEN 1 ELSE 0 END
+CASE WHEN nr_of_format_function>0 THEN 1 ELSE 0 END
+CASE WHEN nr_of_concat_operator>0 THEN 1 ELSE 0 END
FROM Stats)
ORDER BY object_type, schema_name, object_name;
Collections
This query belongs to the following collections:
Name
Description
Find problems automatically
Queries, 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:
Name
Description
Derived tables
Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
Inconsistencies
Queries of this catergory provide information about inconsistencies of solving the same problem in different places.
User-defined routines
Queries of this category provide information about the user-defined routines