PostgreSQL - 我怎么知道哪个函数使用了一个类型？

I have a type defined in my server:

我的服务器中定义了一个类型：

CREATE TYPE typ_gbom AS
(
   ab citext,
   id   integer,
   name citext,
   user citext,
   comment citext
);
ALTER TYPE typ_gbom 
  OWNER TO postgres;

I want to find all functions that return this type.

我想找到所有返回此类型的函数。

Meaning all function that has:

意味着所有具有以下功能：

CREATE OR REPLACE FUNCTION a()
  RETURNS setof typ_gbom AS

要么

CREATE OR REPLACE FUNCTION a()
  RETURNS typ_gbom AS

I tried:

我试过了：

select * from pg_proc where prosrc ilike '%typ_gbom%'

But this is not working, this is looking only in the function code itself and not in it's structure.

但这不起作用，这只是在功能代码本身，而不是在它的结构中。

How can I find if this type is being used?

如何查找是否使用此类型？

2 个解决方案

#1

You can use pg_depend for this:

您可以使用pg_depend：

select n.nspname as function_schema, p.proname as function_name
from pg_proc p 
  join pg_namespace n on n.oid = p.pronamespace 
  join pg_depend d on d.objid = p.oid and d.classid = 'pg_proc'::regclass 
  join pg_type typ on typ.oid = d.refobjid 
  join pg_namespace ts on ts.oid = typ.typnamespace 
where ts.nspname = 'public' --<< change to your schema name 
  and typ.typname = 'typ_gbom'

#2

there are a few functions that return function metadata. we can use them to extract the desired info

有一些函数返回函数元数据。我们可以使用它们来提取所需的信息

SELECT 
  p.proname as func_name,
  pg_catalog.pg_get_function_result(p.oid) as result_type,
  pg_catalog.pg_get_function_arguments(p.oid) as argument_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 func_type
FROM pg_catalog.pg_proc p
WHERE pg_catalog.pg_function_is_visible(p.oid)
  AND pg_catalog.pg_get_function_result(p.oid) IN ('typ_gbom', 'SETOF typ_gbom')

#1