Is it possible to discover the column types from a Postgres function?

Question

I'm working on a utility that is using templates to generate a data access layer against a Postgres database. As part of this I'm trying to dynamically discover the return types of the stored procedures. This is easy enough in simple cases where a single standard type is returned, but I'm struggling when it comes to it returning a user defined type.

I'd appreciate if someone could provide the necessary SQL to return this data.

Thanks Mark

I appreciate the answers that I have so far, which effectively boil to to the following SQL

SELECT p.proname, t.typname, p,proretset
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
INNER JOIN pg_type t ON p.prorettype = t.oid
WHERE n.nspname = 'public'
--and proname = 'foo'
ORDER BY proname;

This will return the name of the return types. However I still need to decompose the type into the properties that make it up when it returns a user defined type.

In the case that a function returns a record I don't think there is any way to discover its return structure other than calling the function and examining its return values.

Answer 1

psql meta commands are an easy shortcut to finding information schema stuff.

try test=# \d? to find introspection info.

then psql -E -c '\df' will show the sql behind the show function command:

d$ psql -E -c '\df+'
********* QUERY **********
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",
CASE
 WHEN p.provolatile = 'i' THEN 'immutable'
 WHEN p.provolatile = 's' THEN 'stable'
 WHEN p.provolatile = 'v' THEN 'volatile'
END as "Volatility",
 pg_catalog.pg_get_userbyid(p.proowner) as "Owner",
 l.lanname as "Language",
 p.prosrc as "Source code",
 pg_catalog.obj_description(p.oid, 'pg_proc') as "Description"
FROM pg_catalog.pg_proc p
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
    LEFT JOIN pg_catalog.pg_language l ON l.oid = p.prolang
WHERE pg_catalog.pg_function_is_visible(p.oid)
     AND n.nspname <> 'pg_catalog'
     AND n.nspname <> 'information_schema'
ORDER BY 1, 2, 4;
**************************

In your case the would be what you want:

 pg_catalog.pg_get_function_result(p.oid) as "Result data type",