1

I am using this query to find all foreign key relationships:

 SELECT con.relname AS child_table,
    att2.attname AS child_column,
    cl.relname AS parent_table,
    att.attname AS parent_column
   FROM ( 
       SELECT 
         unnest(con1.conkey) AS parent,
         unnest(con1.confkey) AS child,
         con1.confrelid,
         con1.conrelid,
         cl_1.relname
       FROM 
         pg_class cl_1,
         pg_namespace ns,
         pg_constraint con1
       WHERE 
         con1.contype = 'f'::"char" 
         AND cl_1.relnamespace = ns.oid 
         AND con1.conrelid = cl_1.oid
  ) con,
    pg_attribute att,
    pg_class cl,
    pg_attribute att2
  WHERE 
    att.attrelid = con.confrelid 
    AND att.attnum = con.child 
    AND cl.oid = con.confrelid
    AND att2.attrelid = con.conrelid 
    AND att2.attnum = con.parent

For any number of tables connected by foreign keys, how can only all of the relevant relationships be selected?

Take for example this relationship tree:

progenitor
    grandparent_a
        parent_a_1
        parent_a_2
    grandparent_b
        parent_b_1
        parent_b_2

That is a simple example, and my trees have many many layers, but I'd like, from an arbitrary amount of tables, be able to get all of the linking foreign key relationships.

If I wanted to select data from both parent_a_1 and parent_a_2, the two relationships to grandparent_a would be selected.

If I wanted to select data from both parent_b_1 and parent_a_1, all relationships from both to progenitor would be selected.

I can do this when all tables are immediately connected, but I can't conceptualize how to connect the gaps.

  • What do you mean by "potentially gapped"? – Craig Ringer Oct 1 '14 at 3:18
  • You're trying to find all connected edges in a graph, right? i.e. the set of tables that have direct or indirect FK relationships to each other, and all the relationships that build that set. – Craig Ringer Oct 1 '14 at 3:21
  • @CraigRinger Yes, to your last comment. I will add more detail in an edit. Thank you for your help. – Jim Bob Oct 1 '14 at 13:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.