Функции для документирования баз данных PostgreSQL. Часть первая

За время работы с PostgreSQL накопилось больше ста функций для работы с системными каталогами: pg_class, pg_attribute, pg_constraints и т.д.

zcrxta08dnfat5cqpgid4lyq7by.png


Что с ними делать? Используются они относительно редко. Включить в какой-нибудь проект? Красноярский заказчик за такую «ерунду» платить не будет. И все же, а вдруг они полезны еще кому-то кроме автора. И решил выложить их, как прочитанные книги в общедоступный шкаф для желающих.
Кто-то захочет их использовать в своей работе. А кого-то заинтересует отличный от своего опыт работы с системными каталогами.

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

Расширенный список характеристик таблицы базы данных возвращает функция admtf_Table_ComplexFeatures, которая в этой статье будет называться головной функцией. Таким образом, статья ограничится рассмотрением функций, которые вызываются в процессе выполнения головной функции.

В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.

О каких расширенных характеристиках идет речь?


Для того, чтобы составить представление о том, что здесь понимается под расширенными характеристиками таблицы базы данных, начнем с рассмотрения следующего списка характеристик. Список содержит характеристики таблицы базы данных Street (улицы), возвращенные функцией admtf_Table_ComplexFeatures ('public', 'street').

Приведенная ниже таблица содержит сокращенный список характеристик таблицы Street. Полный набор характеристик этой таблицы приведен в в Дополнительные материалах Приложения 2.

Таблица 1. Расширенные характеристики таблицы Street (улицы).

Категория Название Комментарий тип Базовый тип ? not NULL
tbl 0 street Список улиц в населенных пунктах
att 1 wcrccode Код страны wcrccode smallint t
att 2 localityid ИД населенного пункта localityid integer t
att 3 streetid ИД улицы населенного пункта streetid smallint t
att 4 streettypeacrm Акроним типа улицы streettypeacrm character (8) f
att 5 streetname Наименование улицы streettypeacrm varchar (150) t
pk 0 xpkstreet Первичный ключ таблицы street
pkatt 1 wcrccode Код страны wcrccode smallint t
fk01 1 fk_street_locality Внешний ключ таблицы
fk02 2 fk_street_streettype Внешний ключ таблицы
idx01 1 xie1street Индекс по типу и названию улицы населенного пункта
idx02 2 xie2street Индекс по названию улицы населенного пункта
idx03 3 xie3street Индекс по названиям улиц всех населенных пунктов
idx04 4 xpkstreet Индекс уникальный (первичный ключ) таблицы street

Переборов неприязнь к этому набору букв и цифр, можно заметить, что речь идет об обычных характеристиках таблицы базы данных:


  • Названии таблицы;
  • Списка атрибутов таблицы и их типов;
  • Первичного ключа и списка внешних ключей таблицы вместе с составляющими их атрибутами таблицы;
  • Списка индексов таблицы.

Уникальность каждой записи из списка характеристик обеспечивают значения полей «категория» и порядковый номер (»№») характеристики.

Таблица 2.Категории характеристик таблиц.

Акроним Назначение
tb Характеристики таблицы
att Характеристики атрибута таблицы
seq Характеристики последовательности
pk Характеристики первичного ключа
pkAtt Характеристики атрибута первичного ключа
fk99 Характеристики внешнего ключа
fk99att Характеристики атрибута внешнего ключа
fk99rtbl Характеристики таблицы, на которую ссылается внешний
ключ
fk99ratt Характеристики атрибута таблицы, на которую ссылается внешний ключ
Idx99 Характеристики индекса
Idx99att Характеристики атрибута индекса
Inhtbl99 Характеристики порожденной таблицы

Значение категории необходимо для того, чтобы отличать различные группы характеристик друг от друга. А порядковый номер, для того, чтобы отличать характеристики внутри группы.

В таблице базы данных может быть объявлено несколько внешних ключей (FOREIGN KEY) и индексов. Поэтому значение категории для этих характеристик и их потомков содержит порядковый номер. Например, запись с ключом «Категория» = idx02att и »№» = 1 указавает на первый атрибут 2-го индекса.

В выше приведенном списке категорий, место нахождения порядкового номера обозначено как '99'.

Замечание 1


xtye1lr36ssftr4kmvnwedjgnew.png

В статье приводятся примеры характеристик таблиц, которые кратко описаны во вспомогательной схеме, созданной специально для демонстрации возможностей функций. Но читатель, создав ту или иную функцию в своей базе данных, может в качестве параметров использовать названия своих схем и таблиц. Более того, в качестве параметра может быть использован, например, каталога pg_class, хотя в этом случае выдается ограничеснное число характеристик.

Конец замечания.


Структура головной функции


mnpw9welx2ltfhwiqllgqrp6uyc.png
Рис. 1. Функции, которые вызывает головная функция.

Таблица 3. Назначение функций.

Название Назначение
1 admtf_Table_Features Функция возвращает список характеристик таблицы базы данных
2 admtf_Table_Attributes Функция возвращает список атрибутов таблицы базы данных и их характеристик
3 admtf_Table_Constraintes Функция возвращает список ограничений таблицы базы данных и их характеристик
4 admtf_Table_Indexes Функция возвращает список индексов таблицы базы данных и их характеристик
5 admtf_Table_InheritanceChildrens Функция возвращает список таблиц, порожденных (IHERITS) от исходной таблицы базы данных.
6 admtf_Table_Sequences Функция возвращает список последовательностей (SEQUENCE), от которых зависит таблица
7 admtf_PrimaryKey_ComplexFeatures Функция возвращает полный (расширенный) список характеристик первичного ключа (PRIMARY KEY) таблицы базы данных.
8 admtf_ForeignKey_ComplexFeatures Функция возвращает полный (расширенный) список характеристик внешнего ключа (FOREIGN KEY) таблицы базы данных.
9 admtf_Index_ComplexFeatures Функция возвращает полный (расширенный) список характеристик индекса таблицы базы данных
10 admtf_Table_ComplexFeatures Функция возвращает полный (расширенный) список характеристик таблицы базы данных.

Замечание 2.

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

Конец замечания.

Функция admtf_Table_Features список характеристик таблицы базы данных

Функция admtf_Table_Features возвращает список характеристик собственно таблицы базы данных. Исходный код можно посмотреть и скачать здесь.

В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).

Основные данные функция извлекает из записи каталога pg_class, содержащего кроме записей о таблицах еще и, записи о последовательностях, представлениях, материализованных представлениях, составных типах. Поэтому для выбора таблиц используется условие relkind='r'.


57g7gk8twocsticu_kw6nvisgsq.png

Дополнительно функция обращается к данным каталогов pg_namespace и pg_description. Первый содержит названия схем базы данных, а второй — комментарии ко всем объектам БД.

Здесь важно обратить внимание на условие objsubid=0. Оно определяет комментарий к таблице, т к. значение поля objoid одинаково как для таблицы, так и для ее атрибутов. Комментарий к атрибуту таблицы содержится в записи, в которой objsubid совпадает с номером этого атрибута.

Таблица 4. Результат выполнения функции admtf_Table_Features ('public','Street').

Название Комментарий Число атрибутов Число ограничений CHECK ? имеется ли первичный ключ ? объявлены ли индексы ? есть ли потомки Число записей в таблице
street Список улиц в населенных пунктах 22 0 t t f 20150

Замечание 3

Обратите внимание на число атрибутов таблицы street. Оно значительно отличается от числа атрибутов, указанных во вспомогательной схеме.

Таблица 5. Дополнительные атрибуты таблицы Street.


attname atttypid attnum Примечание
cmin 29 -4 Системный атрибут
xmin 28 -3 Системный атрибут
ctid 27 -1 Системный атрибут
wcrccode 795369 1 Действующий атрибут
localityid 795352 2 Действующий атрибут
streetid 795364 3 Действующий атрибут
streettypeacrm 1919168 4 Действующий атрибут
streetname 1043 5 Действующий атрибут
…pg.dropped.6… 0 6 Удаленный атрибут
…pg.dropped.7… 0 7 Удаленный атрибут

Дело в том, что PostgreSQL кроме основных атрибутов дополнительно учитывает несколько системных атрибутов, и даже удаленные атрибуты.

Конец замечания

Функция admtf_Table_Attributes список атрибутов таблицы базы данных и их характеристик

Функция admtf_Table_Attributes возвращает список атрибутов таблицы базы данных.Исходный код можно посмотреть и скачать здесь.

В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).

Основные данные функция извлекает из записи каталогов pg_attribute и pg_type. Первый содержит записи с данными об атрибутах таблиц, представлений, материализованных представлений, составных типов и даже функций. Второй — о характеристиках типах атрибутов.

Возможно, некоторого пояснения требует способ, которым в функции определяются пользовательский и базовый типы.

Атрибут таблицы объявлен с пользовательским типом, если в соответствующей записи каталога pg_type поле typbasetype больше 0. В противном случае атрибут имеет базовый тип. Поэтому в предложении FROM каталог pg_type участвует дважды. В первой записи каталога определяется наличие пользовательского типа, если таковой не определен (typbasetype=0), то по этой записи формируется значение базового типа. В противном случае базовый тип определяется из записи, для которой btyp.OID= typ.typbasetype.

Непосредственно строка с базовым типом формируется при помощи функции системного каталога FORMAT_TYPE (type_oid, typemod). Первым параметром которой является записи OID базового типа. Второй параметр — это значение модификатора для типов, которые содержат в себе размер. Например, VARCHAR (100) или NUMERIC (4,2), DECIMAL (4,2). Значение параметра typemod берется из typ.typtypmod в случае, если атрибут имеет пользовательский тип, иначе из attr.atttypmod, т.е. непосредственно из записи об атрибуте.


7l4zojgp7tw1c4ntsyqy0idlbgg.png

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

Третий каталог используется для извлечения комментария к атрибуту таблицы.

Комментарий к атрибуту таблицы находится в записи, в который dsc.objoid содержит OID исходной таблицы, а dsc.objsubid порядковый номер атрибута в таблице, т.е. attr.attnum.

Для того чтобы функция не возвращала системные и удаленные атрибуты, в предложении WHERE установлено условие attr.attnum>0 AND attr.atttypID>0.

Таблица 6. Результат выполнения функции admtf_Table_Attributes ('public','Street').

Название Пользовательский тип Базовый тип ? not NULL Комментарий
1 wcrccode wcrccode smallint t Код страны
2 localityid localityid integer t ИД населенного пункта
3 streetid streetid smallint t ИД улицы населенного пункта
4 streettypeacrm streettypeacrm character (8) f Акроним типа улицы
5 streetname varchar (150) t Наименование улицы населенного пункта


Версия функции с использованием псевдонима regclass для типа oid

Идентификаторы объектов (OIDs) в PostgreSQL имеют одноименный тип OID, который в настоящий момент реализован как беззнаковое четырёхбайтовое целое число. Но благодаря наличию псевдонимов этого типа, целое число может быть представлено как имя объекта. И наоборот — преобразовать имя объекта к целому числу типа OID.


q2u8ruhhdc3v8ph1nbl8oixgqu8.png

В списке выводимых значение оператора SELECT до преобразования с применением псевдонимов типа OID все значения кроме названия атрибута числовые, но в результате отображены названия таблицы и типов атрибутов. Типы выводимых значений можно рассмотреть во второй строчке заголовка таблицы.

Кроме того, в разделе WHERE утверждения находится условие attr.attrelid=('public'||'.'||'Street'):: regclass, в левой части которого числовое значение, а в правой — строковое, которое преобразуется к числовому с помощью псевдонима regclass.


wwdfodjjlo_gfbjehvgkvtlutqw.png

С помощью псевдонима regclass из основного утверждения можно убрать соединение с двумя каталогами. Но на производительность функции такое улучшение почти не сказалось — и в той, и в другой версии функция выполняется за 11 ms. Возможно из-за того, что в тестовой таблице мало атрибутов.

Замечание 4

Серьезный недостаток условия в форме attr.attrelid=(a_SchemaName||'.'||a_TableName):: regclass проявляется в случае, когда в базе данных присутствует схема и/или таблица с необычным названием. Например, «Моя схема» и/или «Моя таблица». Такие значения нужно передавать, заключенными в двойные кавычки или использовать функцию QUOTE_IDENT, иначе функция завершится с системной ошибкой.


jtaknvaqwphry4yla4bnlemrdeq.png

Поэтому я предпочитаю использовать условия в форме LOWER (nspc.nspname)=LOWER (a_SchemaName) AND LOWER (tbl.relname) =LOWER (a_TableName), которые не приводит к системным ошибкам.

Конец замечания

ПРИЛОЖЕНИЕ 1. Скрипты


Создание функции admtf_Table_Features


Комментарии к исходному коду функции можно посмотреть здесь.

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Features (a_SchemaName NAME,a_TableName NAME);
/********************************************************************************************************/
/*  Функция возвращает список характеристик таблицы, принадлежащей схеме                                */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Features
        (a_SchemaName   NAME default 'public',  /* название схемы базы данных           */
         a_TableName    NAME default NULL       /* Название таблицы */
 )                                                                               

RETURNS  TABLE (rs_TableName NAME,rs_TableDescription TEXT,rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS
$BODY$
DECLARE c_TableKind CONSTANT CHAR:='r';
        v_TableOID         OID;      /* ИД таблицы */
        v_TableName      NAME;    /* Название таблицы */
        v_TableDescription TEXT; /* Описание таблицы */
        v_TableNumberOfRowCalc INTEGER; /* Число записей в таблице */
        --******************************************************************************************************                
BEGIN           
         SELECT INTO rs_TableName,rs_TableDescription,rs_NumberOfAttribute,
                rs_NumberOfChecks,rs_hasPKey,rs_hasIndex,rs_hasSubClass,
                rs_NumberOfRow
                tbl.relname,dsc.description,tbl.relnatts::INTEGER,tbl.relchecks::INTEGER,
                tbl.relhaspkey,tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::INTEGER  
        FROM pg_class tbl 
                        INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid 
                        LEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoid
                                                                AND dsc.objsubid=0
        WHERE nspc.nspname=LOWER(a_SchemaName) AND tbl.relkind=c_TableKind
                                        AND tbl.relname =LOWER(a_TableName);
        EXECUTE 'SELECT count(*) FROM ' ||LOWER(a_SchemaName)
                                        ||'.'||quote_ident(LOWER(a_TableName)) 
                                INTO v_TableNumberOfRowCalc;
        RETURN QUERY SELECT rs_TableName,rs_TableDescription,rs_NumberOfAttribute,
                                        rs_NumberOfChecks,rs_hasPKey,rs_hasIndex,
                                        rs_hasSubClass,v_TableNumberOfRowCalc AS rs_NumberOfRow;        
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Features(a_SchemaName NAME,a_TableName NAME) IS 'Возвращает список характеристик таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Features (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256));
/********************************************************************************************************/
/*  Функция возвращает список характеристик таблицы, принадлежащей схеме                                */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Features
        (a_SchemaName   VARCHAR(256) default 'public',  /* название схемы базы данных    */
        a_TableName     VARCHAR(256) default NULL       /* Название таблицы */
 )                                                                               

RETURNS  TABLE (rs_TableName VARCHAR(256),rs_TableDescription TEXT,rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS
$BODY$
DECLARE c_TableKind             CONSTANT CHAR:='r';
        v_TableOID      OID;                    /* ИД таблицы */
        v_TableName     VARCHAR(256);   /* Название таблицы */
        v_TableDescription      TEXT;           /* Описание таблицы */
        v_TableNumberOfRowCalc  INTEGER;                /* Число записей в таблице */
        --******************************************************************************************************                
BEGIN           
        RETURN QUERY SELECT tf.rs_TableName::VARCHAR(256),
                                        tf.rs_TableDescription::TEXT,
                                        tf.rs_NumberOfAttribute::INTEGER,
                                        tf.rs_NumberOfChecks::INTEGER,
                                        tf.rs_hasPKey::BOOLEAN,
                                        tf.rs_hasIndex::BOOLEAN,
                                        tf.rs_hasSubClass::BOOLEAN,
                                        tf.rs_NumberOfRow::INTEGER
                                FROM admtf_Table_Features(a_SchemaName::NAME,a_TableName::NAME) tf;     

END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Features(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS 'Возвращает список характеристик таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECt * FROM admtf_Table_Features('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECt * FROM admtf_Table_Features('public':: NAME,'Street'::NAME);


Создание функции admtf_Table_Attributes


Комментарии к исходному коду функции можно посмотреть здесь.

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME);
/********************************************************************************************************/
/*  Функция возвращает список колонок таблицы                                                           */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Attributes
                (a_SchemaName   NAME default 'public',  /* название схемы базы данных   */
                a_TableName     NAME default NULL       /* Название таблицы */
 )                                                                               

RETURNS  TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS
$BODY$
DECLARE c_TableKind             CONSTANT CHAR:='r';
        v_Scale                 INTEGER;                /* Масштаб колонки */
        --******************************************************************************************************                
BEGIN           
        RETURN QUERY SELECT attr.attnum AS r_AttributeNumber,
                                        attr.attname::NAME AS r_AttributeName,
                                CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME
                                        ELSE ''::NAME END AS r_UserTypeName,
                                FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
                                        COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME 
                                                                                                                        AS r_TypeName,
                                attr.attnotnull AS r_isNotNULL,
                                dsc.description AS r_Description 
                        FROM pg_attribute attr 
                                INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid 
                                INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
                                LEFT OUTER JOIN pg_type typ ON attr.atttypid=typ.oid
                                LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid
                                LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid
                                                                AND dsc.objsubid=attr.attnum
                        WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
                                        AND LOWER(tbl.relname)=LOWER(a_TableName)
                                        AND tbl.relkind=c_TableKind AND attr.attnum>0 AND attr.atttypID>0 
                        ORDER BY tbl.relname,attr.attnum;               

        RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName NAME,a_TableName NAME) IS 'Возвращает список колонок таблицы';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256));
/********************************************************************************************************/
/*  Функция возвращает список колонок таблицы                                                           */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Attributes
                (a_SchemaName   VARCHAR(256) default 'public',  /* название схемы базы данных   */
                a_TableName     VARCHAR(256) default NULL       /* Название таблицы */
 )                                                                               

RETURNS  TABLE (r_AttributeNumber SMALLINT,r_AttributeName      VARCHAR(256),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN, r_Description Text) AS
$BODY$
DECLARE c_TableKind             CONSTANT CHAR:='r';
        v_Scale                 INTEGER;                /* Масштаб колонки */
        --******************************************************************************************************                
BEGIN           
        RETURN QUERY SELECT ta.r_AttributeNumber::SMALLINT,
                                        ta.r_AttributeName::VARCHAR(256),
                                        ta.r_UserTypeName::VARCHAR(256),
                                        ta.r_TypeName::VARCHAR(256),
                                        ta.r_isNotNULL::BOOLEAN,
                                        ta.r_Description::TEXT
                FROM admtf_Table_Attributes(a_SchemaName::NAME,a_TableName::NAME) ta;           
        RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS 'Возвращает список колонок таблицы';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Attributes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Attributes('public'::NAME,'Street'::NAME);


Создание функции admtf_Table_Attributes с использованием псевдонима regclass


Комментарии к исходному коду функции можно посмотреть здесь.

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME);
/********************************************************************************************************/
/*  Функция возвращает список колонок таблицы                                                           */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Attributes
                        (a_SchemaName   NAME default 'public',  /* название схемы базы данных           */
                        a_TableName     NAME default NULL       /* Название таблицы */
        
 )                                                                               

RETURNS  TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS
$BODY$
DECLARE c_TableKind             CONSTANT CHAR:='r';
        v_Scale                 INTEGER;                /* Масштаб колонки */
        --******************************************************************************************************                
BEGIN           
        RETURN QUERY SELECT attr.attnum AS r_AttributeNumber,
                                attr.attname::NAME AS r_AttributeName,
                                CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME
                                         ELSE ''::NAME END AS r_UserTypeName,
                                FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
                                        COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME
                                                                                                AS r_TypeName,                          
                                attr.attnotnull AS r_isNotNULL,
                                dsc.description AS r_Description 
                        FROM pg_attribute attr 
                                INNER JOIN pg_type typ ON attr.atttypid=typ.oid
                                LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid
                                LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid
                                                                AND dsc.objsubid=attr.attnum
                        WHERE attr.attrelid=(LOWER(a_SchemaName)||'.'||
                                                        LOWER(a_TableName))::regclass 
                                AND attr.attnum>0 AND attr.atttypID>0 
                        ORDER BY attr.attnum;           
        RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName NAME,a_TableName NAME) IS 'Возвращает список колонок таблицы';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256));
/********************************************************************************************************/
/*  Функция возвращает список колонок таблицы                                                           */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Attributes
                (a_SchemaName   VARCHAR(256) default 'public',  /* название схемы базы данных   */
                a_TableName     VARCHAR(256) default NULL       /* Название таблицы */
 )                                                                               

RETURNS  TABLE (r_AttributeNumber SMALLINT,r_AttributeName      VARCHAR(256),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN, r_Description Text) AS
$BODY$
DECLARE c_TableKind             CONSTANT CHAR:='r';
        v_Scale                 INTEGER;                /* Масштаб колонки */
        --******************************************************************************************************                
BEGIN           
        RETURN QUERY SELECT ta.r_AttributeNumber::SMALLINT,
                                ta.r_AttributeName::VARCHAR(256),
                                ta.r_UserTypeName::VARCHAR(256),
                                ta.r_TypeName::VARCHAR(256),
                                ta.r_isNotNULL::BOOLEAN,
                                ta.r_Description::TEXT
                        FROM admtf_Table_Attributes(a_SchemaName::NAME,a_TableName::NAME) ta;   
        RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS 'Возвращает список колонок таблицы';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION; 
SELECT * FROM admtf_Table_Attributes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Attributes('public'::NAME,'Street'::NAME);


ПРИЛОЖЕНИЕ 2. Дополнительные материалы


Вспомогательная схема базы данных


qirb9qyb5nzkn3hef8v7pvgyvvs.png

  • COUNTRY — Классификатор стран мира — ОКСМ (Общероссийский классификатор стран мира);
  • HOUSEADDR — Список номеров домов на улицах населенных пунктов;
  • LCLTYTYPE — Справочник типов населенных пунктов;
  • LOCALITY — Список населенных пунктов;
  • STREET — Список улиц в населенных пунктах;
  • STREETTYPE — Справочник типов улиц;
  • TERRITORY — Список территорий (республик, краев, областей, районов и т.д.);
  • TERRITORYTYPE — Справочник типов территорий.

Расширенные характеристики таблицы Street (улицы)


Таблица 1. Расширенные характеристики таблицы Street (улицы).

Категория Название Комментарий тип Базовый тип ? not NULL
tbl 0 street Список улиц в населенных пунктах
att 1 wcrccode Код страны wcrccode smallint t
att 2 localityid ИД населенного пункта localityid integer t
att 3 streetid ИД улицы населенного пункта streetid smallint t
att 4 streettypeacrm Акроним типа улицы streettypeacrm character (8) f
att 5 streetname Наименование улицы streettypeacrm varchar (150) t
pk 0 xpkstreet Первичный ключ таблицы street
pkatt 1 wcrccode Код страны wcrccode smallint t
pkatt 2 localityid ИД населенного пункта localityid integer t
pkatt 3 streetid ИД улицы населенного пункта streetid smallint t
fk01 1 fk_street_locality Внешний ключ таблицы
fk01att 1 wcrccode Код страны wcrccode smallint t
fk01att 2 localityid ИД населенного пункта localityid integer t
fk01rtbl 0 locality Список населенных пунктов
fk01ratt 1 wcrccode Код страны wcrccode smallint t
fk01ratt 2 localityid ИД населенного пункта localityid integer t
fk02 2 fk_street_streettype Внешний ключ таблицы
fk02att 1 streettypeacrm Акроним типа улицы streettypeacrm character (8) f
fk02rtbl 0 streettype Справочник типов улиц
fk02ratt 1 streettypeacrm Акроним типа улицы streettypeacrm character (8) t
idx01 1 xie1street Индекс по типу и названию улицы населенного пункта
idx01att 1 wcrccode Код страны wcrccode smallint t
idx01att 2 localityid ИД населенного пункта localityid integer t
idx01att 3 streettypeacrm Акроним типа улицы streettypeacrm character (8) f
idx01att 4 streetname Наименование улицы населенного пункта varchar (150) t
idx02 2 xie2street Индекс по названию улицы населенного пункта
idx02att 1 wcrccode Код страны wcrccode smallin t
idx02att 2 localityid ИД населенного пункта localityid integer t
idx02att 3 streetname Наименование улицы населенного пункта varchar (150) t
idx03 3 xie3street Индекс по названиям улиц всех населенных пунктов
idx03att 1 streetname Наименование улицы населенного пункта varchar (150) t
idx04 4 xpkstreet Индекс уникальный (первичный ключ) таблицы street
idx04att 1 wcrccode Код страны wcrccode smallint t
idx04att 2 localityid ИД населенного пункта localityid integer t
idx04att 3 streetid ИД улицы населенного пункта streetid smallint t

© Habrahabr.ru