Goal Find as to different database objects use different means to concatenate text (format function, concat function, concat_ws function, || operator).
Notes The query returns rows only if there are at least two different means that are used.
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 Use the same mean in different objects.
Data Source INFORMATION_SCHEMA+system catalog
SQL Query
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:

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
Derived tablesQueries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer.
InconsistenciesQueries of this catergory provide information about inconsistencies of solving the same problem in different places.
User-defined routinesQueries of this category provide information about the user-defined routines