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

Поиск
Список
Период
Сортировка
От Sandro Dentella
Тема How to bypass perm pb on information schema. Request of review
Дата
Msg-id 20060704183014.GA8405@casa.e-den.it
обсуждение исходный текст
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: "Rivera Alejandro"
Дата:
Сообщение: Postgres & CODA
Следующее
От: "Ian Harding"
Дата:
Сообщение: Re: pgsql user change to postgres