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;

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);

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)));

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);

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));

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;