List stored procedures in PostgreSQL database

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

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
0
There are no comments. Click here to write the first comment.