Refactoring PostgreSQL SProcs
with a moderate installation of PostgreSQL, we have amassed quite a few stored procedures / functions and types.
Now on a lower-level composite type (i.e. 3 types are created with it, and many functions refer to any of those types), one element of that type is of the wrong type (i.e. smallint instead of bigint), so the processing is identical, only the range is different.
- How to find out all types depending on type (pg_catalog.pg_type seems to be insufficient)?
- How can I find out all type-dependent functions (like arguments and locally localized vars)?
- Can I convert a composite type (maybe change smallint to bigint) without dropping / restoring one function depends on it?
- Is there any automation / tool / best practice for such refactoring?
I know his 4 questions in one, but atm this is kind of disappointing and any help would be appreciated! Many thanks!
a source to share
The pg_depend system directory contains some useful information about dependencies. You can find objects depending on specific types, for example:
select * from pg_depend where refclassid = 'pg_type'::regclass
and refobjid = 'information_schema.sql_identifier'::regtype;
There are objects that depend on the "information_schema.sql_identifier" type. As a result, classid is the OID of the directory - for example, for a user-specific column, 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 results as follows:
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, we (classid,objid,objsubid)
describe some object that depends on the object described (refclassid,refobjid)
.
a source to share