CODEWITHSUNDEEP
sqlpostgresql
SMW
SMW

Reputation: 339

How to check where Type is used in Postgresql?

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.

Upvotes: 1

Views: 1295

Answers (2)

klin
klin

Reputation: 121474

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)

Upvotes: 2

Pavel Stehule
Pavel Stehule

Reputation: 45750

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.

Upvotes: 0

Related Questions