[reporting] Create Columns from DB process works with materialized views
authorAugusto Mauch <augusto.mauch@openbravo.com>
Mon, 18 Nov 2019 16:06:24 +0100
changeset 36835 202a20ec999a
parent 36834 6d86f00eb0b7
child 36836 6e96d33a5642
[reporting] Create Columns from DB process works with materialized views

Now it is possible to use the "Create Columns from DB" to import the columns of a materialized view
src-db/database/model/prescript-PostgreSql.sql
--- a/src-db/database/model/prescript-PostgreSql.sql	Mon Nov 18 16:04:04 2019 +0100
+++ b/src-db/database/model/prescript-PostgreSql.sql	Mon Nov 18 16:06:24 2019 +0100
@@ -1141,28 +1141,28 @@
 /-- END
 
 CREATE OR REPLACE VIEW user_tab_columns AS
- SELECT upper(pg_class.relname::text) AS table_name, upper(pg_attribute.attname::text) AS column_name, 
-        (CASE WHEN upper(pg_type.typname::text)='NUMERIC' 
+ SELECT upper(c.relname::text) AS table_name, upper(a.attname::text) AS column_name, 
+        (CASE WHEN upper(t.typname::text)='NUMERIC' 
              THEN 'NUMBER'
-             ELSE upper(pg_type.typname::text)
+             ELSE upper(t.typname::text)
           END) AS data_type,
-        CASE pg_type.typname
-            WHEN 'varchar'::name THEN pg_attribute.atttypmod - 4
-            WHEN 'bpchar'::name THEN pg_attribute.atttypmod - 4
+        CASE t.typname
+            WHEN 'varchar'::name THEN a.atttypmod - 4
+            WHEN 'bpchar'::name THEN a.atttypmod - 4
             ELSE NULL::integer
         END AS char_col_decl_length,
 
-        CASE pg_type.typname
+        CASE t.typname
             WHEN 'bytea'::name THEN 4000
             WHEN 'text'::name THEN 4000
             WHEN 'oid'::name THEN 4000
-            ELSE CASE PG_ATTRIBUTE.ATTLEN 
-                     WHEN -1 THEN PG_ATTRIBUTE.ATTTYPMOD-4 
-                     ELSE PG_ATTRIBUTE.ATTLEN 
+            ELSE CASE a.ATTLEN 
+                     WHEN -1 THEN a.ATTTYPMOD-4 
+                     ELSE a.ATTLEN 
                  END
         END AS data_length,
 
-        CASE pg_type.typname
+        CASE t.typname
             WHEN 'bytea'::name THEN 4000
             WHEN 'text'::name THEN 4000
             WHEN 'oid'::name THEN 4000
@@ -1173,18 +1173,20 @@
                 END
         END AS data_precision,
         CASE 
-            WHEN upper(pg_type.typname) = 'NUMERIC' and cols.numeric_scale is not null THEN cols.numeric_scale
+            WHEN upper(t.typname) = 'NUMERIC' and cols.numeric_scale is not null THEN cols.numeric_scale
             ELSE 0
         END AS data_scale,
-        CASE pg_attribute.atthasdef
+        CASE a.atthasdef
             WHEN true THEN ( SELECT pg_get_expr(adbin, adrelid)
                FROM pg_attrdef
-              WHERE pg_attrdef.adrelid = pg_class.oid AND pg_attrdef.adnum = pg_attribute.attnum)
+              WHERE pg_attrdef.adrelid = c.oid AND pg_attrdef.adnum = a.attnum)
             ELSE NULL::text
-        END AS data_default, not pg_attribute.attnotnull AS nullable, pg_attribute.attnum AS column_id
-   FROM pg_class, pg_namespace, pg_attribute, pg_type, information_schema.columns cols
-  WHERE pg_attribute.attrelid = pg_class.oid AND pg_attribute.atttypid = pg_type.oid AND pg_class.relnamespace = pg_namespace.oid AND pg_namespace.nspname = current_schema() AND pg_attribute.attnum > 0 
-  AND upper(cols.table_name)=upper(pg_class.relname) AND upper(cols.column_name)=upper(pg_attribute.attname) AND cols.table_schema = current_schema()
+        END AS data_default, not a.attnotnull AS nullable, a.attnum AS column_id, a.*
+   FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid AND n.nspname = current_schema()
+   JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0
+   JOIN pg_type t ON t.oid = a.atttypid 
+   LEFT JOIN information_schema.columns cols on upper(cols.table_name)=upper(c.relname) AND upper(cols.column_name)=upper(a.attname)
+   WHERE c.relkind in ('r', 'v', 'm')
 /-- END
 
 SELECT * FROM drop_view('v$version')