Redshift Observatory

System Table Tracker

System view pg_catalog.svv_redshift_schemas version 1.0.52931 / 2023-07-07

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(CAST('local' AS varchar) AS varchar(128)) AS schema_type,
       CAST(array_to_string(pgn.nspacl,
                            CAST('~' AS text)) AS varchar(128)) AS schema_acl,
       '' AS schema_option
FROM pg_namespace AS pgn
     LEFT JOIN svv_external_schemas AS ses ON pgn.nspname = ses.schemaname
WHERE ses.eskind IS NULL
  AND 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(btrim(CAST(rs_schemas.schema_type AS text)) 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.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,
                                                    schema_id integer)

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