As mentioned in Lessons Learned Packaging PostGIS extensions, I am working on PostGIS 2.0.0 extension packaging. One of the things I wanted to know was what objects, types, functions etc were installed by my extension. The new packaging system allows for cataloging this relatively easily, but I couldn't find a function or view for this and didn't see one mentioned in the manual, so I created this query which seems to work pretty well as far as I can tell. The basic idea being that any object that an extension depends on that is not an extension is part of the extension package.
SELECT c.relname As item_type, COALESCE(proc.proname,typ.typname, cd.relname, op.oprname, 'CAST(' || cs.typname || ' AS ' || ct.typname || ') ', opcname, opfname) As item_name, COALESCE(proc.proisagg,false) As is_agg, oidvectortypes(proc.proargtypes) As arg_types FROM pg_depend As d INNER JOIN pg_extension As e ON d.refobjid = e.oid INNER JOIN pg_class As c ON c.oid = d.classid LEFT JOIN pg_proc AS proc ON proc.oid = d.objid LEFT JOIN pg_type AS typ ON typ.oid = d.objid LEFT JOIN pg_class As cd ON cd.oid = d.objid LEFT JOIN pg_operator As op ON op.oid = d.objid LEFT JOIN pg_cast AS ca ON ca.oid = d.objid LEFT JOIN pg_type AS cs ON ca.castsource = cs.oid LEFT JOIN pg_type AS ct ON ca.casttarget = ct.oid LEFT JOIN pg_opclass As oc ON oc.oid = d.objid LEFT JOIN pg_opfamily As ofa ON ofa.oid = d.objid WHERE d.deptype = 'e' and e.extname = 'postgis' ORDER BY item_type, item_name;
The output looks like:
item_type | item_name | is_agg |arg_types ------------+----------------------------------+--------+----------- pg_cast | CAST(box2d AS box3d) | f | pg_cast | CAST(box2d AS geometry) | f | pg_cast | CAST(box3d AS box) | f | pg_cast | CAST(box3d AS box2d) | f | pg_cast | CAST(box3d AS geometry) | f | : pg_opclass | btree_geography_ops | f | pg_opclass | btree_geometry_ops | f | pg_opclass | gist_geography_ops | f | pg_opclass | gist_geometry_ops_2d | f | pg_opclass | gist_geometry_ops_nd | f | : pg_opfamily | btree_geography_ops | f | pg_opfamily | btree_geometry_ops | f | pg_opfamily | gist_geography_ops | f | pg_opfamily | gist_geometry_ops_2d | f | pg_opfamily | gist_geometry_ops_nd | f | : pg_operator | && | f | pg_operator | &&& | f | : pg_proc | postgis_addbbox | f | geometry : pg_type | spheroid | f | pg_type | summarystats | f | : pg_type | wktgeomval | f | (984 rows)