get PostgreSQL command input and output types

Question

Is there any way to get a list of the input parameters and their types and output parameters and their types for any PostgreSQL command? I am primarily interested in select statements and stored procedure calls. I don't want to execute the command to get the information. I am open to any automate-able solutions that I can use. Ideally, it would be a stored procedure I could call or a native library I could wrap. I want the datatype info for each parameter.

With Microsoft SQL Server, this information can be obtained from sys.sp_describe_first_result_set as described in New Metadata Discovery Features in SQL Server 2012.

I don't think there is anything similar to `sys.sp_describe_first_result_set`. You might have to create your own proc for PostgreSQL. — zedfoxus, Dec 11 '15 at 04:32

score 2 · Answer 1 · answered Dec 11 '15 at 05:19

The PostgreSQL hasn't procedures (not in T-SQL sense) - and function result type is declared statically - just you have to look to table pg_proc. Following query shows info for nanvl function.

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
  WHEN p.proisagg THEN 'agg'
  WHEN p.proiswindow THEN 'window'
  WHEN p.prorettype = 'pg_catalog.trigger'::pg_catalog.regtype THEN 'trigger'
  ELSE 'normal'
 END as "Type"
FROM pg_catalog.pg_proc p
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 WHERE p.proname ~ '^(nanvl)$'
  AND pg_catalog.pg_function_is_visible(p.oid)
ORDER BY 1, 2, 4;

                                 List of functions
┌────────┬───────┬──────────────────┬─────────────────────────────────────┬────────┐
│ Schema │ Name  │ Result data type │         Argument data types         │  Type  │
╞════════╪═══════╪══════════════════╪═════════════════════════════════════╪════════╡
│ public │ nanvl │ double precision │ double precision, character varying │ normal │
│ public │ nanvl │ double precision │ double precision, double precision  │ normal │
│ public │ nanvl │ numeric          │ numeric, character varying          │ normal │
│ public │ nanvl │ numeric          │ numeric, numeric                    │ normal │
│ public │ nanvl │ real             │ real, character varying             │ normal │
│ public │ nanvl │ real             │ real, real                          │ normal │
└────────┴───────┴──────────────────┴─────────────────────────────────────┴────────┘
(6 rows)

Get the result of query is little bit more difficult (but it is fully generic) - you have to create prepared statement or cursor, and you then can use a API functions PQdescribePrepared or PQdescribePortal` for returning type info.

get PostgreSQL command input and output types

1 Answers1