| Query 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 about the query: | 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; |
| Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
| Query reliability: | Low (Many false-positive results) |
| Query 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" |
| Data source: | system catalog only |
| SQL query: | Click on query to copy it
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]','<br>','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 ~*'(?<!([[:space:]]Trim[(].*))(concat(?!_ws)|format|\|\|)' ORDER BY table_schema, table_name; |
| Category name | Category description |
|---|---|
| Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |