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

Question

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.

Answer

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;
```

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

Relevant content