How to List Views (CREATE VIEW) Without Materialized Views in Amazon Redshift?

0

I'm working with Amazon Redshift, which is based on PostgreSQL 8.0.2, and I'm implementing a functionality to list all the views of a given schema while excluding materialized views created with "CREATE MATERIALIZED VIEW."

In PostgreSQL, it's relatively straightforward to differentiate between regular views and materialized views as they are stored with different values in the pg_catalog.pg_class table under the relkind column (i.e., 'v' for views and 'm' for materialized views).

However, in Amazon Redshift, both regular views and materialized views are stored with relkind as 'v'. To differentiate, Redshift provides a system table called SVV_MV_INFO that exclusively lists materialized views, but there doesn't appear to be an equivalent table to list regular views.

I attempted to retrieve all views from the pg_class table and then filter out materialized views by joining it with SVV_MV_INFO, but I encountered an error:

Query 1: To fetch all views from pg_catalog

SELECT c.oid, c.relname AS name
FROM pg_class c
WHERE c.relkind = 'v'::char AND c.relnamespace = [schema oid, eg:101]::oid
ORDER BY c.relname;

Query 2: To fetch just materialized views

SELECT name
FROM svv_mv_info
WHERE schema_name = '[schema name, eg:abc]';

Query 3: After joining both Query 1 and Query 2

SELECT c.oid, c.relname AS name
FROM pg_class c
WHERE c.relkind = 'v'::char AND c.relnamespace = 101::oid
      AND NOT EXISTS (
          SELECT 1
          FROM svv_mv_info s
          WHERE s.schema_name = 'abc' AND c.relname = s.name
      )
ORDER BY c.relname;

The error I encountered is (for query 3):

[Amazon](500310) Invalid operation: Specified types or functions (one per INFO message) not supported on Redshift tables.

Is there a method to fetch just regular views (created with "CREATE VIEW") from Amazon Redshift without encountering this error?

Any insights or suggestions on how to achieve this would be greatly appreciated. Thank you.

gefragt vor 7 Monaten675 Aufrufe
1 Antwort
1

Following query will give you list of all views excluding materialized views.

select table_schema as schema_name,
       table_name as view_name,
       view_definition
from information_schema.views
where table_schema in ('public')
and view_definition not like '%MATERIALIZED VIEW%'
order by schema_name,
         view_name;
AWS
EXPERTE
Nita_S
beantwortet vor 7 Monaten
  • Kindly use ilike instead of like and this query should work fine

Du bist nicht angemeldet. Anmelden um eine Antwort zu veröffentlichen.

Eine gute Antwort beantwortet die Frage klar, gibt konstruktives Feedback und fördert die berufliche Weiterentwicklung des Fragenstellers.

Richtlinien für die Beantwortung von Fragen