Re: Queries for Diagramming Schema Keys

On Mon, Aug 14, 2017 at 2:46 PM, <stimits(at)comcast(dot)net> wrote:

> Hi,
>
> I'm trying to write an application (using libpqxx/C++) which creates
> graphical images of large and complex relations between tables (basically
> an SVG image with hot spots for drilling down on details). I need to
> essentially create icons of tables and their columns, and then draw
> interconnecting lines where foreign keys are involved, and to distinctly
> label primary keys, foreign keys, and non-key columns. Eventually this will
> have knowledge of an XML file loading scheme and be able to reverse
> engineer the required load order (imagine approximately 1,000 tables with
> many foreign keys and file loads which may take hours for each failure to
> load). I need some advice on using ANSI/information_schema queries to
> accomplish this.
>
> Thus I have this query to list all tables:
>
> SELECT table_name
> FROM information_schema.tables
> WHERE table_schema='public'
> AND table_type='BASE TABLE';
>
>
> ...this seems to work ok. The next query is to find all foreign
> keys...this seems to sort of work, but may have issues:
>
> SELECT
> tc.table_name AS local_table,
> kcu.column_name AS key_column,
> ccu.table_name AS fk_table,
> ccu.column_name AS fk_column
> FROM
> information_schema.table_constraints AS tc
> JOIN information_schema.key_column_usage AS kcu
> ON tc.constraint_name = kcu.constraint_name
> JOIN information_schema.constraint_column_usage AS ccu
> ON ccu.constraint_name = tc.constraint_name
> WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
> IN (SELECT table_name
> FROM information_schema.tables
> WHERE table_schema='public'
> AND table_type='BASE TABLE');
>
>
> This is my query to find all primary keys which are not foreign keys, and
> this definitely is not 100% correct:
> SELECT DISTINCT
> tc.table_name AS local_table,
> kcu.column_name AS key_column
> FROM
> information_schema.table_constraints AS tc
> JOIN information_schema.key_column_usage AS kcu
> ON tc.constraint_name = kcu.constraint_name
> JOIN information_schema.constraint_column_usage AS ccu
> ON ccu.constraint_name = tc.constraint_name
> WHERE constraint_type = 'PRIMARY KEY' AND tc.table_name
> IN (SELECT table_name
> FROM information_schema.tables
> WHERE table_schema='public'
> AND table_type='BASE TABLE')
> AND (tc.table_name, kcu.column_name)
> NOT IN (
> SELECT
> tc.table_name,
> kcu.column_name
> FROM
> information_schema.table_constraints AS tc
> JOIN information_schema.key_column_usage AS kcu
> ON tc.constraint_name = kcu.constraint_name
> JOIN information_schema.constraint_column_usage AS ccu
> ON ccu.constraint_name = tc.constraint_name
> WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name
> IN (SELECT table_name
> FROM information_schema.tables
> WHERE table_schema='public'
> AND table_type='BASE TABLE')
> )
> ORDER BY local_table, key_column;
>
> I am completely at a loss how I would query for all columns which are
> neither primary nor foreign keys. Would anyone have a suggestion for
> something like this:
> SELECT table_name, non_key_column
> FROM ...
> WHERE ...
> ORDER BY table_name, non_key_column
>
> Any advice on any of the queries would be appreciated!
>
> Thanks!
>

>I am completely at a loss how I would query for all columns

*Does this help?*

*-- TABLES AND COLUMNS*

*SELECT c.table_schema as schema, c.table_name as table,
c.ordinal_position as order, c.column_name as column, CASE WHEN
c.data_type IN ('character', 'varchar') THEN c.data_type || '(' ||
c.character_maximum_length || ')' WHEN TRIM(c.data_type) IN
('numeric') THEN c.data_type || '(' || c.numeric_precision_radix ||
',' *

* || c.numeric_scale || ')' ELSE c.data_type
END, c.is_nullable as null, col_description(t.oid,
c.ordinal_position) as comment FROM information_schema.columns c JOIN
pg_class t ON (t.relname = c.table_name) WHERE table_schema = 'public'
AND c.table_name = 'album'ORDER BY 1, 2, 3;*

*-- TABLES AND PKEYS*

*SELECT n.nspname, t.relname as table, c.conname as pk_name
FROM pg_class t JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype
= 'p') JOIN pg_namespace n ON (n.oid = t.relnamespace) WHERE relkind =
'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE 'sql_%'
ORDER BY n.nspname, t.relname, c.conname;*

*-- TABLES and FKEYS*

*SELECT n.nspname as schema, t.relname as table, c.conname as
fk_name FROM pg_class t JOIN pg_namespace n ON n.oid = t.relnamespace
JOIN pg_constraint c ON ( c.conrelid = t.OID AND c.contype = 'f') WHERE
relkind = 'r' AND t.relname NOT LIKE 'pg_%' AND t.relname NOT LIKE
'sql_%' ORDER BY n.nspname, t.relname, c.conname;*
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.