Hi list,
since up to now information_schema is pretty 'severe' in giving info that
can be retrieved easily using '\d'or '\d table_name', I tried to write
some queries for sqlalchemy (the ORM for python) to make it work correctly.
I used "psql -E + \d" to spy how postgresql retrieved the information.
Before committing to the guy of sqlalchemy the result I'd like you to
review if what I'm doing is general enought. The query I attach are
working correctly in all situations I tested it but I know I'm not at all
a good tester...
The querie aim at finding:
0. list of tables
1. Primary keys of a table
2. Foreign keys
3. attributes (type, null/not null, default, length)
I'm also interested in understanding why, looking for the
column_definition psql searches using: ~ '^table_name$' rather
than = 'table_name'...?
Here are the Queries, thank for your attention
sandro
*:-)
The tables in schema :schema
SELECT c.relname as name,
n.nspname as schema,c.relkind,
u.usename as owner
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r')
AND n.nspname = :schema
AND pg_catalog.pg_table_is_visible(c.oid)
Primary keys:
SELECT attname FROM pg_attribute
WHERE attrelid = (
SELECT indexrelid FROM pg_index i, pg_class c
WHERE c.relname = :table_name AND c.oid = i.indrelid
AND i.indisprimary = 't' ) ;
Foreign Keys
SELECT conname, pg_catalog.pg_get_constraintdef(oid, true) as condef
FROM pg_catalog.pg_constraint r
WHERE r.conrelid = (
SELECT c.oid FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n
ON n.oid = c.relnamespace
WHERE c.relname = :table_name
AND pg_catalog.pg_table_is_visible(c.oid))
AND r.contype = 'f' ORDER BY 1
Attributes:
SELECT a.attname,
pg_catalog.format_type(a.atttypid, a.atttypmod),
(SELECT substring(d.adsrc for 128) FROM pg_catalog.pg_attrdef d
WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef)
AS DEFAULT,
a.attnotnull, a.attnum
FROM pg_catalog.pg_attribute a
WHERE a.attrelid = (
SELECT c.oid
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE pg_catalog.pg_table_is_visible(c.oid)
AND c.relname = :table_name AND c.relkind = 'r'
) AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum
--
Sandro Dentella *:-)
e-mail: sandro@e-den.it
http://www.tksql.org TkSQL Home page - My GPL work
