PostgreSQL :: Запрос на извлечение списка таблиц и полей

Предлагаю postgresql функцию, позволяющую получить атрибуты всех полей и ключей из любых таблиц сервера.

Начну с примера. Допустим надо вывести атрибуты таблицы fos_user:

# select * from return_ro_tables_info() where tab_name='fos_user';

 tab_name | attnum |         field         |   type    | isnotnull | pri |           default_adsrc           |     comment
----------+--------+-----------------------+-----------+-----------+-----+-----------------------------------+-----------------
 fos_user |      1 | id                    | int8      | t         | t   |                                   |
 fos_user |      2 | username              | varchar   | t         |     |                                   |
 fos_user |      3 | username_canonical    | varchar   | t         |     |                                   |
 fos_user |      4 | email                 | varchar   | t         |     |                                   |
 fos_user |      5 | email_canonical       | varchar   | t         |     |                                   |
 fos_user |      6 | enabled               | bool      | t         |     |                                   |
 fos_user |      7 | salt                  | varchar   | t         |     |                                   |
 fos_user |      8 | password              | varchar   | t         |     |                                   |
 fos_user |      9 | last_login            | timestamp | f         |     | NULL::timestamp without time zone |
 fos_user |     10 | locked                | bool      | t         |     |                                   |
 fos_user |     11 | expired               | bool      | t         |     |                                   |
 fos_user |     12 | expires_at            | timestamp | f         |     | NULL::timestamp without time zone |
 fos_user |     13 | confirmation_token    | varchar   | f         |     | NULL::character varying           |
 fos_user |     14 | password_requested_at | timestamp | f         |     | NULL::timestamp without time zone |
 fos_user |     15 | roles                 | text      | t         |     |                                   | (DC2Type:array)
 fos_user |     16 | credentials_expired   | bool      | t         |     |                                   |
 fos_user |     17 | credentials_expire_at | timestamp | f         |     | NULL::timestamp without time zone |
(17 rows)

# select * from return_ro_tables_info() where tab_name='fos_user';

----------+--------+-----------------------+-----------+-----------+-----+-----------------------------------+-----------------

fos_user | 1 | id | int8 | t | t | |

(17 rows)

Названия колонок таблицы говорят сами за себя, описывать не буду

Ну и, собственно, сам код функции

-- Информация о колонках и ключах таблиц
DROP FUNCTION IF EXISTS return_ro_tables_info();
CREATE OR REPLACE FUNCTION return_ro_tables_info() RETURNS TABLE(
    tab_name      varchar,
    attnum        integer,
    field         varchar,
    type          varchar,
    isnotnull     boolean,
    pri           boolean,
    default_adsrc varchar,
    comment       varchar
) AS
$$
    SELECT
        c.relname::varchar AS tab_name,
        a.attnum::integer,
        a.attname::varchar AS field,
        t.typname::varchar AS type,
        a.attnotnull::boolean AS isnotnull,
        (SELECT 't'::boolean
            FROM pg_index
            WHERE c.oid = pg_index.indrelid
            AND pg_index.indkey[0] = a.attnum
            AND pg_index.indisprimary = 't'
        ) AS pri,
        (SELECT pg_attrdef.adsrc::varchar
            FROM pg_attrdef
            WHERE c.oid = pg_attrdef.adrelid AND pg_attrdef.adnum=a.attnum
        ) AS default_adsrc,
        (SELECT pg_description.description::varchar
            FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid
        ) AS comment
    FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n
    WHERE n.nspname = 'public'
        AND a.attnum > 0
        AND a.attrelid = c.oid
        AND a.atttypid = t.oid
        AND n.oid = c.relnamespace
    ORDER BY  c.relname, a.attnum;
$$
LANGUAGE sql;

-- Информация о колонках и ключах таблиц

DROP FUNCTION IF EXISTS return_ro_tables_info();

CREATE OR REPLACE FUNCTION return_ro_tables_info() RETURNS TABLE(

tab_name varchar,

attnum integer,

field varchar,

type varchar,

isnotnull boolean,

pri boolean,

default_adsrc varchar,

comment varchar

) AS

SELECT

c.relname::varchar AS tab_name,

a.attnum::integer,

a.attname::varchar AS field,

t.typname::varchar AS type,

a.attnotnull::boolean AS isnotnull,

(SELECT 't'::boolean

FROM pg_index

WHERE c.oid = pg_index.indrelid

AND pg_index.indkey[0] = a.attnum

AND pg_index.indisprimary = 't'

) AS pri,

(SELECT pg_attrdef.adsrc::varchar

FROM pg_attrdef

WHERE c.oid = pg_attrdef.adrelid AND pg_attrdef.adnum=a.attnum

) AS default_adsrc,

(SELECT pg_description.description::varchar

FROM pg_description WHERE pg_description.objoid = c.oid AND a.attnum = pg_description.objsubid

) AS comment

FROM pg_attribute a, pg_class c, pg_type t, pg_namespace n

WHERE n.nspname = 'public'

AND a.attnum > 0

AND a.attrelid = c.oid

AND a.atttypid = t.oid

AND n.oid = c.relnamespace

ORDER BY c.relname, a.attnum;

LANGUAGE sql;