Что с ними делать? Используются они относительно редко. Включить в какой-нибудь проект? Красноярский заказчик за такую «ерунду» платить не будет. И все же, а вдруг они полезны еще кому-то кроме автора. И решил выложить их, как прочитанные книги в общедоступный шкаф для желающих.
Кто-то захочет их использовать в своей работе. А кого-то заинтересует отличный от своего опыт работы с системными каталогами.
Но чтобы не превращать публикацию в скучное перечисление, неизвестно зачем созданных функций, решил остановиться на тех из них, которые можно объединить общей целью. Поэтому выбраны функции, которые используются для выдачи расширенного списка характеристик произвольной таблицы базы данных.
Расширенный список характеристик таблицы базы данных возвращает функция 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
В статье приводятся примеры характеристик таблиц, которые кратко описаны во вспомогательной схеме, созданной специально для демонстрации возможностей функций. Но читатель, создав ту или иную функцию в своей базе данных, может в качестве параметров использовать названия своих схем и таблиц. Более того, в качестве параметра может быть использован, например, каталога pg_class, хотя в этом случае выдается ограничеснное число характеристик.
Конец замечания.
Структура головной функции
Рис. 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'.
Дополнительно функция обращается к данным каталогов 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, т.е. непосредственно из записи об атрибуте.
Дополнительно функция обращается к данным каталогов 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.
В списке выводимых значение оператора SELECT до преобразования с применением псевдонимов типа OID все значения кроме названия атрибута числовые, но в результате отображены названия таблицы и типов атрибутов. Типы выводимых значений можно рассмотреть во второй строчке заголовка таблицы.
Кроме того, в разделе WHERE утверждения находится условие attr.attrelid=('public'||'.'||'Street')::regclass, в левой части которого числовое значение, а в правой – строковое, которое преобразуется к числовому с помощью псевдонима regclass.
С помощью псевдонима regclass из основного утверждения можно убрать соединение с двумя каталогами. Но на производительность функции такое улучшение почти не сказалось – и в той, и в другой версии функция выполняется за 11 ms. Возможно из-за того, что в тестовой таблице мало атрибутов.
Замечание 4
Серьезный недостаток условия в форме attr.attrelid=(a_SchemaName||'.'||a_TableName)::regclass проявляется в случае, когда в базе данных присутствует схема и/или таблица с необычным названием. Например, «Моя схема» и/или «Моя таблица». Такие значения нужно передавать, заключенными в двойные кавычки или использовать функцию QUOTE_IDENT, иначе функция завершится с системной ошибкой.
Поэтому я предпочитаю использовать условия в форме 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. Дополнительные материалы
Вспомогательная схема базы данных
- 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 |
Комментарии (4)
mgramin
08.07.2018 12:41А чем не подошли такие тулы как schemacrawler или schemaspy? Они и доки формируют и шаблоны поддерживают и даже рисуют диаграммы.
gladkovs Автор
08.07.2018 13:17Причина обычная. Так случилось, что никаких средств, в том числе и schemacrawler или schemaspy, для документирования у меня не было.
mgramin
Спасибо за полезную статью. Если не секрет, какая именно задача решалась? И ещё — не было мыслей выложить код на github/gitlab ?
gladkovs Автор
Спасибо на добром слове и за идею. Неоднократно решалась задача документирования унаследованной базы данных, базы данных разработанной не мной, в условиях очень ограниченного бюджета.
Мыслей выложить код на github/gitlab пока не было.