cancel
Showing results for 
Search instead for 
Did you mean: 

Listing all functions/procedures with input/output parameter definitions in EDB

SOLVED
Highlighted
Adventurer

Listing all functions/procedures with input/output parameter definitions in EDB

Hello Experts,

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

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.

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

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Level 2 Adventurer

Re: Listing all functions/procedures with input/output parameter definitions in EDB

Hi EDBNewbie  ,

 

Please find the below query to get expected resultset.

 

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
ORDER BY 1, 2, 4, 5;

Test result - 

 

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.proname in ('emp_query','emp_comp')
ORDER BY 1, 2, 4, 5;
 Schema |   Name    | Result data type |                                                                         Argument data types                                                                          | Type 
--------+-----------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
 public | emp_comp  | numeric          | p_sal numeric, p_comm numeric                                                                                                                                        | func
 public | emp_query |                  | p_deptno numeric, INOUT p_empno numeric, INOUT p_ename character varying, OUT p_job character varying, OUT p_hiredate timestamp without time zone, OUT p_sal numeric | proc
(2 rows)

edb=# 

 

Note - Above query executed on EnterpriseDB Advanced Server 11.4.11

 

Please let us know if this resolves your issues.

 

Adventurer

Re: Listing all functions/procedures with input/output parameter definitions in EDB

Tested your query on 9.6.5.10 and 10.1.5 edb database. Worked fine. Thanks so much for your help.

2 REPLIES 2
Level 2 Adventurer

Re: Listing all functions/procedures with input/output parameter definitions in EDB

Hi EDBNewbie  ,

 

Please find the below query to get expected resultset.

 

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
ORDER BY 1, 2, 4, 5;

Test result - 

 

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.proname in ('emp_query','emp_comp')
ORDER BY 1, 2, 4, 5;
 Schema |   Name    | Result data type |                                                                         Argument data types                                                                          | Type 
--------+-----------+------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------+------
 public | emp_comp  | numeric          | p_sal numeric, p_comm numeric                                                                                                                                        | func
 public | emp_query |                  | p_deptno numeric, INOUT p_empno numeric, INOUT p_ename character varying, OUT p_job character varying, OUT p_hiredate timestamp without time zone, OUT p_sal numeric | proc
(2 rows)

edb=# 

 

Note - Above query executed on EnterpriseDB Advanced Server 11.4.11

 

Please let us know if this resolves your issues.

 

Adventurer

Re: Listing all functions/procedures with input/output parameter definitions in EDB

Tested your query on 9.6.5.10 and 10.1.5 edb database. Worked fine. Thanks so much for your help.