Redshift Observatory

System Table Tracker

System view information_schema.element_types version 1.0.52931 / 2023-07-07

View Text

SELECT CAST(current_database() AS information_schema.sql_identifier) AS object_catalog,
       CAST(n.nspname AS information_schema.sql_identifier) AS object_schema,
       CAST(x.objname AS information_schema.sql_identifier) AS object_name,
       CAST(x.objtype AS information_schema.character_data) AS object_type,
       CAST(x.objdtdid AS information_schema.sql_identifier) AS array_type_identifier,
       CAST(CASE
              WHEN nbt.nspname = CAST('pg_catalog' AS name)
                THEN format_type(bt.oid,
                                 CAST(NULL AS integer))
              ELSE CAST('USER-DEFINED' AS text)
            END AS information_schema.character_data) AS data_type,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS character_maximum_length,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS character_octet_length,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS character_set_name,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS collation_name,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS numeric_precision,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS numeric_precision_radix,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS numeric_scale,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS datetime_precision,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS interval_type,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS interval_precision,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS domain_default,
       CAST(current_database() AS information_schema.sql_identifier) AS udt_catalog,
       CAST(nbt.nspname AS information_schema.sql_identifier) AS udt_schema,
       CAST(bt.typname AS information_schema.sql_identifier) AS udt_name,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS scope_name,
       CAST(CAST(NULL AS information_schema.cardinal_number) AS information_schema.cardinal_number) AS maximum_cardinality,
       CAST(CAST('a' AS text) || CAST(x.objdtdid AS text) AS information_schema.sql_identifier) AS dtd_identifier
FROM pg_namespace AS n,
     pg_type AS at,
     pg_namespace AS nbt,
     pg_type AS bt,
     (((SELECT c.relnamespace,
               CAST(c.relname AS information_schema.sql_identifier) AS relname,
               CAST('TABLE' AS varchar) AS varchar,
               a.attnum,
               a.atttypid
        FROM pg_class AS c, pg_attribute AS a
        WHERE c.oid = a.attrelid
          AND (c.relkind = CAST('r' AS "char")
            OR c.relkind = CAST('v' AS "char"))
          AND a.attnum > 0
          AND NOT a.attisdropped

        UNION ALL

        SELECT t.typnamespace,
               CAST(t.typname AS information_schema.sql_identifier) AS typname,
               CAST('DOMAIN' AS varchar) AS varchar,
               1,
               t.typbasetype
        FROM pg_type AS t
        WHERE t.typtype = CAST('d' AS "char"))

       UNION ALL

       SELECT p.pronamespace,
              CAST((CAST(p.proname AS text) || CAST('_' AS text)) || CAST(CAST(p.oid AS varchar) AS text) AS information_schema.sql_identifier) AS sql_identifier,
              CAST('ROUTINE' AS varchar) AS varchar,
              pos.n,
              (p.proargtypes)[pos.n - 1] AS proargtypes
       FROM pg_proc AS p,
            information_schema._pg_keypositions() AS pos (n)
       WHERE p.pronargs >= pos.n)

      UNION ALL

      SELECT p.pronamespace,
             CAST((CAST(p.proname AS text) || CAST('_' AS text)) || CAST(CAST(p.oid AS varchar) AS text) AS information_schema.sql_identifier) AS sql_identifier,
             CAST('ROUTINE' AS varchar) AS varchar,
             0,
             p.prorettype
      FROM pg_proc AS p) AS x (objschema, objname, objtype, objdtdid, objtypeid)
WHERE n.oid = x.objschema
  AND at.oid = x.objtypeid
  AND (at.typelem <> CAST(0 AS oid)
   AND at.typlen = -1)
  AND at.typelem = bt.oid
  AND nbt.oid = bt.typnamespace
  AND (CAST(n.nspname AS information_schema.sql_identifier),
       CAST(x.objname AS information_schema.sql_identifier),
       CAST(x.objtype AS information_schema.character_data),
       CAST(x.objdtdid AS information_schema.sql_identifier)) IN (SELECT data_type_privileges.object_schema,
                                                                         data_type_privileges.object_name,
                                                                         data_type_privileges.object_type,
                                                                         data_type_privileges.dtd_identifier
                                                                  FROM information_schema.data_type_privileges)

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