Pull to refresh

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

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


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

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

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

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

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

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




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


Для того, чтобы составить представление о том, что здесь понимается под расширенными характеристиками таблицы базы данных, начнем с рассмотрения следующего списка характеристик. Список содержит характеристики таблицы базы данных 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



исходный код оператора на рисунке
SELECT * FROM admtf_Table_ComplexFeatures('pg_catalog','pg_class');



В статье приводятся примеры характеристик таблиц, которые кратко описаны во вспомогательной схеме, созданной специально для демонстрации возможностей функций. Но читатель, создав ту или иную функцию в своей базе данных, может в качестве параметров использовать названия своих схем и таблиц. Более того, в качестве параметра может быть использован, например, каталога 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'.



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 одинаково как для таблицы, так и для ее атрибутов. Комментарий к атрибуту таблицы содержится в записи, в которой 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, т.е. непосредственно из записи об атрибуте.



исходный код оператора на рисунке
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 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. В нем необычно извлекаются названия таблицы атрибута и названия его типов — вместо обращения к соответствующим полям каталогов с названиями этих характеристик используются:

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


SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,
        typ.typbasetype::regtype,attr.attrelid::regclass::name,
        attr.atttypid::regtype::name,typ.typbasetype::regtype::name 
   FROM pg_attribute attr 
     INNER JOIN pg_type typ ON attr.atttypid=typ.oid
   WHERE attr.attrelid=('public'||'.'||'Street')::regclass 
     AND attr.attnum>0 AND attr.atttypID>0
   ORDER BY attr.attnum;

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



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


Кроме того, в разделе WHERE утверждения находится условие attr.attrelid=('public'||'.'||'Street')::regclass, в левой части которого числовое значение, а в правой – строковое, которое преобразуется к числовому с помощью псевдонима 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),
 		COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256),
       attr.attnotnull, dsc.description
   FROM pg_attribute attr 
     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 attr.attrelid=( a_SchemaName ||'.'|| a_TableName)::regclass 
      AND attr.attnum>0 AND attr.atttypID>0
   ORDER BY attr.attnum;


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

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



исходный код оператора на рисунке
/* Выполнение следующего утверждения завершится системным сообщением  «ошибка синтаксиса в имени»*/
SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype,
	attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name 
FROM pg_attribute attr 
		INNER JOIN pg_type typ ON attr.atttypid=typ.oid
WHERE attr.attrelid=('Моя схема'||'.'||'Моя таблица')::regclass AND attr.attnum>0 AND attr.atttypID>0
ORDER BY attr.attnum;
/* А вот это выполнится без ошибок */
SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype,
	attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name 
FROM pg_attribute attr 
		INNER JOIN pg_type typ ON attr.atttypid=typ.oid
WHERE attr.attrelid=('"Моя схема"'||'.'||'"Моя таблица"')::regclass AND attr.attnum>0 AND attr.atttypID>0
ORDER BY attr.attnum;


Поэтому я предпочитаю использовать условия в форме 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


Смотрите также


Функции для документирования баз данных PostgreSQL. Часть вторая;
Функции для документирования баз данных PostgreSQL. Часть третья.
Функции для документирования баз данных PostgreSQL. Окончание(часть четвертая).
Tags:
Hubs:
Total votes 10: ↑9 and ↓1 +8
Views 5.6K
Comments Comments 4