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.Blackwell@rrd.com
http://www.rrdonnelley.com
On Mon, Nov 21, 2011 at 17:30, J.V.
<jvsrvcs@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
