Postgres table relations

Wow so sometimes I know I’m learning things again when I can feel a sensation in my head almost like the feeling my little nothing bicep gets from lifting my coffee mug. Postgres’ internal database structure is fucked up and I’ll let you discover the relationship between the two data sets.

The goal:

I want to make a pretty basic postgres query builder for my job. We always have to make simple, custom reports for clients.  They are basic, but take up our time.  This query builder has to be very user friendly and allow them to create their own reports.

It’s a pretty simple task(none of these reports are expected to have any logic beyond basic where clauses, ie – no joins), but having to look through the postgres documentation and remember that pg_class.oid = pg_constraint.conkey unless its the foreign relation whence you use confkey unless a bunch of other stuff also from pg.att and pg_constraint blah just isn’t what I was designed to keep in my head.

My main problem is this:

I want users to be able to say “Show me all Terminal Names and Location Names for Location #7″, but I can’t expect the users to know about a Terminal table and a Location table, and much less which fields relate those two tables.

Well, setting the location=7 is easy, but I can’t ask users to manually specify Terminal.Location = Location.id && Location.id = 7. The user just knows to say “Location 7″. So I relate the tables through foreign keys. I look at all the tables involved (terminals and locations in this case) and find out how they are related. After some reading and experimenting,  this query relates tables to one another by looking through the foreign keys.

 MySQL |  copy code |? 
1
SELECT pc1.relname as ltable, pga2.attname as lcolumn, pc2.relname as rtable, pga1.attname as rcolumn
2
                FROM pg_class pc1, pg_class pc2, pg_constraint, pg_attribute pga1, pg_attribute pga2
3
                WHERE pc1.relname in ({$sourceList}) and pg_constraint.conrelid = pc1.oid
4
                AND pc2.oid = pg_constraint.confrelid
5
                AND pc2.relname in ({$destList})
6
                AND pga1.attnum = pg_constraint.confkey[1]
7
                AND pga1.attrelid = pc2.oid
8
                AND pga2.attnum = pg_constraint.conkey[1]
9
                AND pga2.attrelid = pc1.oid

oh noes my code plugin has MySQL code but no Postgres code… no time keep going.

The ‘sourceList’ represents the table(s) you want to relate to ‘destList’. If the two lists are equal(containing the same tables) then this query just shows all the links to each other:

 MySQL |  copy code |? 
1
    ltable     |    lcolumn   |        rtable         |   rcolumn
2
--------------+---------------+-------------------+-------------
3
Terminal | location_id | Locations | location_id

Now I can manually add a WHERE clause to relate Terminal.location_id to Locations.location_id.

But now it gets fun! We have routes!

Routes HasMany Locations. Locations HasMany Terminals. Terminals have no direct relation to Routes.

So when a user says “Show me Terminal Names for Route #2″, I don’t have a direct relation between the two tables! The solution is obvious, get all foreign keys for the Terminals table. One will relate to the Locations table. Get all foreign keys for the Locations table, one will relate to Route. Then I can create the link manually.

This probably should be done recursively, but I don’t do it that way for a couple reasons.

  1. Go do it yourself, I’m hungry and going to lunch.
  2. Second though is I have no idea how all the relations in our database work out. I could end up in a loop and not really get out of it, it would take a lot of time, etc.

So I just go one level. If I can’t link your two tables through a single third table, then you’re SOL. I run this query:

 MySQL |  copy code |? 
1
SELECT pc1.relname as ltable, pga2.attname as lcolumn, pc2.relname as rtable, pga1.attname as rcolumn
2
                        FROM pg_class pc1, pg_class pc2, pg_constraint, pg_attribute pga1, pg_attribute pga2
3
                        WHERE pc1.relname = '{$table}' and pg_constraint.conrelid = pc1.oid
4
                        AND pc2.relkind = 'r' AND pc2.oid = pg_constraint.confrelid
5
                        AND pga1.attnum = pg_constraint.confkey[1]
6
                        AND pga1.attrelid = pc2.oid
7
                        AND pga2.attnum = pg_constraint.conkey[1]
8
                        AND pga2.attrelid = pc1.oid

This gives me all the foreign keys for a table, say Terminal. Then I run the first query on all the foreign tables(rtable from the first query) to see if I can link to the desired table (Route). If I can awsome! If not, move on.

That concludes my adventure is dealing with the internal Postgres information schema.  I hope this will save me a couple hours of trouble further down the road.  I did move on to pl/pgsql, and that is a huge pain in the neck too… but maybe for another day.

Posted Thursday, November 12th, 2009 under postgres.

Comments are closed.