Perhaps Trim is missing

Back to Catalog
Goal Find derived tables (views and materialized views) that apparently concatenate values (by using || operator or use Concat function or use Format function) by putting a space between these but do not use Trim function to get rid of the extra space at the beginning or the end of the string.
Notes In PostgreSQL NULL || 'text' => NULL. To replace NULL with an empty string one can use Coalesce function. Thus Coalesce(NULL,'') || 'text' => 'text'. Coalesce(NULL,'') || ' ' || 'text' => ' text'. We can use Trim function to get rid of the space at the beginning of the concatenation result like that: Trim(Coalesce(NULL,'') || ' ' || 'text'))=> 'text'. Similarly, Concat (NULL, ' ', 'text') )=>' text' and Trim should be used to get rid of the space at the beginning of the result.

Instead of SELECT Concat(surname, ' ', given_name) AS person_name FROM Person; write SELECT Trim(Concat(surname, ' ', given_name)) AS person_name FROM Person;
Type Problem detection (Each row in the result could represent a flaw in the design)
Reliability Low (Many false-positive results)
License MIT License
Fixing Suggestion Change the subquery of the derived table. Use Trim function or use Concat_ws function to concatenate the values.

concat(NULL, ' ', 'a')=> " a"

concat_ws(' ', NULL, 'a')=> "a"
Data Source system catalog only
SQL Query 
WITH derived_tables AS (SELECT n.nspname AS table_schema, 
c.relname AS table_name,
CASE WHEN c.relkind='v' THEN 'VIEW'
WHEN c.relkind='m' THEN 'MATERIALIZED VIEW'
END AS table_type,
pg_get_viewdef(c.oid, true) AS table_definition,
regexp_replace(pg_get_viewdef(c.oid, true),'[\r\n]','
','g') AS table_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'))
SELECT table_schema, table_name, table_type, table_def
FROM derived_tables
WHERE table_definition  ~*'(?
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.