Re: stored function data structures - difficulty

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(at)gmail(dot)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(at)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(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>