Redshift Observatory

System Table Tracker

System view pg_catalog.svv_all_schemas version 1.0.50300 / 2023-05-09

View Text

(SELECT CAST(btrim(CAST(current_database() AS text)) AS varchar(128)) AS database_name,
        CAST(btrim(CAST(pgn.nspname AS text)) AS varchar(128)) AS schema_name,
        pgn.nspowner AS schema_owner,
        CAST(CASE
               WHEN ses.eskind IS NULL
                 THEN CAST('local' AS text)
               ELSE CAST('external' AS text)
             END AS varchar(128)) AS schema_type,
        CAST(array_to_string(pgn.nspacl,
                             CAST('~' AS text)) AS varchar(128)) AS schema_acl,
        CAST(btrim(ses.databasename) AS varchar(128)) AS source_database,
        CAST(btrim(ses.esoptions) AS varchar(128)) AS schema_option
 FROM pg_namespace AS pgn
      LEFT JOIN svv_external_schemas AS ses ON pgn.nspname = ses.schemaname
 WHERE has_schema_privilege(CAST("current_user"() AS name),
                            CAST(pgn.nspname AS text),
                            CAST('USAGE' AS text))
   AND (pgn.nspname <> CAST('catalog_history' AS name)
    AND pgn.nspname <> CAST('pg_toast' AS name)
    AND pgn.nspname <> CAST('pg_internal' AS name))
   AND pgn.nspname !~~ CAST('pg_temp%' AS text)

 UNION ALL

 SELECT CAST(btrim(CAST(rs_schemas.database_name AS text)) AS varchar(128)) AS database_name,
        CAST(btrim(CAST(rs_schemas.schema_name AS text)) AS varchar(128)) AS schema_name,
        rs_schemas.schema_owner,
        CAST(rs_schemas.schema_type AS varchar(128)) AS schema_type,
        CAST(btrim(CAST(rs_schemas.schema_acl AS text)) AS varchar(128)) AS schema_acl,
        '' AS source_database,
        CAST(btrim(CAST(rs_schemas.schema_option AS text)) AS varchar(128)) AS schema_option
 FROM pg_get_shared_redshift_schemas() AS rs_schemas(database_name varchar,
                                                     schema_name varchar,
                                                     schema_owner integer,
                                                     schema_type varchar,
                                                     schema_acl varchar,
                                                     schema_option varchar))

UNION ALL

SELECT CAST(btrim(CAST(rs_schemas.database_name AS text)) AS varchar(128)) AS database_name,
       CAST(btrim(CAST(rs_schemas.schema_name AS text)) AS varchar(128)) AS schema_name,
       rs_schemas.schema_owner,
       CAST(CAST('external' AS varchar) AS varchar(128)) AS schema_type,
       CAST(btrim(CAST(rs_schemas.schema_acl AS text)) AS varchar(128)) AS schema_acl,
       CAST(btrim(CAST(rs_schemas.source_database AS text)) AS varchar(128)) AS source_database,
       CAST(btrim(CAST(rs_schemas.schema_option AS text)) AS varchar(128)) AS schema_option
FROM pg_get_all_external_schemas() AS rs_schemas(database_name varchar,
                                                 schema_name varchar,
                                                 schema_owner integer,
                                                 schema_type varchar,
                                                 schema_acl varchar,
                                                 source_database varchar,
                                                 schema_option varchar)

Home 3D Друк Blog Bring-Up Times Consultancy Cross-Region Benchmarks Email Forums IRC Mailing Lists Reddit Redshift Price Tracker Redshift Version Tracker Redshift Workbench System Table Tracker The Known Universe Twitter White Papers