10

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.

Improve this question
1
  • I'm creating an open source library that might suit your purpose precisely. Among other features, standardised access to stored procedures is granted through source code generation. Check out some examples here: sourceforge.net/apps/trac/jooq/wiki/Examples. I'm currently adding UDT support for stored procedures. This will be included in the next release, some time this week. Any feedback is welcome! Commented Jan 10, 2011 at 21:38

6 Answers 6

Reset to default
4

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",
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

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.

Improve this answer

Comments

1

Just to get started:

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

3 Comments

My interpretation of the documentation is that the proallargtypes is for the arguments used to call the function not what it returns. Plus when I execute the query on my installation (9.0.1) proallargtypes has no value. So I'm afraid it doesn't get my too far
Check postgresql.org/docs/9.0/interactive/catalog-pg-proc.html maybe you have to make a combination with proargmodes.
proargmodes again is for arguments. prorettype will give me the oid if the function returns a user defined type (I'll still have to figure out how to get the definition of this type) but that doesn't help me if the function returns a record.
1

Are you looking for this?

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

Comments

1

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

Improve this answer

Comments

1

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
);
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.