How to bypass perm pb on information schema. Request of review

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(at)e-den(dot)it
http://www.tksql.org TkSQL Home page - My GPL work