Thread: stored function data structures - difficulty

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.

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.

Hello

2011/11/22 J.V. <jvsrvcs@gmail.com>:
>
> 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.
>

psql has a nice featute, that can help with orientation in system catalog

if I need a query, that describe a some database object, I need to
know a adequate psql meta statement. You have to run psql with -E
param, and then psql shows a queries that was necessary for processing
a statement

[pavel@nemesis ~]$ psql -E postgres
psql (9.2devel)
Type "help" for help.

postgres=# \d a1
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(a1)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
c.reloftype = 0 THEN '' ELSE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '146989';
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '146989' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************

********* QUERY **********
SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,
i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
  pg_catalog.pg_get_constraintdef(con.oid, true), contype,
condeferrable, condeferred, c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND
conindid = i.indexrelid AND contype IN ('p','u','x'))
WHERE c.oid = '146989' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
**************************

********* QUERY **********
SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '146989' AND r.contype = 'f' ORDER BY 1;
**************************

********* QUERY **********
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '146989' AND c.contype = 'f' ORDER BY 1;
**************************

********* QUERY **********
SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = '146989' AND NOT t.tgisinternal
ORDER BY 1;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
'146989' ORDER BY inhseqno;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
'146989' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************

      Table "public.a1"
 Column │  Type   │ Modifiers
────────┼─────────┼───────────
 id     │ integer │ not null
 v      │ integer │
Indexes:
    "a1_pkey" PRIMARY KEY, btree (id)
Referenced by:
    TABLE "a2" CONSTRAINT "a2_id_fkey" FOREIGN KEY (id) REFERENCES a1(id)

postgres=# \d a2
********* QUERY **********
SELECT c.oid,
  n.nspname,
  c.relname
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname ~ '^(a2)$'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 2, 3;
**************************

********* QUERY **********
SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
c.reloftype = 0 THEN '' ELSE
c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
FROM pg_catalog.pg_class c
 LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
WHERE c.oid = '146994';
**************************

********* QUERY **********
SELECT a.attname,
  pg_catalog.format_type(a.atttypid, a.atttypmod),
  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
   FROM pg_catalog.pg_attrdef d
   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
  a.attnotnull, a.attnum,
  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
a.attcollation <> t.typcollation) AS attcollation,
  NULL AS indexdef,
  NULL AS attfdwoptions
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = '146994' AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum;
**************************

********* QUERY **********
SELECT conname,
  pg_catalog.pg_get_constraintdef(r.oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = '146994' AND r.contype = 'f' ORDER BY 1;
**************************

********* QUERY **********
SELECT conname, conrelid::pg_catalog.regclass,
  pg_catalog.pg_get_constraintdef(c.oid, true) as condef
FROM pg_catalog.pg_constraint c
WHERE c.confrelid = '146994' AND c.contype = 'f' ORDER BY 1;
**************************

********* QUERY **********
SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled
FROM pg_catalog.pg_trigger t
WHERE t.tgrelid = '146994' AND NOT t.tgisinternal
ORDER BY 1;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
'146994' ORDER BY inhseqno;
**************************

********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
'146994' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************

      Table "public.a2"
 Column │  Type   │ Modifiers
────────┼─────────┼───────────
 id     │ integer │
 v      │ integer │
Foreign-key constraints:
    "a2_id_fkey" FOREIGN KEY (id) REFERENCES a1(id)

Regards

Pavel Stehule





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

1)
What is "a1" ?

2)
Where did the queries below come from?

3)
What information does each query below provide?

On 11/21/2011 9:14 PM, Pavel Stehule wrote:
> Hello
>
> 2011/11/22 J.V.<jvsrvcs@gmail.com>:
>> 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.
>>
> psql has a nice featute, that can help with orientation in system catalog
>
> if I need a query, that describe a some database object, I need to
> know a adequate psql meta statement. You have to run psql with -E
> param, and then psql shows a queries that was necessary for processing
> a statement
>
> [pavel@nemesis ~]$ psql -E postgres
> psql (9.2devel)
> Type "help" for help.
>
> postgres=# \d a1
> ********* QUERY **********
> SELECT c.oid,
>    n.nspname,
>    c.relname
> FROM pg_catalog.pg_class c
>       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relname ~ '^(a1)$'
>    AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 2, 3;
> **************************
>
> ********* QUERY **********
> SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
> c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
> c.reloftype = 0 THEN '' ELSE
> c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
> FROM pg_catalog.pg_class c
>   LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
> WHERE c.oid = '146989';
> **************************
>
> ********* QUERY **********
> SELECT a.attname,
>    pg_catalog.format_type(a.atttypid, a.atttypmod),
>    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
>     FROM pg_catalog.pg_attrdef d
>     WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
>    a.attnotnull, a.attnum,
>    (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
>     WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
> a.attcollation<>  t.typcollation) AS attcollation,
>    NULL AS indexdef,
>    NULL AS attfdwoptions
> FROM pg_catalog.pg_attribute a
> WHERE a.attrelid = '146989' AND a.attnum>  0 AND NOT a.attisdropped
> ORDER BY a.attnum;
> **************************
>
> ********* QUERY **********
> SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,
> i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
>    pg_catalog.pg_get_constraintdef(con.oid, true), contype,
> condeferrable, condeferred, c2.reltablespace
> FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
>    LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND
> conindid = i.indexrelid AND contype IN ('p','u','x'))
> WHERE c.oid = '146989' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
> ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
> **************************
>
> ********* QUERY **********
> SELECT conname,
>    pg_catalog.pg_get_constraintdef(r.oid, true) as condef
> FROM pg_catalog.pg_constraint r
> WHERE r.conrelid = '146989' AND r.contype = 'f' ORDER BY 1;
> **************************
>
> ********* QUERY **********
> SELECT conname, conrelid::pg_catalog.regclass,
>    pg_catalog.pg_get_constraintdef(c.oid, true) as condef
> FROM pg_catalog.pg_constraint c
> WHERE c.confrelid = '146989' AND c.contype = 'f' ORDER BY 1;
> **************************
>
> ********* QUERY **********
> SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled
> FROM pg_catalog.pg_trigger t
> WHERE t.tgrelid = '146989' AND NOT t.tgisinternal
> ORDER BY 1;
> **************************
>
> ********* QUERY **********
> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
> pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
> '146989' ORDER BY inhseqno;
> **************************
>
> ********* QUERY **********
> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
> pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
> '146989' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
> **************************
>
>        Table "public.a1"
>   Column │  Type   │ Modifiers
> ────────┼─────────┼───────────
>   id     │ integer │ not null
>   v      │ integer │
> Indexes:
>      "a1_pkey" PRIMARY KEY, btree (id)
> Referenced by:
>      TABLE "a2" CONSTRAINT "a2_id_fkey" FOREIGN KEY (id) REFERENCES a1(id)
>
> postgres=# \d a2
> ********* QUERY **********
> SELECT c.oid,
>    n.nspname,
>    c.relname
> FROM pg_catalog.pg_class c
>       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relname ~ '^(a2)$'
>    AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 2, 3;
> **************************
>
> ********* QUERY **********
> SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
> c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
> c.reloftype = 0 THEN '' ELSE
> c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
> FROM pg_catalog.pg_class c
>   LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
> WHERE c.oid = '146994';
> **************************
>
> ********* QUERY **********
> SELECT a.attname,
>    pg_catalog.format_type(a.atttypid, a.atttypmod),
>    (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
>     FROM pg_catalog.pg_attrdef d
>     WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
>    a.attnotnull, a.attnum,
>    (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
>     WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
> a.attcollation<>  t.typcollation) AS attcollation,
>    NULL AS indexdef,
>    NULL AS attfdwoptions
> FROM pg_catalog.pg_attribute a
> WHERE a.attrelid = '146994' AND a.attnum>  0 AND NOT a.attisdropped
> ORDER BY a.attnum;
> **************************
>
> ********* QUERY **********
> SELECT conname,
>    pg_catalog.pg_get_constraintdef(r.oid, true) as condef
> FROM pg_catalog.pg_constraint r
> WHERE r.conrelid = '146994' AND r.contype = 'f' ORDER BY 1;
> **************************
>
> ********* QUERY **********
> SELECT conname, conrelid::pg_catalog.regclass,
>    pg_catalog.pg_get_constraintdef(c.oid, true) as condef
> FROM pg_catalog.pg_constraint c
> WHERE c.confrelid = '146994' AND c.contype = 'f' ORDER BY 1;
> **************************
>
> ********* QUERY **********
> SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled
> FROM pg_catalog.pg_trigger t
> WHERE t.tgrelid = '146994' AND NOT t.tgisinternal
> ORDER BY 1;
> **************************
>
> ********* QUERY **********
> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
> pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
> '146994' ORDER BY inhseqno;
> **************************
>
> ********* QUERY **********
> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
> pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
> '146994' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
> **************************
>
>        Table "public.a2"
>   Column │  Type   │ Modifiers
> ────────┼─────────┼───────────
>   id     │ integer │
>   v      │ integer │
> Foreign-key constraints:
>      "a2_id_fkey" FOREIGN KEY (id) REFERENCES a1(id)
>
> Regards
>
> Pavel Stehule
>
>
>
>
>
>> 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
>>

this is a ton of documentation to wade through, it literally would take weeks to go through, and many trials and errors, fiddling to get what I need (if I ever get that far).

Is there specific documentation / tutorial that will guide me on:

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

store this information in some data structure that I can iterate through? 

like I said before, given my deadlines, it would be much faster for me to simply hard code all of the data for 100 tables and put it in a data structure than figure out the query I need with the stack of documentation to sort through and find what I need.  I am simply looking for a data structure that can be used and a way to iterate through.  I just need to know if this is possible and an example.

On 11/21/2011 5:23 PM, Mike Blackwell wrote:

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

Hello

2011/11/29 J.V. <jvsrvcs@gmail.com>:
> 1)
> What is "a1" ?

a1 is table name
>
> 2)
> Where did the queries below come from?
>
These queries are used in "psql" console to ensure run backslash commands

> 3)
> What information does each query below provide?

columns names, column types and other

Regards

Pavel Stehule


>
> On 11/21/2011 9:14 PM, Pavel Stehule wrote:
>>
>> Hello
>>
>> 2011/11/22 J.V.<jvsrvcs@gmail.com>:
>>>
>>> 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.
>>>
>> psql has a nice featute, that can help with orientation in system catalog
>>
>> if I need a query, that describe a some database object, I need to
>> know a adequate psql meta statement. You have to run psql with -E
>> param, and then psql shows a queries that was necessary for processing
>> a statement
>>
>> [pavel@nemesis ~]$ psql -E postgres
>> psql (9.2devel)
>> Type "help" for help.
>>
>> postgres=# \d a1
>> ********* QUERY **********
>> SELECT c.oid,
>>   n.nspname,
>>   c.relname
>> FROM pg_catalog.pg_class c
>>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>> WHERE c.relname ~ '^(a1)$'
>>   AND pg_catalog.pg_table_is_visible(c.oid)
>> ORDER BY 2, 3;
>> **************************
>>
>> ********* QUERY **********
>> SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
>> c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
>> c.reloftype = 0 THEN '' ELSE
>> c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
>> FROM pg_catalog.pg_class c
>>  LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
>> WHERE c.oid = '146989';
>> **************************
>>
>> ********* QUERY **********
>> SELECT a.attname,
>>   pg_catalog.format_type(a.atttypid, a.atttypmod),
>>   (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
>>    FROM pg_catalog.pg_attrdef d
>>    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
>>   a.attnotnull, a.attnum,
>>   (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
>>    WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
>> a.attcollation<>  t.typcollation) AS attcollation,
>>   NULL AS indexdef,
>>   NULL AS attfdwoptions
>> FROM pg_catalog.pg_attribute a
>> WHERE a.attrelid = '146989' AND a.attnum>  0 AND NOT a.attisdropped
>> ORDER BY a.attnum;
>> **************************
>>
>> ********* QUERY **********
>> SELECT c2.relname, i.indisprimary, i.indisunique, i.indisclustered,
>> i.indisvalid, pg_catalog.pg_get_indexdef(i.indexrelid, 0, true),
>>   pg_catalog.pg_get_constraintdef(con.oid, true), contype,
>> condeferrable, condeferred, c2.reltablespace
>> FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
>>   LEFT JOIN pg_catalog.pg_constraint con ON (conrelid = i.indrelid AND
>> conindid = i.indexrelid AND contype IN ('p','u','x'))
>> WHERE c.oid = '146989' AND c.oid = i.indrelid AND i.indexrelid = c2.oid
>> ORDER BY i.indisprimary DESC, i.indisunique DESC, c2.relname;
>> **************************
>>
>> ********* QUERY **********
>> SELECT conname,
>>   pg_catalog.pg_get_constraintdef(r.oid, true) as condef
>> FROM pg_catalog.pg_constraint r
>> WHERE r.conrelid = '146989' AND r.contype = 'f' ORDER BY 1;
>> **************************
>>
>> ********* QUERY **********
>> SELECT conname, conrelid::pg_catalog.regclass,
>>   pg_catalog.pg_get_constraintdef(c.oid, true) as condef
>> FROM pg_catalog.pg_constraint c
>> WHERE c.confrelid = '146989' AND c.contype = 'f' ORDER BY 1;
>> **************************
>>
>> ********* QUERY **********
>> SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled
>> FROM pg_catalog.pg_trigger t
>> WHERE t.tgrelid = '146989' AND NOT t.tgisinternal
>> ORDER BY 1;
>> **************************
>>
>> ********* QUERY **********
>> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
>> pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
>> '146989' ORDER BY inhseqno;
>> **************************
>>
>> ********* QUERY **********
>> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
>> pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
>> '146989' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
>> **************************
>>
>>       Table "public.a1"
>>  Column │  Type   │ Modifiers
>> ────────┼─────────┼───────────
>>  id     │ integer │ not null
>>  v      │ integer │
>> Indexes:
>>     "a1_pkey" PRIMARY KEY, btree (id)
>> Referenced by:
>>     TABLE "a2" CONSTRAINT "a2_id_fkey" FOREIGN KEY (id) REFERENCES a1(id)
>>
>> postgres=# \d a2
>> ********* QUERY **********
>> SELECT c.oid,
>>   n.nspname,
>>   c.relname
>> FROM pg_catalog.pg_class c
>>      LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
>> WHERE c.relname ~ '^(a2)$'
>>   AND pg_catalog.pg_table_is_visible(c.oid)
>> ORDER BY 2, 3;
>> **************************
>>
>> ********* QUERY **********
>> SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules,
>> c.relhastriggers, c.relhasoids, '', c.reltablespace, CASE WHEN
>> c.reloftype = 0 THEN '' ELSE
>> c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence
>> FROM pg_catalog.pg_class c
>>  LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)
>> WHERE c.oid = '146994';
>> **************************
>>
>> ********* QUERY **********
>> SELECT a.attname,
>>   pg_catalog.format_type(a.atttypid, a.atttypmod),
>>   (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128)
>>    FROM pg_catalog.pg_attrdef d
>>    WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),
>>   a.attnotnull, a.attnum,
>>   (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t
>>    WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND
>> a.attcollation<>  t.typcollation) AS attcollation,
>>   NULL AS indexdef,
>>   NULL AS attfdwoptions
>> FROM pg_catalog.pg_attribute a
>> WHERE a.attrelid = '146994' AND a.attnum>  0 AND NOT a.attisdropped
>> ORDER BY a.attnum;
>> **************************
>>
>> ********* QUERY **********
>> SELECT conname,
>>   pg_catalog.pg_get_constraintdef(r.oid, true) as condef
>> FROM pg_catalog.pg_constraint r
>> WHERE r.conrelid = '146994' AND r.contype = 'f' ORDER BY 1;
>> **************************
>>
>> ********* QUERY **********
>> SELECT conname, conrelid::pg_catalog.regclass,
>>   pg_catalog.pg_get_constraintdef(c.oid, true) as condef
>> FROM pg_catalog.pg_constraint c
>> WHERE c.confrelid = '146994' AND c.contype = 'f' ORDER BY 1;
>> **************************
>>
>> ********* QUERY **********
>> SELECT t.tgname, pg_catalog.pg_get_triggerdef(t.oid, true), t.tgenabled
>> FROM pg_catalog.pg_trigger t
>> WHERE t.tgrelid = '146994' AND NOT t.tgisinternal
>> ORDER BY 1;
>> **************************
>>
>> ********* QUERY **********
>> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
>> pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid =
>> '146994' ORDER BY inhseqno;
>> **************************
>>
>> ********* QUERY **********
>> SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c,
>> pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent =
>> '146994' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
>> **************************
>>
>>       Table "public.a2"
>>  Column │  Type   │ Modifiers
>> ────────┼─────────┼───────────
>>  id     │ integer │
>>  v      │ integer │
>> Foreign-key constraints:
>>     "a2_id_fkey" FOREIGN KEY (id) REFERENCES a1(id)
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>>
>>
>>
>>> 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
>>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>