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