Refactoring PostgreSQL SProcs

Question

Hi,

with a moderate PostgreSQL installation we accumulated quite a few stored procedures/functions and types.

Now in the lowest level composite type (i.e. 3 types are built with it and a myriad functions reference any of those types) one element of the type is of wrong type (i.e. smallint instead of bigint), thus handling it is identical, only the range is different.

1. How do I know all types depending on a type (pg_catalog.pg_type seems insufficient)?
2. How can I know all functions depending on a type (as arguments and locally scoped vars)?
3. Can I refactore a composite type (maybe change smallint to bigint) without dropping/rebuilding every single function depending on it?
4. Is there any kind of automation/tool/best practice for such a refactoring?

I know its 4 questions in one, but atm this is kind of frustrating and any help would be appreciated! Many Thanks!

Answer 1

The system catalogue "pg_depend" contains some useful dependency information. You can find objects depending on particular types a bit like this:

select * from pg_depend where refclassid = 'pg_type'::regclass
    and refobjid = 'information_schema.sql_identifier'::regtype;

This finds objects dependent on the "information_schema.sql_identifier" type. In the result, classid is the OID of a catalogue- for instance, for a column depending on a user type, classid is 'pg_class'::regclass, objid is the OID of the pg_class row, and objsubid is the attnum value from pg_attribute, so for this case you can format the results like this:

select objid::regclass, attname from pg_depend
    join pg_attribute on pg_attribute.attrelid = pg_depend.objid and pg_attribute.attnum = pg_depend.objsubid
where refclassid = 'pg_type'::regclass and refobjid = 'information_schema.sql_identifier'::regtype
    and classid = 'pg_class'::regclass
limit 10

So in pg_depend, (classid,objid,objsubid) describe some object that depends on the object described by (refclassid,refobjid).