是否可以从 Postgres 函数中发现列类型？

Question

我正在开发一个实用程序，该实用程序使用模板针对 Postgres 数据库生成数据访问层。作为其中的一部分，我试图动态发现存储过程的返回类型。在返回单个标准类型的简单情况下，这很容易，但是当它返回用户定义的类型时，我很困难。

如果有人可以提供必要的 SQL 来返回此数据，我将不胜感激。

谢谢 马克，

我很欣赏到目前为止我得到的答案，这些答案实际上归结为以下 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;

这将返回返回类型的名称。然而，我仍然需要将类型分解为当它返回用户定义类型时组成它的属性。

在函数返回记录的情况下，我认为除了调用函数并检查其返回值之外，没有任何方法可以发现其返回结构。

Answer 1

psql 元命令是查找信息模式内容的简单快捷方式。

尝试 test=# \d? 查找内省信息。

然后 psql -E -c '\df' 将显示 show function 命令后面的 sql：

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;
**************************

在您的情况下，这将是您想要的：

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

Answer 2

此查询将列出带有类型的存储过程。

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'  

您始终可以按昵称进行过滤。

Answer 3

刚开始：

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

Answer 4

您在找这个吗？

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

Answer 5

如果函数返回记录，则直到运行时才知道类型，如下所示：

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;$;

换句话说，在调用之后之前，您无法知道类型函数。调用该函数后，您可以将有关记录的信息传递到参考此处

Answer 6

感谢大家的帮助，我认为 JackPDouglas 是正确的，并且由于返回记录集的函数可以是多态的，因此无法找出返回类型定义。

然而，这是我正在寻找的用于获取返回复合类型的函数的定义的 SQL：

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
);