How to get the DDL of a function in Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This KB describes how to get the DDL of a function in Greenplum.

Environment

Product Version: 6.20

Resolution

There is a built in function "pg_get_functionde(OID)" which returns the DDL of a function.
It can be used as follows:

psql <db_name> -Atc "select pg_get_functiondef(oid) from pg_proc where proname='<function_name>';"

Note: Replace <function_name> with an appropriate function name value

For example:

gpadmin=# CREATE OR REPLACE FUNCTION Func_Test ()
gpadmin-# RETURNS integer AS $test$
gpadmin$# declare
gpadmin$# test integer;
gpadmin$# BEGIN
gpadmin$# SELECT count (1) into test FROM a;
gpadmin$# RETURN test;
gpadmin$# END;
gpadmin$# $test$ LANGUAGE plpgsql;
CREATE FUNCTION
 
 
gpadmin=# select pg_get_functiondef(oid) from pg_proc where proname = 'func_test';
              pg_get_functiondef
-----------------------------------------------
 CREATE OR REPLACE FUNCTION public.func_test()+
  RETURNS integer                             +
  LANGUAGE plpgsql                            +
 AS $function$                                +
 declare                                      +
 test integer;                                +
 BEGIN                                        +
 SELECT count (1) into test FROM a;           +
 RETURN test;                                 +
 END;                                         +
 $function$                                   +
 
(1 row)