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]','<br>','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),'<','&lt','g'),'[\r\n]','<br>','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)
SELECT schema_name, object_name, object_type, object_def
FROM checked_elements
WHERE object_definition  ~*'[[:space:]](concat|concat_ws)[[:space:]]*[(].*[[:space:]]coalesce[[:space:]]*[(]'
ORDER BY schema_name, object_type, object_name;