Beefy Boxes and Bandwidth Generously Provided by pair Networks
Your skill will accomplish
what the force of many cannot
 
PerlMonks  

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).


Comment on Re: DBI:to sort tables according to their dependencies
Download Code
Re^2: DBI:to sort tables according to their dependencies
by wrog (Monk) 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?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://944662]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (10)
As of 2014-09-02 00:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    My favorite cookbook is:










    Results (18 votes), past polls