Query below lists all arguments from procedures in PostgreSQL database.

Note

Procedures was introduced in PostgreSQL 11 and under this version query was executed. Query below should work with no errors with older versions but it wouldn't show any result.

Query

select proc.specific_schema as procedure_schema,
       proc.specific_name,
       proc.routine_name as procedure_name,
       proc.external_language,
       args.parameter_name,
       args.parameter_mode,
       args.data_type
from information_schema.routines proc
left join information_schema.parameters args
          on proc.specific_schema = args.specific_schema
          and proc.specific_name = args.specific_name
where proc.routine_schema not in ('pg_catalog', 'information_schema')
      and proc.routine_type = 'PROCEDURE'
order by procedure_schema,
         specific_name,
         procedure_name,
         args.ordinal_position;

Columns

• procedure_schema - name of the schema conaining procedure
• specific_name - name of the specific procedure (used for overloading procedures)
• procedure_name - name of the procedure
• external_language - language the procedure is written in
• parameter_name - procedure parameter name
• parameter_mode - direction of the parameter
  • IN
  • OUT
  • IN/OUT
• data_type -data type of parameter

Rows

• One row represents one procedure parameter or procedure if it hasn't any parameters
• Scope of rows: all parameters from all procedures
• Ordered by schema name and name of procedure, parameter position

Sample results