On Mon, Aug 14, 2017 at 2:46 PM, <stim...@comcast.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.