Is there a way to list stored procedures and functions source with parameter definition in EDB Postgres using a SQL against the catalog

Question

I want to list all procedures and functions in EDB Postgres with their input & output parameter definitions in a CSV file. Looking at pg_catalog.pg_proc table in EDB, I see that the information about I/O parameters is spread out in two columns mainly:

proargtypes --> Containing the OID for the data types

proargnames --> Argument names

Both of these columns are LOVs containing the data type OIDs and just names.

Is it possible to list the parameters as a combined string (IN Name varchar, IN startdate date,OUT sal int)?

'prosrc' column has the function listing but nothing about parameters.

Want to accomplish this in a SQL e.g. SELECT prosrc, arguments_list.. from pg_catalog.pg_proc.

Looked at the information_schema.routines view also but didn't anything there.

Want to accomplish this in a SQL e.g. SELECT prosrc, arguments_list.. from pg_catalog.pg_proc.

score 0 · Answer 1 · answered Apr 14 '20 at 18:12

Following is a SQL:

SELECT n.nspname as "Schema",
  p.proname as "Name",
  pg_catalog.pg_get_function_result(p.oid) as "Result data type",
  pg_catalog.pg_get_function_arguments(p.oid) as "Argument data types",
 CASE p.prokind
  WHEN 'a' THEN 'agg'
  WHEN 'w' THEN 'window'
  WHEN 'p' THEN 'proc'
  ELSE 'func'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
WHERE p.protype in ('0', '1')
  AND (pg_catalog.pg_function_is_visible(p.oid) OR pg_catalog.pg_procedure_is_visible(p.oid))
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname <> 'sys'
ORDER BY 1, 2, 4, 5;

You can also explore all_sources or dba_source table in EDB.

Is there a way to list stored procedures and functions source with parameter definition in EDB Postgres using a SQL against the catalog

1 Answers1