How to check which function uses a type?

Question

I have a type which I'd like to change but I don't know who else is using it.

How can I check for all functions that return this type?

Answer 1

You can find all dependencies in the system catalog pg_depend.

This returns all functions depending on the type. I.e. not only those with the type in the RETURNS clause, but also those with the type as function parameter:

SELECT objid::regproc                            AS function_name
     , pg_get_functiondef(objid)                 AS function_definition
     , pg_get_function_identity_arguments(objid) AS function_args
     , pg_get_function_result(objid)             AS function_returns
FROM   pg_depend
WHERE  refclassid = 'pg_type'::regclass
AND    refobjid   = 'my_type'::regtype    -- insert your type name here
AND    classid    = 'pg_proc'::regclass;  -- only find functions

This also works for table functions:

...
RETURNS TABLE (foo my_type, bar int)

Using system catalog information functions.

There may be other dependencies (not to functions). Remove the last WHERE condition from my query to test (and adapt the SELECT list, obviously).

And there is still the possibility of the type being used explicitly (in a cast for instance) in queries in the function body or in dynamic SQL. You can only identify such use cases by parsing the text of the function body. There are no explicit dependencies registered in the system.

Related:

• How to get function parameter lists (so I can drop a function)
• DROP FUNCTION without knowing the number/type of parameters?