Query goal: | Find columns of derived tables that name has been given by the system. The creators of the table should specify the name themselves to avoid ugly names and nasty surprises. |
Notes about the query: | pg_get_viewdef function reconstructs the underlying SELECT command for a view or materialized view. Thus, this is a decompiled reconstruction, not the original text of the command. |
Query type: | Problem detection (Each row in the result could represent a flaw in the design) |
Query reliability: | High (Few or no false-positive results) |
Query license: | MIT License |
Fixing suggestion: | Give to the columns explicitly names in the table definition. |
Data source: | INFORMATION_SCHEMA+system catalog |
SQL query: | Click on query to copy it
WITH columns AS (SELECT nspname AS table_schema, relname AS table_name, CASE WHEN relkind='m' THEN 'MATERIALIZED VIEW' ELSE 'VIEW' END AS table_type, attname AS column_name, pg_get_viewdef(pg_class.oid) AS viewdef FROM pg_class INNER JOIN pg_namespace ON pg_class.relnamespace=pg_namespace.oid INNER JOIN pg_attribute ON pg_class.oid=pg_attribute.attrelid WHERE relkind IN ( 'm','v') AND attnum>=1 AND nspname NOT IN (SELECT schema_name FROM INFORMATION_SCHEMA.schemata WHERE schema_name<>'public' AND schema_owner='postgres' AND schema_name IS NOT NULL)) SELECT table_schema, table_name, table_type, column_name, viewdef FROM columns WHERE (lower(column_name) IN ('min','max','sum','avg', 'count', 'string_agg', 'array_agg', 'json_agg') AND viewdef~*'(min|max|sum|avg|count|string_agg|array_agg|json_agg)[(]') OR lower(column_name) IN ('?column?') ORDER BY table_schema, table_name, column_name; |
Collection name | Collection 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 . |
Category name | Category description |
---|---|
Comfortability of data management | Queries of this category provide information about the means that have been used to make the use or management of database more comfortable and thus, more efficient. |
Derived tables | Queries of this category provide information about the derived tables (views, materialized views), which are used to implement virtual data layer. |
Naming | Queries of this category provide information about the style of naming. |
Reference |
---|
https://www.sqlstyle.guide/ |