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

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.

---

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",

---

This query will list the stored procedures with the types.

SELECT  proname, proargnames as arguments,
        oidvectortypes(proargtypes) as arguments_type, 
        t.typname as return_type,prosrc as source
FROM    pg_catalog.pg_namespace n
JOIN    pg_catalog.pg_proc p   ON    pronamespace = n.oid     
JOIN    pg_type t ON p.prorettype = t.oid  
WHERE   nspname = 'public'  

You can always filter by proname.

---

Just to get started:

SELECT 
  * 
FROM 
  pg_proc 
    JOIN pg_type ON pg_type.oid = ANY(proallargtypes) 
WHERE 
  proname = 'foo';

---

Are you looking for this?

SELECT proname, 
       pg_get_function_result(oid)
FROM pg_proc
WHERE proname = 'foo';

---

if the function returns a record then the type is not known until runtime, demonstrated with:

create or replace function func() returns record language plpgsql immutable as $$
declare 
  r record;
  q record;
begin
  select 10, 'hello' into r;
  select 11, 'hello', 'helloagain' into q;
  if random()>0.5 then
    return r;
  else
    return q;
  end if;
end;$$;

in other words, you can't know the type until after you call the function. Once you have called the function, you could dynamically determine information about the record by passing it into a C-language function as referenced here

---

Thanks for the help guys, I'm think JackPDouglas is correct and that since functions that return record sets can be polymorphic that there's no way to find out the return type definition.

However here's the SQL I was looking for to get the definition of a function that returns a composite type:

SELECT t.typname, attname, a.typname
from pg_type t
JOIN pg_class on (reltype = t.oid)
JOIN pg_attribute on (attrelid = pg_class.oid)
JOIN pg_type a on (atttypid = a.oid)
WHERE t.typname = (
    SELECT t.typname
    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
);