Materialized Views not filtered

[Duccio Fanetti]

In the file Providers/GenericRdbms/Src/PostGis/SchemaMgr/Ph/Rd/DbObjectReader.cpp, the function MakeQueryReader does not filter the materialized views present in the database. When trying to search for a view by providing the schema name and table name, the result obtained is a set consisting of the desired view and all the materialized views present in the database (which are not filtered).

The proposed solution would be to extend the input filter to also include the part of the query after UNION that searches for materialized views.

[jng]

Is your issue that materialized views from *other* schemas are showing up under a particular schema?

[Duccio Fanetti]

Hi jng, the problem is that all materialized views present in all schemas are returned. In fact, the filter is performed only in the first part of the query and not in the second after the union. To give you an example, the query that is performed on the my database searching for a view 'view_particelle' on schema 'catasto':

SELECT  t.table_schema  || '.' || t.table_name AS name,  lower(t.table_type) AS type,  convert_to(t.table_schema,'UTF8') as collate_schema_name,  convert_to(t.table_name,'UTF8') as collate_table_name  
FROM information_schema.tables AS t
WHERE t.table_schema not in ( 'information_schema' ,'pg_catalog')  and  ( (t.table_schema = 'catasto' and t.table_name = 'view_particelle') ) AND t.table_type IN ('BASE TABLE','VIEW')  AND t.table_name not in ( 'geometry_columns', 'geography_columns','spatial_ref_sys', 'raster_columns', 'raster_overviews')
UNION  
SELECT ns.nspname || '.' || c.relname AS name,  'view' AS type,  convert_to(ns.nspname,'UTF8') AS collate_schema_name,  convert_to(c.relname,'UTF8') AS collate_table_name  
FROM pg_class AS c  JOIN pg_namespace ns ON c.relnamespace = ns.oid  
WHERE c.relkind = 'm'  ORDER BY collate_schema_name, collate_table_name ASC;

The result of the query is a set of 5 rows: one is the the view that I requested, the other four are all the materialized view that are in the database (4 materialized views in my case, even in other schemas).

Thank you.