Listing user-defined functions allows us to understand the available functions and their purpose in a database. By getting the details of the function like their names, argument types, return types, etc. we can gain valuable insights into the functionality and behavior of the functions. Therefore, Postgres offers various methods to get a list of user-defined functions/procedures.

This write-up presents the below-listed methods to get the list of user-defined functions in Postgres:

• Using “\df”
• Using “pg_proc”
• Using “information_schema.routines”

Let’s begin with the “\df” command.

How to List User-Defined Functions Using “\df” Command?

The “\df” is a meta-command in Postgres that is executed in the SQL Shell. It helps us get the list of all available functions in the current database, including inbuilt as well as user-defined functions. It retrieves the details of the function like name, accepted arguments, return type, and much more. Execute the following meta-command to get a better insight into the "\df" command:

\df

The output shows that the stated command retrieves the list of functions along with detailed information.

How to List User-Defined Functions Using the “pg_proc” Catalog?

“pg_proc” is a Postgres system catalog table that keeps information regarding functions and procedures defined in a database. It retrieves the function list along with the functions’ metadata, such as name, data types, return data type, etc. Consider the following code snippet for a better understanding of the “pg_proc” catalog:

SELECT proname AS function_name, 
pg_get_function_identity_arguments(oid) AS arguments_accepted
FROM pg_proc
WHERE pronamespace = 'public'::regnamespace;

The output snippet shows that the “pg_proc” successfully retrieves the list of user-defined functions.

How to List User-Defined Functions Using “information_schema.routines” View?

The “information_schema.routines” is a system view in PostgreSQL that contains information about database routines, such as functions and procedures. It retrieves details like routine names, parameter data types, return types, etc. Execute the following code to gain a better understanding of the “information_schema.routines” View:

SELECT routine_name AS function_name,
routine_type AS function_type,
data_type
FROM information_schema.routines
WHERE routine_type = 'FUNCTION' AND routine_schema = 'public';

That’s all about listing the user-defined functions in Postgres.

Conclusion

In PostgreSQL, the “\df” mata-command, “information_schema.routines” View, and the “pg_proc” Catalog is used to get the list of user-defined functions. All these approaches retrieve the details of the user-defined functions like their name, accepted arguments, return type, and much more. This post has illustrated various methods to get a list of user-defined functions in Postgres.