Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl: the Markov chain saw

Re: DBI:to sort tables according to their dependencies

by erix (Vicar)
on Dec 21, 2011 at 16:43 UTC ( #944662=note: print w/replies, xml ) Need Help??

in reply to DBI:to sort tables according to their dependencies

Here is an example using pg_depend, a postgres catalog:

select classid::regclass as "depender object class" , case classid when 'pg_class'::regclass then objid::regclass::text when 'pg_type'::regclass then objid::regtype::text when 'pg_proc'::regclass then objid::regprocedure::text else objid::text end as "depender object identity" , objsubid , refclassid::regclass as "referenced object class" , case refclassid when 'pg_class'::regclass then refobjid::regclass::text when 'pg_type'::regclass then refobjid::regtype::text when 'pg_proc'::regclass then refobjid::regprocedure::text else refobjid::text end as "referenced object identity" , refobjsubid , case deptype when 'p' then 'pinned' when 'i' then 'internal' when 'a' then 'automatic' when 'n' then 'normal' end as "dependency type" from pg_catalog.pg_depend -- exclude dependencies on system objects: where objid >= 16384 or refobjid >= 16384 ;

That code comes from the postgresql wiki, but see also the pg_depend page, and for catalog stuff generally, internals (includes dictionary tables).

Replies are listed 'Best First'.
Re^2: DBI:to sort tables according to their dependencies
by wrog (Friar) on Dec 26, 2011 at 18:39 UTC

    As I understand it, pg_depend contains all links between all database objects (tables, columns, sequences, indexes, blobs, etc...); it gets used by the server to decide what all goes away when you do DROP TABLE/whatever. There may be Rather a Lot of Filtering to do to get it down to just foreign-key links between tables.

    An alternative would be to start from the list of constraints in pg_constraint (link is for version 8.4; adjust accordingly if you're using some other version of postgresql)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://944662]
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others about the Monastery: (4)
As of 2017-02-26 00:33 GMT
Find Nodes?
    Voting Booth?
    Before electricity was invented, what was the Electric Eel called?

    Results (369 votes). Check out past polls.