Функции Для Документирования Баз Данных Postgresql. Первая Часть

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



Что с ними делать? Они используются относительно редко.

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

И я решил положить их, как прочитанные книги, в общественный шкаф для желающих.

Кто-то захочет использовать их в своей работе.

А кому-то будет интересен иной опыт работы с системными каталогами, чем собственный.

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

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

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

Первая половина статьи содержит комментарии по реализации функций.

Второй содержит исходные коды функций.

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

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

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

О каких расширенных функциях мы говорим?

Список содержит характеристики таблицы базы данных Street, возвращаемые функцией.

admtf_Table_ComplexFeatures( 'общественный' , 'улица' ) .

В таблице ниже приведен сокращенный список характеристик таблицы Street. Полный набор характеристик этой таблицы приведен в в дополнительных материалах, Приложение 2. Таблица 1. Расширенные характеристики таблицы Street.



Текстовый вариант таблицы на рисунке

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

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

Текстовый вариант таблицы на рисунке

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

Таблица базы данных может иметь несколько внешних ключей (FOREIGN KEY) и объявленных индексов.

Следовательно, значение категории для этих характеристик и их потомков содержит порядковый номер.

Например, запись с ключом «Категория» = idx02att и «Нет» = 1 указывает на первый атрибут второго индекса.

В приведенном выше списке категорий расположение серийного номера указано как «99».

Примечание 1



исходный код оператора на рисунке

   

 SELECT * FROM admtf_Table_ComplexFeatures('pg_catalog','pg_class');
 

   

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

Причем в качестве параметра можно использовать, например, каталог pg_class , хотя в этом случае дается ограниченное количество характеристик.

Конец примечания.

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

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

Текстовый вариант таблицы на рисунке

Причина в том, что статью придется разделить на несколько частей.

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

Конец примечания.

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

Исходный код можно посмотреть и скачать здесь.

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

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

Функция извлекает основные данные из записи каталога.

pg_class , содержащий помимо записей о таблицах также записи о последовательностях, представлениях, материализованных представлениях и составных типах.

Поэтому условие relkind= используется для выбора таблиц 'р' .

SELECT 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 LOWER( nspc.nspname)=LOWER(a_SchemaName) AND tbl.relkind='r' AND LOWER(tbl.relname) =LOWER(a_TableName);

Кроме того, функция обращается к данным каталога pg_namespace И pg_description .

Первый содержит имена схем базы данных, а второй — комментарии для всех объектов базы данных.

Здесь важно обратить внимание на условие objsubid=0 .

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

Комментарий к атрибуту таблицы содержится в записи, в которой объект соответствует номеру этого атрибута.

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

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

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

Конец примечания

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

Исходный код можно посмотреть и скачать здесь.

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

Функция извлекает основные данные из записей каталога.

pg_attribute И pg_type .

Первый содержит записи с данными об атрибутах таблиц, представлений, материализованных представлений, составных типов и даже функций.

Во-вторых, речь идет о характеристиках и типах атрибутов.

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

Атрибут таблицы объявляется с пользовательским типом, если он указан в соответствующей записи каталога.

pg_type поле типбазовый тип больше 0. В противном случае атрибут имеет базовый тип.

Поэтому в предложении FROM указан каталог pg_type участвует дважды.

Первая запись каталога определяет наличие пользовательского типа, если он не определен ( типбазовыйтип=0 ), то из этой записи формируется значение базового типа.

В противном случае базовый тип определяется по записи, для которой btyp.OID= typ.typbasetype. Строка с базовым типом формируется непосредственно с помощью функции системного каталога.

FORMAT_TYPE(type_oid, typemod) .

Первым параметром которого является запись OID базового типа.

Второй параметр — это значение модификатора для типов, содержащих размер.

Например, VARCHAR(100) или NUMERIC(4,2), DECIMAL(4,2).

Значение параметра типмод взято из тип.

типтипмод в случае, если атрибут имеет пользовательский тип, в противном случае из attr.atttypmod , т. е.

непосредственно из записи атрибута.

исходный код оператора на рисунке

SELECT attr.attnum, attr.attname::VARCHAR(100), CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256), attr.attnotnull, dsc.description FROM pg_attribute attr INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE LOWER( nspc.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname) =LOWER(a_TableName) AND tbl.relkind='r' AND attr.attnum>0 AND attr.atttypID>0 ORDER BY tbl.relname,attr.attnum;

Кроме того, функция обращается к данным каталога pg_class , pg_namespace И pg_description .

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

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

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

attr.attnum .

Чтобы функция не возвращала системные и удаленные атрибуты, в предложении WHERE задается условие.

attr.attnum> 0 И attr.atttypID> 0 .

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

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

Но благодаря наличию псевдонимов этого типа целое число может быть представлено как имя объекта.

И наоборот — преобразовать имя объекта в целое число типа OID. В качестве примера рассмотрим следующее утверждение ВЫБИРАТЬ .

Он необычным образом извлекает имена таблицы атрибутов и имена ее типов — вместо обращения к соответствующим полям каталога с именами этих характеристик он использует:

• attrelid::regclass(attrelid::regclass:NAME) ,
• atttypid::regtype(atttypid::regtype:NAME)
• typebasetype::regtype(typbasetype::regtype:NAME) .

'||'Street')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum;

Ниже приведен результат выполнения этого запроса.

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

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

Кроме того, в предложении WHERE оператора есть условие attr.attrelid=('public'||'.

'||'Street')::regclass , левая часть которого представляет собой числовое значение, а правая часть — строковое значение, которое преобразуется в числовое значение с помощью псевдонима регулярный класс .

исходный код оператора на рисунке

SELECT attr.attnum, attr.attname::VARCHAR(100), CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),

Теги: #Администрирование базы данных #postgresql #pg_class #pg_attribute #pg_namespace #pg_description #pg_type pg_constraints #pg_index #pg_index #pg_am

• Video Studio Express — Универсальное, Простое В Использовании Профессиональное Программное Обеспечение Для Редактирования Видео От Aimersoft

  19 Oct, 24

• Apple Объявила Об Удалении Устаревших И Ошибочных Приложений Из App Store

  19 Oct, 24

• Курс Mit «Безопасность Компьютерных Систем». Лекция 23: «Экономика Безопасности», Часть 3

  19 Oct, 24

• Тестирование Точек Доступа Zyxel Vs Ubiquiti

  19 Oct, 24

• Ambilight Для World Of Tanks - Новогоднее Освещение

  19 Oct, 24

• Jaguar Будет Читать Мысли И Следить За Здоровьем Водителей

  19 Oct, 24

• Копаемся Внутри Умной Тепловизионной Камеры С Микроболометром

  19 Oct, 24

• Я Пишу Двухмерную Игру На Java №2.

  19 Oct, 24

• Визуализация Четырехдеревьев В Реальном Времени На Shader Model 2.0

  19 Oct, 24

• Программисты Подверглись «Атаке» Со Стороны Неизвестных Правоохранительных Органов

  19 Oct, 24