How to List Functions in PostgreSQL

Built-in and user-defined functions in PostgreSQL

PostgreSQL functions can be categorized into built-in and user-defined types.

Built-in functions, also known as system functions, come predefined with PostgreSQL upon installation. They cover a wide range of tasks such as mathematical operations, string manipulation, date and time handling, aggregates, and more. They are optimized for performance, eliminating the need for additional programming efforts.

User-defined functions, on the other hand, are implemented by developers to execute specific tasks. They encapsulate code into reusable units, which can be called multiple times across different parts of an application. This way, PostgreSQL users can extend database functionality beyond its default capabilities without repetitive custom coding for each scenario.

A common task in daily PostgreSQL database management routines is checking the user-defined database functions.

Methods to list all functions in PostgreSQL

List functions using command-line tools

PostgreSQL's default command-line interface, psql, offers an efficient way to interact with databases. With its help, you can connect to databases, manipulate data, execute commands, and retrieve results.

To list all user-defined functions in PostgreSQL, use the following command:

\df 

This command retrieves a list of functions from the current database, including details such as function names, accepted arguments, return types, and more. Let's examine the list of functions in the test database sakila, which we will use to illustrate our article.

We can also filter the results by schema or function name.

To filter the list of functions by schema name, execute the below command with psql:

\df schema_name.*

Assume we want to list all functions from the public schema. In that case, the query is:

\df public.*

Or, we can filter the list of functions by name using the following command (note that you can use masks in that command):

\df *function_name*

Let us view all functions that contain the keyword customer in their names:

\df *customer*

Also, you can retrieve detailed information about all functions with the help of the \df+ command.

Many PostgreSQL specialists favor the \df command and its variations for their simplicity and accuracy. Alternatively, you can retrieve the list of functions using dedicated SQL queries.

List functions using SQL queries

The information regarding PostgreSQL database functions is stored in the pg_proc system table and the information_schema.routines system view. These sources can be queried to retrieve the necessary information.

Using pg_proc:

pg_proc is a system catalog in PostgreSQL that stores details about functions and procedures within the current database. Queries to this catalog table can fetch information such as function names, data types, accepted arguments, and more. For example:

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

Using information_schema.routines:

information_schema.routines is a PostgreSQL system view containing information about database functions and procedures. Queries to this view can retrieve a list of all user-defined functions and their related details, as shown in the query below:

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

Using SQL queries to list user-defined functions in PostgreSQL databases offers flexibility to specify the required information and organize the output for better readability. However, professional database management tools with graphical user interfaces, like dbForge Studio for PostgreSQL, can greatly enhance convenience and efficiency in managing these tasks.

List functions using dbForge Studio for PostgreSQL

When working with PostgreSQL-based databases (including Amazon Redshift), dbForge Studio for PostgreSQL provides extensive functionality. Its toolset covers all standard database tasks, enabling users to perform them more efficiently and accurately through a visual interface along with the automation of manual routines.

To list the available functions in a PostgreSQL database, you can either execute dedicated SQL queries or view the list in Database Explorer.

Executing SQL queries against the database

The Studio includes a powerful SQL Editor with numerous options that make it easy for users to write, validate, professionally format, and execute SQL queries directly against the target database. Results are displayed in a user-friendly table format.

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

Using Database Explorer

To view the list of user-defined functions in dbForge Studio for PostgreSQL, first navigate to the desired database node in the Database Explorer pane on the left.

• 1. Expand the public schema node
• 2. Find the Functions directory under the public schema and expand it
• 3. View all user-defined functions and their details under Functions

System functions can also be viewed using this method.

Conclusion

Functions in PostgreSQL enable database experts to efficiently perform complex operations without extensive custom coding. User-defined functions are particularly useful for addressing specific tasks across various scenarios, making it essential to access and review their details. This article explores methods for listing functions in PostgreSQL databases, including command-line and third-party GUI tools.

Among these, dbForge Studio for PostgreSQL stands out. This solution, provided by Devart, offers comprehensive database management capabilities through its robust visualization features. Devart offers a fully functional 30-day trial of dbForge Studio for PostgreSQL, allowing users to explore its robust features at full capacity.