Query below return all stored procedures and information about it in PostgreSQL database.

Query

select n.nspname as schema_name,
       p.proname as specific_name,
       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
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')
      and p.prokind = 'p'
order by schema_name,
         specific_name;

Columns

• schema_name - name of the schema conaining procedure
• specific_name - name of the procedure
• language - language in which procedure is written
• definition - actual source code of the procedure for interpreted languages, a link symbol or file name (depending on language)
• arguments - procedure arguments type

Rows

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

Sample results