Beefy Boxes and Bandwidth Generously Provided by pair Networks
"be consistent"

Re: DBI:to sort tables according to their dependencies

by erix (Parson)
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]
[usemodperl]: someone has yet to make that future famous free http to https proxy written in perl and supported by ads, paypal, patreon, etc to make millions of dollars a minute!
[Veltro]: Why would this be fresh, I recon this is stuff that happens all the time (not Perl per se)
[Corion]: usemodperl: I'm sure you could even code that on the command line.
[usemodperl]: definitely

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (8)
As of 2018-06-24 17:05 GMT
Find Nodes?
    Voting Booth?
    Should cpanminus be part of the standard Perl release?

    Results (126 votes). Check out past polls.