3

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
avi
  • 1,626
  • 3
  • 27
  • 45

2 Answers2

5

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:

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

As mentioned by Erwin Brandstetter, this only works for functions directly returning the data type.

SELECT * FROM information_schema.routines r 
WHERE r.type_udt_name = 'YOUR_DATA_TYPE' ORDER BY r.routine_name;
Community
  • 1
  • 1
zb226
  • 9,586
  • 6
  • 49
  • 79
  • it doesn't work... I puted my type name in ur query and it gives 0 rows... it doesn't make sence as I see the function that uses this type. – avi Nov 05 '15 at 14:16
  • 1
    @avi: Can you try `SELECT * FROM information_schema.routines r WHERE r.type_udt_name = 'YOUR_DATA_TYPE' ORDER BY r.routine_name;`? – zb226 Nov 05 '15 at 14:31
  • @a_horse_with_no_name i copy paste the type name from the function it is used... so there is no issue of lowercase – avi Nov 05 '15 at 14:35
  • @a_horse_with_no_name: I used upper-case just to emphasize where a replacement is necessary – zb226 Nov 05 '15 at 14:37
  • 1
    This is not going to work for functions returning `TABLE(...)` or `SETOF record` *containing* the type in question. – Erwin Brandstetter Nov 05 '15 at 14:43