...snip...
For all tables and columns I get no output at all. Basically I need all tables
and columns for which the column is neither a regular primary key, nor a
foreign key. This will be marked as an attribute in the diagramming program.
Your query for primary keys which are not foreign keys seems to be magic :)
I'll have to dig through some of the tables (nearly 1000 of them) to validate,
but it seems to work with much less complication than my original query (and I
think my original had issues...I forgot to mention that I had been relying on
the columns which implemented values which foreign keys had referenced were
assumed to have the same column names...mostly this had been true, but not
always, so my original approach was a guaranteed failure).
For foreign keys I need a row with both the table and column which is a foreign
key, and the specific table and column it maps to. My mapping program will be
adding a double-linked list among keys for validation and for interactive use
of an SVG image being produced.
So I'm still looking for
"original_table.fk_column->implementing_table.implementing_column", plus the
list of "table.column" where "column" is not a key.
>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 DavidsonI reserve the right to fantasize. Whether or not you wish to
share my fantasy is entirely up to you.
