Take a look at http://www.postgresql.org/docs/9.1/interactive/information-schema.html and http://www.postgresql.org/docs/9.1/interactive/catalogs.html. I think you'll find what you need. The former is relatively stable between releases, while the latter has more detail but is subject to change.
I have use the system catalogs for several one time projects related to foreign keys, including checking which fks have associated indexes defined. __________________________________________________________________________________ *Mike Blackwell | Technical Analyst, Distribution Services/Rollout Management | RR Donnelley* 1750 Wallace Ave | St Charles, IL 60174-3401 Office: 630.313.7818 mike.blackw...@rrd.com http://www.rrdonnelley.com <http://www.rrdonnelley.com/> * <mike.blackw...@rrd.com>* On Mon, Nov 21, 2011 at 17:30, J.V. <jvsr...@gmail.com> wrote: > > I cannot find a way to programatically: > 1. Given a table name, find all foreign key fields in the given table > by field name (column name) > 2. Given a single foreign key field name, programatically look up the > corresponding reference table name and the reference primary key field > > so have thought of simply hard coding this (for 100 tables). > > What Kind of data structure could I use that is available to me that would > hold as the key the table name and from there be able to iterate through > and get all foreign keys by field name and from there another inner loop > that would give me another key/value pair of the table name (key) and the > primary key (value) that corresponds to that foreign key? > > I want to hard code all of this information into a data structure and > iterate through at some point in another function. > > Instead of discovering this programatically, I can manually look at each > table / schema diagram and hard code it, but I really need one super > structure that will hold as keys every table in the schema and be able to > drill down that that tables foreign keys and from there further drill down > to get the table name, primary key field in that table. > > I have seen a number of structures that might work, but cannot find an > example on how to actually use for what I need to do. If you do have an > idea of a structure, it would be great and awesome if I could be pointed to > an actual working example that I could test in a sandbox first to > understand how it works. > > thanks > > > J.V. > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/**mailpref/pgsql-general<http://www.postgresql.org/mailpref/pgsql-general> >
