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

DBI:to sort tables according to their dependencies

by ansh batra (Friar)
on Dec 21, 2011 at 06:48 UTC ( #944543=perlquestion: print w/replies, xml ) Need Help??
ansh batra has asked for the wisdom of the Perl Monks concerning the following question:

Hi Monks

i have a method which return the list of tables present in a schema, now what I need is to sort this list according to their dependencies i.e if x have foreign key which uses y then the list will have x before y.(where x and y are the tables in the schema).

i am using select tablename from pg_tables where schemaname='schemaname' command to get the list of tables
please help

Replies are listed 'Best First'.
Re: DBI:to sort tables according to their dependencies
by moritz (Cardinal) on Dec 21, 2011 at 08:39 UTC

      thanks marto .
      can you suggest me any way of getting the dependencies also

        I'm sure you can search the WWW. "postgres list foreign keys" proved successful for me. (I'm not allowed to link to external sites as anonymous)

        Furthermore, you can run psql -E and use the backslash-functions (e.g. \d) and watch the SQL it issues to figure out what fetches the foreign keys.

Re: DBI:to sort tables according to their dependencies
by erix (Parson) on Dec 21, 2011 at 16:43 UTC

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

      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: perlquestion [id://944543]
Approved by Corion
Front-paged by Corion
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others meditating upon the Monastery: (9)
As of 2018-07-23 13:55 GMT
Find Nodes?
    Voting Booth?
    It has been suggested to rename Perl 6 in order to boost its marketing potential. Which name would you prefer?

    Results (468 votes). Check out past polls.