I have a type defined as:
CREATE TYPE A AS
(header text,
body text,
id numeric);
ALTER TYPE A
I want to add more atrributes to it, but I don't know how to check where this type is being used.
How do I get a list of which function uses it as return type?
for example if I have fuction:
CREATE OR REPLACE FUNCTION X(a integer, b integer, c integer, d citext)
RETURNS A AS .....
The query will list X in it's result.
Example setup:
create type my_type as (id int, str text);
create table my_table (val my_type);
create function my_fun()
returns my_type language sql as $$
select (1, '1')::my_type;
$$;
Use pg_depend to find all references to my_type:
select deptype, pg_describe_object(classid, objid, objsubid)
from pg_depend d
join pg_class c on refobjid = reltype
where c.oid = 'my_type'::regclass
deptype | pg_describe_object
---------+---------------------------
i | composite type my_type
i | type my_type[]
n | table my_table column val
n | function my_fun()
(4 rows)
you can run following query:
postgres=# select oid::regprocedure from pg_proc
where prorettype = 'A'::regtype;
┌─────────────────────────────────┐
│ oid │
├─────────────────────────────────┤
│ x(integer,integer,integer,text) │
└─────────────────────────────────┘
(1 row)
All informations about functions are stored in table pg_proc.
