Redshift Observatory

System Table Tracker

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

View Text

SELECT rls_info_on_all_rels.datname,
       rls_info_on_all_rels.relschema,
       rls_info_on_all_rels.relname,
       rls_info_on_all_rels.relkind,
       rls_info_on_all_rels.is_rls_on,
       rls_info_on_all_rels.is_rls_datashare_on
FROM (SELECT pgc.oid AS reloid,
             CAST(current_database() AS varchar(128)) AS datname,
             CAST(pns.nspname AS varchar(128)) AS relschema,
             CAST(pgc.relname AS varchar(128)) AS relname,
             CAST(CASE
                    WHEN pgc.relkind = CAST('r' AS "char")
                      THEN CAST('table' AS text)
                    WHEN pgc.relkind = CAST('v' AS "char")
                      THEN CASE
                             WHEN mvinfo.value IS NOT NULL
                               THEN CAST('materialized view' AS text)
                             ELSE CAST('view' AS text)
                           END
                    ELSE CAST('unknown' AS text)
                  END AS varchar(17)) AS relkind,
             CAST(CASE
                    WHEN rinfo.colnum = 20
                     AND rinfo.value = CAST('1' AS text)
                      THEN CAST('t' AS text)
                    ELSE CAST('f' AS text)
                  END AS char) AS is_rls_on,
             CAST(CASE
                    WHEN rdsinfo.colnum = 25
                      THEN CASE
                             WHEN rdsinfo.value = CAST('0' AS text)
                               THEN CAST('f' AS text)
                             WHEN rdsinfo.value = CAST('1' AS text)
                               THEN CAST('t' AS text)
                             ELSE CAST('u' AS text)
                           END
                    ELSE CASE
                           WHEN CAST(CASE
                                       WHEN rinfo.colnum = 20
                                        AND rinfo.value = CAST('1' AS text)
                                         THEN CAST('t' AS text)
                                       ELSE CAST('f' AS text)
                                     END AS char) = CAST('t' AS bpchar)
                             THEN CAST('t' AS text)
                           ELSE CAST('f' AS text)
                         END
                  END AS char) AS is_rls_datashare_on
      FROM pg_namespace AS pns
           INNER JOIN pg_class AS pgc ON pgc.relnamespace = pns.oid
           LEFT JOIN (SELECT pgce.reloid,
                             pgce.colnum,
                             pgce.value
                      FROM pg_class_extended AS pgce
                      WHERE pgce.colnum = 20) AS rinfo ON rinfo.reloid = pgc.oid
           LEFT JOIN (SELECT pgce.reloid,
                             pgce.value
                      FROM pg_class_extended AS pgce
                      WHERE pgce.colnum = 6) AS mvinfo ON mvinfo.reloid = pgc.oid
           LEFT JOIN (SELECT pgce.reloid,
                             pgce.colnum,
                             pgce.value
                      FROM pg_class_extended AS pgce
                      WHERE pgce.colnum = 25) AS rdsinfo ON rdsinfo.reloid = pgc.oid
      WHERE (CAST(CASE
                    WHEN rinfo.colnum = 20
                     AND rinfo.value = CAST('1' AS text)
                      THEN CAST('t' AS text)
                    ELSE CAST('f' AS text)
                  END AS char) = CAST('t' AS bpchar)
          OR CAST(CASE
                    WHEN rdsinfo.colnum = 25
                      THEN CASE
                             WHEN rdsinfo.value = CAST('0' AS text)
                               THEN CAST('f' AS text)
                             WHEN rdsinfo.value = CAST('1' AS text)
                               THEN CAST('t' AS text)
                             ELSE CAST('u' AS text)
                           END
                    ELSE CASE
                           WHEN CAST(CASE
                                       WHEN rinfo.colnum = 20
                                        AND rinfo.value = CAST('1' AS text)
                                         THEN CAST('t' AS text)
                                       ELSE CAST('f' AS text)
                                     END AS char) = CAST('t' AS bpchar)
                             THEN CAST('t' AS text)
                           ELSE CAST('f' AS text)
                         END
                  END AS char) = CAST('t' AS bpchar))
        AND has_schema_privilege(CAST("current_user"() AS name),
                                 CAST(pns.nspname AS text),
                                 CAST('USAGE' AS text))) AS rls_info_on_all_rels
WHERE EXISTS (SELECT 1
              FROM pg_identity
              WHERE pg_identity.useid = current_user_id()
                AND pg_identity.usesuper = TRUE)
   OR user_is_member_of(CAST("current_user"() AS name),
                        CAST('sys:secadmin' AS name))

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