Redshift Observatory

System Table Tracker

System view information_schema.routines version 1.0.46534 / 2023-02-21

schema name column data type
information_schema routines as_locator character_data
information_schema routines character_maximum_length cardinal_number
information_schema routines character_octet_length cardinal_number
information_schema routines character_set_catalog sql_identifier
information_schema routines character_set_name sql_identifier
information_schema routines character_set_schema sql_identifier
information_schema routines collation_catalog sql_identifier
information_schema routines collation_name sql_identifier
information_schema routines collation_schema sql_identifier
information_schema routines data_type character_data
information_schema routines datetime_precision cardinal_number
information_schema routines dtd_identifier sql_identifier
information_schema routines external_language character_data
information_schema routines external_name character_data
information_schema routines interval_precision character_data
information_schema routines interval_type character_data
information_schema routines is_deterministic character_data
information_schema routines is_implicitly_invocable character_data
information_schema routines is_null_call character_data
information_schema routines is_user_defined_cast character_data
information_schema routines max_dynamic_result_sets cardinal_number
information_schema routines maximum_cardinality cardinal_number
information_schema routines module_catalog sql_identifier
information_schema routines module_name sql_identifier
information_schema routines module_schema sql_identifier
information_schema routines numeric_precision cardinal_number
information_schema routines numeric_precision_radix cardinal_number
information_schema routines numeric_scale cardinal_number
information_schema routines parameter_style character_data
information_schema routines routine_body character_data
information_schema routines routine_catalog sql_identifier
information_schema routines routine_definition character_data
information_schema routines routine_name sql_identifier
information_schema routines routine_schema sql_identifier
information_schema routines routine_type character_data
information_schema routines schema_level_routine character_data
information_schema routines scope_catalog sql_identifier
information_schema routines scope_name sql_identifier
information_schema routines scope_schema sql_identifier
information_schema routines security_type character_data
information_schema routines specific_catalog sql_identifier
information_schema routines specific_name sql_identifier
information_schema routines specific_schema sql_identifier
information_schema routines sql_data_access character_data
information_schema routines sql_path character_data
information_schema routines to_sql_specific_catalog sql_identifier
information_schema routines to_sql_specific_name sql_identifier
information_schema routines to_sql_specific_schema sql_identifier
information_schema routines type_udt_catalog sql_identifier
information_schema routines type_udt_name sql_identifier
information_schema routines type_udt_schema sql_identifier
information_schema routines udt_catalog sql_identifier
information_schema routines udt_name sql_identifier
information_schema routines udt_schema sql_identifier

View Text

SELECT CAST(current_database() AS information_schema.sql_identifier) AS specific_catalog,
       CAST(n.nspname AS information_schema.sql_identifier) AS specific_schema,
       CAST((CAST(p.proname AS text) || CAST('_' AS text)) || CAST(CAST(p.oid AS varchar) AS text) AS information_schema.sql_identifier) AS specific_name,
       CAST(current_database() AS information_schema.sql_identifier) AS routine_catalog,
       CAST(n.nspname AS information_schema.sql_identifier) AS routine_schema,
       CAST(p.proname AS information_schema.sql_identifier) AS routine_name,
       CAST(CASE
              WHEN p.prorettype <> CAST(0 AS oid)
                THEN CAST('FUNCTION' AS text)
              ELSE CAST('PROCEDURE' AS text)
            END AS information_schema.character_data) AS routine_type,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS module_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS module_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS module_name,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS udt_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS udt_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS udt_name,
       CAST(CASE
              WHEN p.prorettype = CAST(0 AS oid)
                THEN CAST(NULL AS text)
              WHEN t.typelem <> CAST(0 AS oid)
               AND t.typlen = -1
                THEN CAST('ARRAY' AS text)
              WHEN nt.nspname = CAST('pg_catalog' AS name)
                THEN format_type(t.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(CASE
              WHEN p.prorettype <> CAST(0 AS oid)
                THEN current_database()
              ELSE CAST(NULL AS name)
            END AS information_schema.sql_identifier) AS type_udt_catalog,
       CAST(nt.nspname AS information_schema.sql_identifier) AS type_udt_schema,
       CAST(t.typname AS information_schema.sql_identifier) AS type_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(0 AS information_schema.sql_identifier) AS dtd_identifier,
       CAST(CASE
              WHEN l.lanname = CAST('sql' AS name)
                THEN CAST('SQL' AS text)
              ELSE CAST('EXTERNAL' AS text)
            END AS information_schema.character_data) AS routine_body,
       CAST(CASE
              WHEN u.usename = CAST("current_user"() AS name)
                THEN p.prosrc
              ELSE CAST(NULL AS text)
            END AS information_schema.character_data) AS routine_definition,
       CAST(CASE
              WHEN l.lanname = CAST('c' AS name)
                THEN p.prosrc
              ELSE CAST(NULL AS text)
            END AS information_schema.character_data) AS external_name,
       CAST(upper(CAST(l.lanname AS text)) AS information_schema.character_data) AS external_language,
       CAST(CAST('GENERAL' AS information_schema.character_data) AS information_schema.character_data) AS parameter_style,
       CAST(CASE
              WHEN p.provolatile = CAST('i' AS "char")
                THEN CAST('YES' AS text)
              ELSE CAST('NO' AS text)
            END AS information_schema.character_data) AS is_deterministic,
       CAST(CAST('MODIFIES' AS information_schema.character_data) AS information_schema.character_data) AS sql_data_access,
       CAST(CASE
              WHEN p.prorettype <> CAST(0 AS oid)
                THEN CASE
                       WHEN p.proisstrict
                         THEN CAST('YES' AS text)
                       ELSE CAST('NO' AS text)
                     END
              ELSE CAST(NULL AS text)
            END AS information_schema.character_data) AS is_null_call,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS sql_path,
       CAST(CAST('YES' AS information_schema.character_data) AS information_schema.character_data) AS schema_level_routine,
       CAST(0 AS information_schema.cardinal_number) AS max_dynamic_result_sets,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS is_user_defined_cast,
       CAST(CAST(NULL AS information_schema.character_data) AS information_schema.character_data) AS is_implicitly_invocable,
       CAST(CASE
              WHEN p.prosecdef
                THEN CAST('DEFINER' AS text)
              ELSE CAST('INVOKER' AS text)
            END AS information_schema.character_data) AS security_type,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS to_sql_specific_catalog,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS to_sql_specific_schema,
       CAST(CAST(NULL AS information_schema.sql_identifier) AS information_schema.sql_identifier) AS to_sql_specific_name,
       CAST(CAST('NO' AS information_schema.character_data) AS information_schema.character_data) AS as_locator
FROM pg_namespace AS n
     INNER JOIN pg_proc AS p ON n.oid = p.pronamespace
     INNER JOIN pg_language AS l ON p.prolang = l.oid
     INNER JOIN pg_user AS u ON p.proowner = u.usesysid
     LEFT JOIN (pg_type AS t
                INNER JOIN pg_namespace AS nt ON t.typnamespace = nt.oid)
        ON p.prorettype = t.oid
WHERE u.usename = CAST("current_user"() AS name)
   OR has_function_privilege(p.oid,
                             CAST('EXECUTE' AS text))


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