Fetching function definitions from GPDB

There are 5 information schema views that can be used to find all details including DDL of a function in Greenplum.

For example

select  routine_name,routine_definition from information_schema.routines;
The gadget spec URL could not be found
testdb=# \d information_schema.*routine*

1. View "information_schema.check_constraint_routine_usage"
       Column       |               Type                | Modifiers 
--------------------+-----------------------------------+-----------
 constraint_catalog | information_schema.sql_identifier | 
 constraint_schema  | information_schema.sql_identifier | 
 constraint_name    | information_schema.sql_identifier | 
 specific_catalog   | information_schema.sql_identifier | 
 specific_schema    | information_schema.sql_identifier | 
 specific_name      | information_schema.sql_identifier | 
View definition:
 SELECT current_database()::information_schema.sql_identifier AS constraint_catalog, nc.nspname::information_schema.sql_identifier AS constraint_schema, c.conname::information_schema.sql_identifier AS constraint_name, current_database()::information_schema.sql_identifier AS specific_catalog, np.nspname::information_schema.sql_identifier AS specific_schema, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier AS specific_name
   FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np
  WHERE nc.oid = c.connamespace AND c.contype = 'c'::"char" AND c.oid = d.objid AND d.classid = 'pg_constraint'::regclass::oid AND d.refobjid = p.oid AND d.refclassid = 'pg_proc'::regclass::oid AND p.pronamespace = np.oid AND pg_has_role(p.proowner, 'USAGE'::text);
The gadget spec URL could not be found
2. View "information_schema.role_routine_grants"
      Column      |               Type                | Modifiers 
------------------+-----------------------------------+-----------
 grantor          | information_schema.sql_identifier | 
 grantee          | information_schema.sql_identifier | 
 specific_catalog | information_schema.sql_identifier | 
 specific_schema  | information_schema.sql_identifier | 
 specific_name    | information_schema.sql_identifier | 
 routine_catalog  | information_schema.sql_identifier | 
 routine_schema   | information_schema.sql_identifier | 
 routine_name     | information_schema.sql_identifier | 
 privilege_type   | information_schema.character_data | 
 is_grantable     | information_schema.character_data | 
View definition:
 SELECT u_grantor.rolname::information_schema.sql_identifier AS grantor, g_grantee.rolname::information_schema.sql_identifier AS grantee, current_database()::information_schema.sql_identifier AS specific_catalog, n.nspname::information_schema.sql_identifier AS specific_schema, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier AS specific_name, current_database()::information_schema.sql_identifier AS routine_catalog, n.nspname::information_schema.sql_identifier AS routine_schema, p.proname::information_schema.sql_identifier AS routine_name, 'EXECUTE'::character varying::information_schema.character_data AS privilege_type, 
        CASE
            WHEN aclcontains(p.proacl, makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE'::text, true)) THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_grantable
   FROM pg_proc p, pg_namespace n, pg_authid u_grantor, pg_authid g_grantee
  WHERE p.pronamespace = n.oid AND aclcontains(p.proacl, makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE'::text, false)) AND ((u_grantor.rolname IN ( SELECT enabled_roles.role_name
           FROM information_schema.enabled_roles)) OR (g_grantee.rolname IN ( SELECT enabled_roles.role_name
           FROM information_schema.enabled_roles)));
The gadget spec URL could not be found
3. View "information_schema.routine_privileges"
      Column      |               Type                | Modifiers 
------------------+-----------------------------------+-----------
 grantor          | information_schema.sql_identifier | 
 grantee          | information_schema.sql_identifier | 
 specific_catalog | information_schema.sql_identifier | 
 specific_schema  | information_schema.sql_identifier | 
 specific_name    | information_schema.sql_identifier | 
 routine_catalog  | information_schema.sql_identifier | 
 routine_schema   | information_schema.sql_identifier | 
 routine_name     | information_schema.sql_identifier | 
 privilege_type   | information_schema.character_data | 
 is_grantable     | information_schema.character_data | 
View definition:
 SELECT u_grantor.rolname::information_schema.sql_identifier AS grantor, grantee.rolname::information_schema.sql_identifier AS grantee, current_database()::information_schema.sql_identifier AS specific_catalog, n.nspname::information_schema.sql_identifier AS specific_schema, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier AS specific_name, current_database()::information_schema.sql_identifier AS routine_catalog, n.nspname::information_schema.sql_identifier AS routine_schema, p.proname::information_schema.sql_identifier AS routine_name, 'EXECUTE'::character varying::information_schema.character_data AS privilege_type, 
        CASE
            WHEN aclcontains(p.proacl, makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE'::text, true)) THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_grantable
   FROM pg_proc p, pg_namespace n, pg_authid u_grantor, ( SELECT pg_authid.oid, pg_authid.rolname
           FROM pg_authid
UNION ALL 
         SELECT 0::oid AS oid, 'PUBLIC') grantee(oid, rolname)
  WHERE p.pronamespace = n.oid AND aclcontains(p.proacl, makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE'::text, false)) AND (pg_has_role(u_grantor.oid, 'USAGE'::text) OR pg_has_role(grantee.oid, 'USAGE'::text) OR grantee.rolname = 'PUBLIC'::name);
The gadget spec URL could not be found
4. View "information_schema.routines"
               Column                |                Type                | Modifiers 
-------------------------------------+------------------------------------+-----------
 specific_catalog                    | information_schema.sql_identifier  | 
 specific_schema                     | information_schema.sql_identifier  | 
 specific_name                       | information_schema.sql_identifier  | 
 routine_catalog                     | information_schema.sql_identifier  | 
 routine_schema                      | information_schema.sql_identifier  | 
 routine_name                        | information_schema.sql_identifier  | 
 routine_type                        | information_schema.character_data  | 
 module_catalog                      | information_schema.sql_identifier  | 
 module_schema                       | information_schema.sql_identifier  | 
 module_name                         | information_schema.sql_identifier  | 
 udt_catalog                         | information_schema.sql_identifier  | 
 udt_schema                          | information_schema.sql_identifier  | 
 udt_name                            | information_schema.sql_identifier  | 
 data_type                           | information_schema.character_data  | 
 character_maximum_length            | information_schema.cardinal_number | 
 character_octet_length              | information_schema.cardinal_number | 
 character_set_catalog               | information_schema.sql_identifier  | 
 character_set_schema                | information_schema.sql_identifier  | 
 character_set_name                  | information_schema.sql_identifier  | 
 collation_catalog                   | information_schema.sql_identifier  | 
 collation_schema                    | information_schema.sql_identifier  | 
 collation_name                      | information_schema.sql_identifier  | 
 numeric_precision                   | information_schema.cardinal_number | 
 numeric_precision_radix             | information_schema.cardinal_number | 
 numeric_scale                       | information_schema.cardinal_number | 
 datetime_precision                  | information_schema.cardinal_number | 
 interval_type                       | information_schema.character_data  | 
 interval_precision                  | information_schema.character_data  | 
 type_udt_catalog                    | information_schema.sql_identifier  | 
 type_udt_schema                     | information_schema.sql_identifier  | 
 type_udt_name                       | information_schema.sql_identifier  | 
 scope_catalog                       | information_schema.sql_identifier  | 
 scope_schema                        | information_schema.sql_identifier  | 
 scope_name                          | information_schema.sql_identifier  | 
 maximum_cardinality                 | information_schema.cardinal_number | 
 dtd_identifier                      | information_schema.sql_identifier  | 
 routine_body                        | information_schema.character_data  | 
 routine_definition                  | information_schema.character_data  | 
 external_name                       | information_schema.character_data  | 
 external_language                   | information_schema.character_data  | 
 parameter_style                     | information_schema.character_data  | 
 is_deterministic                    | information_schema.character_data  | 
 sql_data_access                     | information_schema.character_data  | 
 is_null_call                        | information_schema.character_data  | 
 sql_path                            | information_schema.character_data  | 
 schema_level_routine                | information_schema.character_data  | 
 max_dynamic_result_sets             | information_schema.cardinal_number | 
 is_user_defined_cast                | information_schema.character_data  | 
 is_implicitly_invocable             | information_schema.character_data  | 
 security_type                       | information_schema.character_data  | 
 to_sql_specific_catalog             | information_schema.sql_identifier  | 
 to_sql_specific_schema              | information_schema.sql_identifier  | 
 to_sql_specific_name                | information_schema.sql_identifier  | 
 as_locator                          | information_schema.character_data  | 
 created                             | information_schema.time_stamp      | 
 last_altered                        | information_schema.time_stamp      | 
 new_savepoint_level                 | information_schema.character_data  | 
 is_udt_dependent                    | information_schema.character_data  | 
 result_cast_from_data_type          | information_schema.character_data  | 
 result_cast_as_locator              | information_schema.character_data  | 
 result_cast_char_max_length         | information_schema.cardinal_number | 
 result_cast_char_octet_length       | information_schema.cardinal_number | 
 result_cast_char_set_catalog        | information_schema.sql_identifier  | 
 result_cast_char_set_schema         | information_schema.sql_identifier  | 
 result_cast_character_set_name      | information_schema.sql_identifier  | 
 result_cast_collation_catalog       | information_schema.sql_identifier  | 
 result_cast_collation_schema        | information_schema.sql_identifier  | 
 result_cast_collation_name          | information_schema.sql_identifier  | 
 result_cast_numeric_precision       | information_schema.cardinal_number | 
 result_cast_numeric_precision_radix | information_schema.cardinal_number | 
 result_cast_numeric_scale           | information_schema.cardinal_number | 
 result_cast_datetime_precision      | information_schema.cardinal_number | 
 result_cast_interval_type           | information_schema.character_data  | 
 result_cast_interval_precision      | information_schema.character_data  | 
 result_cast_type_udt_catalog        | information_schema.sql_identifier  | 
 result_cast_type_udt_schema         | information_schema.sql_identifier  | 
 result_cast_type_udt_name           | information_schema.sql_identifier  | 
 result_cast_scope_catalog           | information_schema.sql_identifier  | 
 result_cast_scope_schema            | information_schema.sql_identifier  | 
 result_cast_scope_name              | information_schema.sql_identifier  | 
 result_cast_maximum_cardinality     | information_schema.cardinal_number | 
 result_cast_dtd_identifier          | information_schema.sql_identifier  | 
View definition:
 SELECT current_database()::information_schema.sql_identifier AS specific_catalog, n.nspname::information_schema.sql_identifier AS specific_schema, ((p.proname::text || '_'::text
) || p.oid::text)::information_schema.sql_identifier AS specific_name, current_database()::information_schema.sql_identifier AS routine_catalog, n.nspname::information_schema.sql
_identifier AS routine_schema, p.proname::information_schema.sql_identifier AS routine_name, 'FUNCTION'::character varying::information_schema.character_data AS routine_type, NUL
L::character varying::information_schema.sql_identifier AS module_catalog, NULL::character varying::information_schema.sql_identifier AS module_schema, NULL::character varying::i
nformation_schema.sql_identifier AS module_name, NULL::character varying::information_schema.sql_identifier AS udt_catalog, NULL::character varying::information_schema.sql_identi
fier AS udt_schema, NULL::character varying::information_schema.sql_identifier AS udt_name, 
        CASE
            WHEN t.typelem <> 0::oid AND t.typlen = (-1) THEN 'ARRAY'::text
            WHEN nt.nspname = 'pg_catalog'::name THEN format_type(t.oid, NULL::integer)
            ELSE 'USER-DEFINED'::text
        END::information_schema.character_data AS data_type, NULL::integer::information_schema.cardinal_number AS character_maximum_length, NULL::integer::information_schema.card
inal_number AS character_octet_length, NULL::character varying::information_schema.sql_identifier AS character_set_catalog, NULL::character varying::information_schema.sql_identi
fier AS character_set_schema, NULL::character varying::information_schema.sql_identifier AS character_set_name, NULL::character varying::information_schema.sql_identifier AS coll
ation_catalog, NULL::character varying::information_schema.sql_identifier AS collation_schema, NULL::character varying::information_schema.sql_identifier AS collation_name, NULL:
:integer::information_schema.cardinal_number AS numeric_precision, NULL::integer::information_schema.cardinal_number AS numeric_precision_radix, NULL::integer::information_schema
.cardinal_number AS numeric_scale, NULL::integer::information_schema.cardinal_number AS datetime_precision, NULL::character varying::information_schema.character_data AS interval
_type, NULL::character varying::information_schema.character_data AS interval_precision, current_database()::information_schema.sql_identifier AS type_udt_catalog, nt.nspname::in
formation_schema.sql_identifier AS type_udt_schema, t.typname::information_schema.sql_identifier AS type_udt_name, NULL::character varying::information_schema.sql_identifier AS s
cope_catalog, NULL::character varying::information_schema.sql_identifier AS scope_schema, NULL::character varying::information_schema.sql_identifier AS scope_name, NULL::integer:
:information_schema.cardinal_number AS maximum_cardinality, 0::information_schema.sql_identifier AS dtd_identifier, 
        CASE
            WHEN l.lanname = 'sql'::name THEN 'SQL'::text
            ELSE 'EXTERNAL'::text
        END::information_schema.character_data AS routine_body, 
        CASE
            WHEN pg_has_role(p.proowner, 'USAGE'::text) THEN p.prosrc
            ELSE NULL::text
        END::information_schema.character_data AS routine_definition, 
        CASE
            WHEN l.lanname = 'c'::name THEN p.prosrc
            ELSE NULL::text
        END::information_schema.character_data AS external_name, upper(l.lanname::text)::information_schema.character_data AS external_language, 'GENERAL'::character varying::inf
ormation_schema.character_data AS parameter_style, 
        CASE
            WHEN p.provolatile = 'i'::"char" THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_deterministic, 'MODIFIES'::character varying::information_schema.character_data AS sql_data_access, 
        CASE
            WHEN p.proisstrict THEN 'YES'::text
            ELSE 'NO'::text
        END::information_schema.character_data AS is_null_call, NULL::character varying::information_schema.character_data AS sql_path, 'YES'::character varying::information_sche
ma.character_data AS schema_level_routine, 0::information_schema.cardinal_number AS max_dynamic_result_sets, NULL::character varying::information_schema.character_data AS is_user
_defined_cast, NULL::character varying::information_schema.character_data AS is_implicitly_invocable, 
        CASE
            WHEN p.prosecdef THEN 'DEFINER'::text
            ELSE 'INVOKER'::text
        END::information_schema.character_data AS security_type, NULL::character varying::information_schema.sql_identifier AS to_sql_specific_catalog, NULL::character varying::i
nformation_schema.sql_identifier AS to_sql_specific_schema, NULL::character varying::information_schema.sql_identifier AS to_sql_specific_name, 'NO'::character varying::informati
on_schema.character_data AS as_locator, NULL::timestamp with time zone::information_schema.time_stamp AS created, NULL::timestamp with time zone::information_schema.time_stamp AS
 last_altered, NULL::character varying::information_schema.character_data AS new_savepoint_level, 'YES'::character varying::information_schema.character_data AS is_udt_dependent,
 NULL::character varying::information_schema.character_data AS result_cast_from_data_type, NULL::character varying::information_schema.character_data AS result_cast_as_locator, N
ULL::integer::information_schema.cardinal_number AS result_cast_char_max_length, NULL::integer::information_schema.cardinal_number AS result_cast_char_octet_length, NULL::charact
er varying::information_schema.sql_identifier AS result_cast_char_set_catalog, NULL::character varying::information_schema.sql_identifier AS result_cast_char_set_schema, NULL::ch
aracter varying::information_schema.sql_identifier AS result_cast_character_set_name, NULL::character varying::information_schema.sql_identifier AS result_cast_collation_catalog,
 NULL::character varying::information_schema.sql_identifier AS result_cast_collation_schema, NULL::character varying::information_schema.sql_identifier AS result_cast_collation_n
ame, NULL::integer::information_schema.cardinal_number AS result_cast_numeric_precision, NULL::integer::information_schema.cardinal_number AS result_cast_numeric_precision_radix,
 NULL::integer::information_schema.cardinal_number AS result_cast_numeric_scale, NULL::integer::information_schema.cardinal_number AS result_cast_datetime_precision, NULL::charac
ter varying::information_schema.character_data AS result_cast_interval_type, NULL::character varying::information_schema.character_data AS result_cast_interval_precision, NULL::c
haracter varying::information_schema.sql_identifier AS result_cast_type_udt_catalog, NULL::character varying::information_schema.sql_identifier AS result_cast_type_udt_schema, NU
LL::character varying::information_schema.sql_identifier AS result_cast_type_udt_name, NULL::character varying::information_schema.sql_identifier AS result_cast_scope_catalog, NU
LL::character varying::information_schema.sql_identifier AS result_cast_scope_schema, NULL::character varying::information_schema.sql_identifier AS result_cast_scope_name, NULL::
integer::information_schema.cardinal_number AS result_cast_maximum_cardinality, NULL::character varying::information_schema.sql_identifier AS result_cast_dtd_identifier
   FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt
  WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid AND (pg_has_role(p.proowner, 'USAGE'::text) OR has_function_privilege(p.
oid, 'EXECUTE'::text));
The gadget spec URL could not be found
 5. View "information_schema.view_routine_usage"
      Column      |               Type                | Modifiers 
------------------+-----------------------------------+-----------
 table_catalog    | information_schema.sql_identifier | 
 table_schema     | information_schema.sql_identifier | 
 table_name       | information_schema.sql_identifier | 
 specific_catalog | information_schema.sql_identifier | 
 specific_schema  | information_schema.sql_identifier | 
 specific_name    | information_schema.sql_identifier | 
View definition:
 SELECT current_database()::information_schema.sql_identifier AS table_catalog, nv.nspname::information_schema.sql_identifier AS table_schema, v.relname::information_schema.sql_identifier AS table_name, current_database()::information_schema.sql_identifier AS specific_catalog, np.nspname::information_schema.sql_identifier AS specific_schema, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier AS specific_name
   FROM pg_namespace nv, pg_class v, pg_depend dv, pg_depend dp, pg_proc p, pg_namespace np
  WHERE nv.oid = v.relnamespace AND v.relkind = 'v'::"char" AND v.oid = dv.refobjid AND dv.refclassid = 'pg_class'::regclass::oid AND dv.classid = 'pg_rewrite'::regclass::oid AND dv.deptype = 'i'::"char" AND dv.objid = dp.objid AND dp.classid = 'pg_rewrite'::regclass::oid AND dp.refclassid = 'pg_proc'::regclass::oid AND dp.refobjid = p.oid AND p.pronamespace = np.oid AND pg_has_role(p.proowner, 'USAGE'::text)
  GROUP BY current_database()::information_schema.sql_identifier, nv.nspname::information_schema.sql_identifier, v.relname::information_schema.sql_identifier, current_database()::information_schema.sql_identifier, np.nspname::information_schema.sql_identifier, ((p.proname::text || '_'::text) || p.oid::text)::information_schema.sql_identifier;
The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

The gadget spec URL could not be found

Comments