Обсуждение: 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@e-den.it
http://www.tksql.org                    TkSQL Home page - My GPL work