Query below return all User Defined Functions and information about it in PostgreSQL database.

Note

PostgreSQL 11 introduce procedures, so we need to distinguish procedures and functions. In this case use: PostgreSQL 11 Query

Universal

Query

select n.nspname as function_schema,
       p.proname as function_name,
       l.lanname as function_language,
       case when l.lanname = 'internal' then p.prosrc
            else pg_get_functiondef(p.oid)
            end as definition,
       pg_get_function_arguments(p.oid) as function_arguments,
       t.typname as return_type
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype 
where n.nspname not in ('pg_catalog', 'information_schema')
order by function_schema,
         function_name;

Columns

• function_schema - name of the schema containing function
• function_name - name of the function
• function_language - language in which function is written
• definition - actual source code of the function for interpreted languages, a link symbol or file name (depending on language)
• function_arguments - function arguments type
• return_type - function return type

Rows

• One row - represents one function
• Scope of rows: - all functions in database
• Ordered by - function schema, function name

Sample results

PostgreSQL 11+

Query

select n.nspname as schema_name,
       p.proname as specific_name,
       case p.prokind 
            when 'f' then 'FUNCTION'
            when 'p' then 'PROCEDURE'
            when 'a' then 'AGGREGATE'
            when 'w' then 'WINDOW'
            end as kind,
       l.lanname as language,
       case when l.lanname = 'internal' then p.prosrc
            else pg_get_functiondef(p.oid)
            end as definition,
       pg_get_function_arguments(p.oid) as arguments,
       t.typname as return_type
from pg_proc p
left join pg_namespace n on p.pronamespace = n.oid
left join pg_language l on p.prolang = l.oid
left join pg_type t on t.oid = p.prorettype 
where n.nspname not in ('pg_catalog', 'information_schema')
order by schema_name,
         specific_name;

Columns

• schema_name - name of the schema containing function/procedure
• specific_name - name of the function/procedure
• kind:
  • FUNCTION - normal function
  • PROCEDURE - procedure
  • AGGREGATE - aggregate function
  • WINDOW - window function
• language - language in which function/procedure is written
• definition - actual source code of the function/procedure for interpreted languages, a link symbol or file name (depending on language)
• arguments - function/procedure arguments
• return_type - return type

Rows

• One row - represents one function or procedure
• Scope of rows: - all functions and procedures in database
• Ordered by - schema name, specific name

Sample results