Redshift Research Project

System Table Tracker

System view pg_catalog.svv_datashares version 1.0.39009 / 2022-06-08

View Text

SELECT CAST(ds.sharename AS varchar(128)) AS share_name,
       CAST(ds.oid AS integer) AS share_id,
       ds.shareowner AS share_owner,
       CAST(pd.datname AS varchar(128)) AS source_database,
       CAST(NULL AS unknown) AS consumer_database,
       'OUTBOUND' AS share_type,
       ds.createdate,
       ds.publicaccess AS is_publicaccessible,
       CAST(array_to_string(ds.shareacl,
                            CAST('~' AS text)) AS varchar(256)) AS share_acl,
       current_aws_account() AS producer_account,
       current_namespace() AS producer_namespace,
       CAST(CASE
              WHEN ds.flag & CAST(1 AS bigint)
                THEN CAST('ADX' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar) AS managed_by
FROM pg_datashare AS ds
     INNER JOIN pg_database AS pd ON ds.sharedb = pd.oid
WHERE has_datashare_privilege(CAST("current_user"() AS name),
                              CAST(ds.sharename AS text))

UNION ALL

SELECT inbound_shares.share_name,
       CAST(NULL AS unknown) AS share_id,
       CAST(NULL AS unknown) AS share_owner,
       CAST(NULL AS unknown) AS source_database,
       CAST(pgd.datname AS varchar(128)) AS consumer_database,
       'INBOUND' AS share_type,
       CAST(NULL AS unknown) AS createdate,
       inbound_shares.is_publicaccessible,
       CAST(NULL AS unknown) AS share_acl,
       inbound_shares.producer_account,
       inbound_shares.producer_namespace,
       inbound_shares.managed_by
FROM (SELECT CAST(btrim(CAST(tables.share_name AS text)) AS varchar(128)) AS share_name,
             CAST(btrim(CAST(tables.share_id AS text)) AS varchar(256)) AS share_id,
             CAST(btrim(CAST(tables.producer_account AS text)) AS varchar(16)) AS producer_account,
             CAST(btrim(CAST(tables.producer_namespace AS text)) AS varchar(64)) AS producer_namespace,
             tables.is_publicaccessible,
             CAST(btrim(CAST(tables.managed_by AS text)) AS varchar(16)) AS managed_by
      FROM pg_get_inbound_datashares() AS tables(share_name varchar,
                                                 share_id varchar,
                                                 producer_account varchar,
                                                 producer_namespace varchar,
                                                 is_publicaccessible boolean,
                                                 managed_by varchar)) AS inbound_shares
     LEFT JOIN pg_external_database AS ped ON ped.shareid = CAST(inbound_shares.share_id AS text)
     LEFT JOIN pg_database AS pgd ON pgd.oid = ped.dbid

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