Re: stored function data structures - difficulty - Mailing list 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
>>