Redshift Observatory

System Table Tracker

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

View Text

SELECT stl_datashare_changes_producer.userid,
       CAST(btrim(CAST(stl_datashare_changes_producer.username AS text)) AS varchar(128)) AS username,
       stl_datashare_changes_producer.pid,
       stl_datashare_changes_producer.xid,
       stl_datashare_changes_producer.shareid AS share_id,
       CAST(btrim(CAST(stl_datashare_changes_producer.sharename AS text)) AS varchar(128)) AS share_name,
       stl_datashare_changes_producer.dbid AS source_database_id,
       CAST(btrim(CAST(stl_datashare_changes_producer.dbname AS text)) AS varchar(128)) AS source_database_name,
       CAST(NULL AS unknown) AS consumer_database_id,
       CAST(NULL AS unknown) AS consumer_database_name,
       CAST(NULL AS unknown) AS arn,
       stl_datashare_changes_producer.actiontime AS recordtime,
       CAST(CASE
              WHEN stl_datashare_changes_producer.action = 0
                THEN CAST('CREATE DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 1
                THEN CAST('DROP DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 2
                THEN CAST('GRANT ALTER ON DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 3
                THEN CAST('GRANT SHARE ON DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 4
                THEN CAST('REVOKE ALTER FROM DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 5
                THEN CAST('REVOKE SHARE FROM DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 6
                THEN CAST('ALTER DATASHARE ADD' AS text)
              WHEN stl_datashare_changes_producer.action = 7
                THEN CAST('ALTER DATASHARE REMOVE' AS text)
              WHEN stl_datashare_changes_producer.action = 8
                THEN CAST('ALTER DATASHARE SET' AS text)
              WHEN stl_datashare_changes_producer.action = 9
                THEN CAST('ALTER DATASHARE OWNER' AS text)
              WHEN stl_datashare_changes_producer.action = 10
                THEN CAST('GRANT USAGE ON DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 11
                THEN CAST('REVOKE USAGE FROM DATASHARE' AS text)
              WHEN stl_datashare_changes_producer.action = 12
                THEN CAST('AUTO ADD TABLE' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(128)) AS action,
       stl_datashare_changes_producer.status,
       CAST(CASE
              WHEN stl_datashare_changes_producer.datashareobjtype = 0
                THEN CAST('SCHEMA' AS text)
              WHEN stl_datashare_changes_producer.datashareobjtype = 1
                THEN CAST('TABLE' AS text)
              WHEN stl_datashare_changes_producer.datashareobjtype = 2
                THEN CAST('FUNCTION' AS text)
              WHEN stl_datashare_changes_producer.datashareobjtype = 3
                THEN CAST('VIEW' AS text)
              WHEN stl_datashare_changes_producer.datashareobjtype = 4
                THEN CAST('LATE BINDING VIEW' AS text)
              WHEN stl_datashare_changes_producer.datashareobjtype = 5
                THEN CAST('MATERIALIZED VIEW' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(64)) AS share_object_type,
       CASE
         WHEN stl_datashare_changes_producer.datashareobjtype >= 0
           THEN stl_datashare_changes_producer.datashareobjid
         ELSE CAST(NULL AS integer)
       END AS share_object_id,
       CAST(CASE
              WHEN stl_datashare_changes_producer.datashareobjtype >= 0
                THEN btrim(CAST(stl_datashare_changes_producer.datashareobjname AS text))
              ELSE CAST(NULL AS text)
            END AS varchar(128)) AS share_object_name,
       CAST(CASE
              WHEN stl_datashare_changes_producer.targetroletype = 0
                THEN CAST('USER' AS text)
              WHEN stl_datashare_changes_producer.targetroletype = 1
                THEN CAST('GROUP' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(16)) AS target_user_type,
       CASE
         WHEN stl_datashare_changes_producer.targetroletype >= 0
           THEN stl_datashare_changes_producer.targetroleid
         ELSE CAST(NULL AS integer)
       END AS target_userid,
       CAST(CASE
              WHEN stl_datashare_changes_producer.targetroletype >= 0
                THEN btrim(CAST(stl_datashare_changes_producer.targetrolename AS text))
              ELSE CAST(NULL AS text)
            END AS varchar(128)) AS target_username,
       CAST(btrim(CAST(stl_datashare_changes_producer.consumeraccount AS text)) AS varchar(16)) AS consumer_account,
       CAST(btrim(CAST(stl_datashare_changes_producer.consumernamespace AS text)) AS varchar(64)) AS consumer_namespace,
       CAST(NULL AS unknown) AS producer_account,
       CAST(NULL AS unknown) AS producer_namespace,
       CAST(CASE
              WHEN stl_datashare_changes_producer.shareproperty = CAST('public accessible' AS bpchar)
                THEN CAST('DATASHARE_PUBLICACCESSIBLE' AS text)
              WHEN stl_datashare_changes_producer.shareproperty = CAST('datashare owner' AS bpchar)
                THEN CAST('DATASHARE_OWNER' AS text)
              WHEN stl_datashare_changes_producer.shareproperty = CAST('include new tables' AS bpchar)
                THEN CAST('INCLUDE_NEW' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(64)) AS attribute_name,
       CAST(btrim(CAST(stl_datashare_changes_producer.sharepropvalue AS text)) AS varchar(128)) AS attribute_value,
       CAST(btrim(CAST(stl_datashare_changes_producer.message AS text)) AS varchar(512)) AS message
FROM stl_datashare_changes_producer

UNION ALL

SELECT stl_datashare_changes_consumer.userid,
       CAST(btrim(CAST(stl_datashare_changes_consumer.username AS text)) AS varchar(128)) AS username,
       stl_datashare_changes_consumer.pid,
       stl_datashare_changes_consumer.xid,
       CAST(NULL AS unknown) AS share_id,
       CAST(btrim(CAST(stl_datashare_changes_consumer.sharename AS text)) AS varchar(128)) AS share_name,
       CAST(NULL AS unknown) AS source_database_id,
       CAST(NULL AS unknown) AS source_database_name,
       stl_datashare_changes_consumer.sharedbid AS consumer_database_id,
       CAST(btrim(CAST(stl_datashare_changes_consumer.sharedbname AS text)) AS varchar(128)) AS consumer_database_name,
       stl_datashare_changes_consumer.arn,
       stl_datashare_changes_consumer.actiontime AS recordtime,
       CAST(CASE
              WHEN stl_datashare_changes_consumer.action = 1
                THEN CAST('CREATE DATABASE FROM DATASHARE' AS text)
              WHEN stl_datashare_changes_consumer.action = 2
                THEN CAST('GRANT USAGE ON DATABASE' AS text)
              WHEN stl_datashare_changes_consumer.action = 3
                THEN CAST('REVOKE USAGE FROM DATABASE' AS text)
              WHEN stl_datashare_changes_consumer.action = 4
                THEN CAST('ALTER DATABASE' AS text)
              WHEN stl_datashare_changes_consumer.action = 5
                THEN CAST('DROP DATABASE' AS text)
              WHEN stl_datashare_changes_consumer.action = 6
                THEN CAST('CREATE DATABASE FROM ARN' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(128)) AS action,
       stl_datashare_changes_consumer.status,
       CAST(NULL AS unknown) AS share_object_type,
       CAST(NULL AS unknown) AS share_object_id,
       CAST(NULL AS unknown) AS share_object_name,
       CAST(CASE
              WHEN stl_datashare_changes_consumer.targettype = 1
                THEN CAST('USER' AS text)
              WHEN stl_datashare_changes_consumer.targettype = 2
                THEN CAST('GROUP' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(16)) AS target_user_type,
       CASE
         WHEN stl_datashare_changes_consumer.targettype = 1
           OR stl_datashare_changes_consumer.targettype = 2
           THEN stl_datashare_changes_consumer.targetid
         ELSE CAST(NULL AS integer)
       END AS target_userid,
       CAST(CASE
              WHEN stl_datashare_changes_consumer.targettype = 1
                OR stl_datashare_changes_consumer.targettype = 2
                THEN btrim(CAST(stl_datashare_changes_consumer.targetname AS text))
              ELSE CAST(NULL AS text)
            END AS varchar(128)) AS target_username,
       CAST(NULL AS unknown) AS consumer_account,
       CAST(NULL AS unknown) AS consumer_namespace,
       CAST(btrim(CAST(stl_datashare_changes_consumer.produceraccount AS text)) AS varchar(16)) AS producer_account,
       CAST(btrim(CAST(stl_datashare_changes_consumer.producernamespace AS text)) AS varchar(64)) AS producer_namespace,
       CAST(CASE
              WHEN stl_datashare_changes_consumer.targettype = 3
               AND stl_datashare_changes_consumer.action = 4
                THEN CAST('DATABASE_NAME' AS text)
              WHEN stl_datashare_changes_consumer.targettype = 1
               AND stl_datashare_changes_consumer.action = 4
                THEN CAST('DATABASE_OWNER' AS text)
              ELSE CAST(NULL AS text)
            END AS varchar(64)) AS attribute_name,
       CAST(CASE
              WHEN stl_datashare_changes_consumer.targettype = 3
               AND stl_datashare_changes_consumer.action = 4
                THEN btrim(CAST(stl_datashare_changes_consumer.targetname AS text))
              WHEN stl_datashare_changes_consumer.targettype = 1
               AND stl_datashare_changes_consumer.action = 4
                THEN btrim(CAST(CAST(stl_datashare_changes_consumer.targetid AS varchar(128)) AS text))
              ELSE CAST(NULL AS text)
            END AS varchar(128)) AS attribute_value,
       CAST(btrim(CAST(stl_datashare_changes_consumer.message AS text)) AS varchar(512)) AS message
FROM stl_datashare_changes_consumer

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