Pull to refresh

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

Reading time 19 min
Views 3.9K
Это вторая часть статьи, которая описывает пользовательские функции для работы с системными каталогами: pg_class, pg_attribute, pg_constraints и т.д.

В этой части статьи обсуждаются функции, возвращающие характеристики ограничений и индексов.

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

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

admtf_Table_Constraintes список ограничений таблицы базы данных и их характеристик



Функция admtf_Table_Constraintes возвращает список ограничений (CONSTRAINT) таблицы базы данных и их характеристик. Исходный код можно посмотреть и скачать здесь, а здесь приведена версия функции, в которой не используется курсор.



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

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




исходный код оператора на рисунке
SELECT tbl.OID,con.conname,con.contype,con.conkey,reftbl.OID,
       reftbl.relname,con.confkey,con.consrc 
   FROM pg_constraint con 
     INNER JOIN pg_namespace nsp ON con.connamespace=nsp.oid 
     LEFT OUTER JOIN pg_class tbl ON con.conrelid=tbl.oid
     LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
     AND LOWER(tbl.relname)=LOWER(a_TableOID)
ORDER BY con.contype DESC,con.conname;


Основные данные (название и тип ограничения) функция извлекает из записи каталога pg_constraint. Из этого же каталога извлекаются характеристики каждого ограничения, которые представлены в форме OID таблиц (conrelid, confrelid) или массивов порядковых номеров атрибутов (conkey, confkey), участвующих в ограничении.



Характеристики ограничений функция возвращает в виде названий таблиц и атрибутов. При этом наименования таблиц извлекаются из записи каталога pg_class по идентификатору (OID), а наименования атрибутов из записей каталога pg_attribute по идентификатору таблицы и порядковому номеру атрибута. Т.к. порядковые номера хранятся в основном каталоге в форме массива (списка), то списки наименований атрибутов формируются внутри функции с помощью цикла.


Функция возвращает одну особую характеристику – правило проверки значений полей в записях таблицы (ограничение CHECK). Эта характеристика хранится как текстовое значение в поле consrc, каталога pg_constraint.


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

Текстовая версия таблицы на рисунке
Название Тип Атрибуты исходной таблицы Название внешней таблицы Атрибуты внешней таблицы Правило проверки
xpkstreet p wcrccode, localityid, streetid
fk_street_locality f wcrccode, localityid locality wcrccode, localityid
fk_street_streettype f streettypeacrm streettype streettypeacrm
ck_street_streetname c streetname ((streetname)::text !~* '[a-z]'::text)
ck_street_streettypeacrm c streettypeacrm ((streettypeacrm)::bpchar !~* '[a-z]'::text)



Версия без курсора


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


Отвечать не буду –на вкус и цвет товарищей нет. Но приведу версию функции без курсора. Версию реализации функции без использования курсора можно посмотреть и скачать здесь.


Главная сложность в том, чтобы организовать соединение (JOIN) таблиц по значениям расположенных в атрибуте типа массив одной из них. Такими массивами в этом случае являются conkey и confkey.



SELECT c.conname,c.contype,c.conkey::SMALLINT[],
       GENERATE_SUBSCRIPTS(c.conkey, 1) as No
   FROM  pg_constraint c 
   WHERE c.conname='fk_street_locality'
   ORDER BY No;

Для решения такой PostgrSQL содержит функции, которые возвращают таблицу из значений указателей на элементы массива. В нашем случае будет использована функция generate_subscripts. Мало того, что она генерирует множество указателей на позицию массива, переданного ей в качестве параметра, она еще превращает одну запись, содержащею массив, в несколько по числу элементов массива. Каждая запись такой таблицы содержит одно уникальное значение – позицию массива.



Таблица 8. Размножение исходной строки с помощью generate_subscripts.

Название ограничения Тип Массив номеров атрибутов Указатель на позицию массива
fk_street_locality f {1,2} 1
fk_street_locality f {1,2} 2



исходный код оператора на рисунке
SELECT con.conname AS ConstraintName,con.contype::VARCHAR(2) AS ConstraintType,
       STRING_AGG(attr.attname, ', 'ORDER BY con.No) AS r_ConstraintKeyNames,
       reftbl.relname AS RefTableName,
       STRING_AGG(rattr.attname,', 'ORDER BY con.No) AS r_RefTableKeyNames,
       con.consrc AS ConstraintSource
   FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.contype,
                c.conkey::SMALLINT[],c.consrc, c.confkey::SMALLINT[],
                generate_subscripts(c.conkey, 1) as No 
            FROM pg_constraint c) con 
              INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
              INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid 
                                            AND attr.attnum=con.conkey[con.No]
              INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
              LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
              LEFT OUTER JOIN pg_attribute rattr ON rattr.attrelid=reftbl.oid
                                                  AND rattr.attnum=con.confkey[con.No]
   WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
       AND LOWER(tbl.relname)=LOWER(a_TableName)
   GROUP BY con.conname,con.contype,reftbl.relname,con.consrc
   ORDER BY con.contype DESC,con.conname;


Такую таблицу можно соединить с каталогом атрибутов pg_attribute, извлекая из него названия атрибутов по условию attr.attrelid=tbl.oid AND attr.attnum=con.conkey[con.No].
Теперь осталось убрать лишние записи при помощи группировки записей, а из названий атрибутов создать строку.


Создание строки выполняется с помощью агрегирующей функции STRING_AGG, в которой обязательно нужно указать опцию сортировки (ORDER BY), иначе порядок атрибутов может оказаться несоответствующим порядку объявления атрибутов в индексе.



Время выполнения обеих версий функций у меня совпало. На вывод данных в таблице результатов ушло 20 ms.


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



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


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

исходный код оператора на рисунке
SELECT tbl.oid,inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary,
       inx.indkey::SMALLINT[],inx.indoption::SMALLINT[],inxam.amcanorder
   FROM pg_index inx 
     INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid 
     INNER JOIN pg_namespace inxnsp ON inxcls.relnamespace=inxnsp.oid
     INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
     INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid
     INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
     AND LOWER(tbl.relname)=LOWER(a_TableOID)
ORDER BY inxam.amname, inxcls.relname;


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


CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 
                                        WHEN 1 THEN ' DESC' 
                                        ELSE ' ASC'  END 
                                ELSE '' END;


Из записи каталога pg_index извлекаются признак уникальности индекса (indisunique), признак того, что индекс построен в соответствии с описанием первичного ключа (indisprimary), а также массивы порядковых номеров атрибутов таблицы, по значениям которых строится индекс (indkey) и признаков порядка сортировки значений атрибутов в индексе (indoption).


Из записи каталога с описанием метода доступа индекса pg_am извлекается признак пригодности включенных в индекс данных для сортировки (amcanorder) и название или тип метода доступа индекса (amname).

Другими словами, признак amcanorder указывает на то, можно ли установить порядок сортировки значений входящих в индекс атрибутов. Если amcanorder = true, то порядок сортировки может быть указан, иначе нет. Из этого же рисунка виден смысл значений массива indoption — если правый бит двоичной формы значения содержит 1B, то значение соответствующего атрибута сортируются в убывающем порядке, в противном случае — в возрастающем порядке.


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



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


Текстовая версия таблицы на рисунке
Название индекса Метод ? Уникальный ? первичный ключ Атрибуты, входящие в индекс
xie1street btree f f wcrccode ASC, localityid ASC, streettypeacrm ASC, streetname ASC
xie2stree btree f f wcrccode ASC, localityid ASC, streetname ASC
xie3street btree f f streetname ASC
xie9street btree f f wcrccode ASC, localityid ASC, streetname DESC
xpkstreet btree t t wcrccode ASC, localityid ASC, streetid ASC
xts1street gin f f streettsvector
xts2street gin f f streettsvector


Версия без курсора


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


  • размножение записей с помощью generate_subscripts;
  • последующая группировка записей;
  • создание списка атрибутов индексов при помощи функции STRING_AGG с опцией ORDER BY.


исходный код оператора на рисунке
SELECT inxcls.relname AS r_IndexName ,inxam.amname AS r_IndexType,
       inx.indisunique AS r_isUnique,inx.indisprimary AS r_isPrimary,
       STRING_AGG(attr.attname||
             CASE inxam.amcanorder  WHEN true THEN CASE inx.indoption[inx.No] & 1 
                                                      WHEN 1 THEN ' DESC' 
                                                      ELSE ' ASC'  END 
                                               ELSE '' END, 
		   c_Delimiter ORDER BY inx.No)
     FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,i.indisprimary,
                  i.indkey::SMALLINT[],i.indoption::SMALLINT[],
                 generate_subscripts(i.indkey, 1) as No 
             FROM pg_index i) inx	
               INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid 
               INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
               INNER JOIN  pg_class tbl ON inx.indrelid=tbl.oid
               INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
               INNER JOIN pg_attribute attr ON attr.attrelid=tbl.OID 
                                            AND attr.attnum=inx.indkey[inx.No]
     WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
           AND LOWER(tbl.relname)=LOWER(a_TableName)
     GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
     ORDER BY inxcls.relname;



Время выполнения обеих версий функций у меня совпало на вывод данных в таблице результатов ушло 20 ms.


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

Смотри также первую , третью и четвертую части статьи.



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



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


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

RETURNS  TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE	v_Scale			INTEGER;		/* Масштаб колонки */
	v_ConstraintRec		RECORD;			/* Данные об ограничении*/
	v_TableOID		INTEGER;		/* OID таблицы*/
	v_ConstraintOID		INTEGER;		/* OID ограничения*/
	v_ConstraintKeyNos	SMALLINT[];	/* */
	v_ConstraintName	name;		/* Название ограничения */
	v_ConstraintType	name;		/* Буквенное обозначение типа ограничения */
	v_isUnique	 	BOOLEAN;		/* Признак уникальности ограничения*/
	v_isPrimary	BOOLEAN;	/* Признак того что индекс представляет Primary KEY таблицы*/
	v_AttributeNum		INTEGER;		/* Порядковый номер аттрибута*/
	v_AttributeName		name;		/* Наименование аттрибута*/
	v_ConstraintKeyNames	TEXT;	/* Строка со списком аттрибутов ограничения*/
	v_RefTableOID		INTEGER;	/* OID таблицы, на которую ссылается ограничение */
	v_RefTableName		name;/* Название таблицы, на которую ссылается ограничение */
	v_RefTableKeyNos	SMALLINT[];		/* */
	v_RefTableKeyNames	TEXT;	/* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
	v_ConstraintSource	TEXT;	/* Строка с описанием уловия CHECK*/
	c_Delimiter		CONSTANT VARCHAR(2):=',';
	--******************************************************************************************************		
BEGIN		
	FOR v_ConstraintRec IN SELECT con.oid AS ConstraintOID ,tbl.OID AS TableOID,
					con.conname AS ConstraintName,
					con.contype AS ConstraintType,
					con.conkey AS ConstraintKeyNos,
					reftbl.OID AS RefTableOID,
					reftbl.relname AS RefTableName,
					con.confkey AS RefTableKeyNos,
					con.consrc AS ConstraintSource 
				FROM pg_constraint con 
					INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
					INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
					LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
				WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
					AND LOWER(tbl.relname)=LOWER(a_TableName)
				ORDER BY con.contype DESC,con.conname 
	LOOP
		v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
		v_TableOID:=v_ConstraintRec.TableOID;
		v_ConstraintName:=v_ConstraintRec.ConstraintName;
		v_ConstraintType:=v_ConstraintRec.ConstraintType;
		v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
		v_RefTableOID:=v_ConstraintRec.RefTableOID;
		v_RefTableName:=v_ConstraintRec.RefTableName;
		v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
		v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
		v_ConstraintKeyNames:='';
		FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
		LOOP
			SELECT INTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
				FROM pg_attribute attr 
				WHERE attr.attrelid=v_TableOID 
					AND attr.attnum=v_AttributeNum;
			v_ConstraintKeyNames:=v_ConstraintKeyNames||
					CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END 
						||v_AttributeName;	
		END LOOP;		
		v_RefTableKeyNames:='';
		IF v_RefTableKeyNos IS NOT NULL THEN
			FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
			LOOP
				SELECT INTO v_AttributeName 
						attr.attname::VARCHAR(100) AS r_AttributeName
					FROM pg_attribute attr 
					WHERE attr.attrelid=v_RefTableOID AND attr.attnum=v_AttributeNum;
				v_RefTableKeyNames:=v_RefTableKeyNames||
					CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
 						||v_AttributeName;	
			END LOOP;		
		END IF;	
		RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
						v_RefTableName,v_RefTableKeyNames,
						v_ConstraintSource;					
	END LOOP;			
	RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает возвращает ограничений таблицы	';


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

RETURNS  TABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE		
	--******************************************************************************************************
BEGIN		
	RETURN QUERY SELECT tc.r_ConstraintName::VARCHAR(256),
				tc.r_ConstraintType::VARCHAR(256),tc.r_ConstraintKeyNames::TEXT,
				tc.r_RefTableName::VARCHAR(256),tc.r_RefTableKeyNames::TEXT,
				tc.r_ConstraintSource::TEXT
		FROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает возвращает ограничений таблицы	';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);



Создание версии функции admtf_Table_Constraintes без курсора


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

RETURNS  TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE	v_Scale			INTEGER;		/* Масштаб колонки */
	v_ConstraintRec		RECORD;			/* Данные об ограничении*/
	v_TableOID		INTEGER;		/* OID таблицы*/
	v_ConstraintOID		INTEGER;		/* OID ограничения*/
	v_ConstraintKeyNos	SMALLINT[];	/* */
	v_ConstraintName	name;		/* Название ограничения */
	v_ConstraintType	name;		/* Буквенное обозначение типа ограничения */
	v_isUnique	 	BOOLEAN;		/* Признак уникальности ограничения*/
	v_isPrimary	 BOOLEAN;/* Признак того что индекс представляет Primary KEY таблицы*/
	v_AttributeNum		INTEGER;		/* Порядковый номер аттрибута*/
	v_AttributeName		name;		/* Наименование аттрибута*/
	v_ConstraintKeyNames TEXT;		/* Строка со списком аттрибутов ограничения*/
	v_RefTableOID		INTEGER;	/* OID таблицы, на которую ссылается ограничение */
	v_RefTableName		name;/* Название таблицы, на которую ссылается ограничение */
	v_RefTableKeyNos	SMALLINT[];		/* */
	v_RefTableKeyNames	TEXT;/* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
	v_ConstraintSource	TEXT;			/* Строка с описанием уловия CHECK*/
	c_Delimiter		CONSTANT VARCHAR(2):=',';
	--******************************************************************************************************		
BEGIN		
	FOR v_ConstraintRec IN SELECT con.oid AS ConstraintOID ,
					tbl.OID AS TableOID,
					con.conname AS ConstraintName,
					con.contype AS ConstraintType,
					con.conkey AS ConstraintKeyNos,
					reftbl.OID AS RefTableOID,
					reftbl.relname AS RefTableName,
					con.confkey AS RefTableKeyNos,
					con.consrc AS ConstraintSource 
				FROM pg_constraint con 
					INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
					INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
					LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
				WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
					 AND LOWER(tbl.relname)=LOWER(a_TableName)
				ORDER BY con.contype DESC,con.conname 
	LOOP
		v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
		v_TableOID:=v_ConstraintRec.TableOID;
		v_ConstraintName:=v_ConstraintRec.ConstraintName;
		v_ConstraintType:=v_ConstraintRec.ConstraintType;
		v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
		v_RefTableOID:=v_ConstraintRec.RefTableOID;
		v_RefTableName:=v_ConstraintRec.RefTableName;
		v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
		v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
		v_ConstraintKeyNames:='';
		FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
		LOOP
			SELECT INTO v_AttributeName 
					attr.attname::VARCHAR(100) AS r_AttributeName
				FROM pg_attribute attr 
				WHERE attr.attrelid=v_TableOID 
					AND attr.attnum=v_AttributeNum;
			v_ConstraintKeyNames:=v_ConstraintKeyNames||
					CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END
										||v_AttributeName;	
		END LOOP;		
		v_RefTableKeyNames:='';
		IF v_RefTableKeyNos IS NOT NULL THEN
			FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
			LOOP
				SELECT INTO v_AttributeName 
						attr.attname::VARCHAR(100) AS r_AttributeName
					FROM pg_attribute attr 
					WHERE attr.attrelid=v_RefTableOID
						AND attr.attnum=v_AttributeNum;
				v_RefTableKeyNames:=v_RefTableKeyNames||
					CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
 										||v_AttributeName;	
			END LOOP;		
		END IF;	
		RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
						v_RefTableName,v_RefTableKeyNames,
						v_ConstraintSource;					
	END LOOP;			
	RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает возвращает ограничений таблицы	';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************/
/*  Функция возвращает список ограничений таблицы								*/
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
	(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных	*/
	a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
 )										 

RETURNS  TABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE		
	--******************************************************************************************************
BEGIN		
	RETURN QUERY SELECT tc.r_ConstraintName::VARCHAR(256),
				tc.r_ConstraintType::VARCHAR(256),
				tc.r_ConstraintKeyNames::TEXT,
				tc.r_RefTableName::VARCHAR(256),
				tc.r_RefTableKeyNames::TEXT,
				tc.r_ConstraintSource::TEXT
		FROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает возвращает ограничений таблицы	';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);



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


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

RETURNS  TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
$BODY$
DECLARE
	c_IndexKind	CONSTANT CHAR:='i';
	v_IndexRec		RECORD;			/* Данные об индексе*/
	v_Scale			INTEGER;		/* Масштаб колонки */
	v_TableOID		INTEGER;		/* OID таблицы*/
	v_IndexOID		INTEGER;		/* OID индекса*/
	v_IndexKeyNos		SMALLINT[];		/* */
	v_IndexName		NAME;		/* Название индекса */
	v_IndexAMName		NAME;		/* Наименование типа индекса (метода доступа) */
	v_isUnique	 	BOOLEAN;		/* Признак уникальности индекса*/
	v_isPrimary	 	BOOLEAN;		/* Признак того что индекс представляет Primary KEY таблицы*/
	v_AttributeNum		INTEGER;		/* Порядковый номер атрибута*/
	v_AttributeName		NAME;		/* Наименование атрибута*/
	v_IndexKeyNames		TEXT;			/* Строка со списком атрибутов индекса*/
	c_Delimiter		CONSTANT VARCHAR(2):=',';
	--******************************************************************************************************		
BEGIN		
	FOR v_IndexRec IN SELECT inxcls.oid AS IndexOID,tbl.oid AS TableOID,
						inxcls.relname AS IndexName,inxam.amname AS IndexAMName,
						inx.indisunique AS isUnique,inx.indisprimary isPrimary,
						inx.indkey::SMALLINT[] AS IndexKeyNos
			FROM pg_index inx 
				INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid 
				INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
				INNER JOIN  pg_class tbl ON inx.indrelid=tbl.oid
				INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
			WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
				AND inxcls.relkind=c_IndexKind
				AND tbl.relname=LOWER(a_TableName)
			ORDER BY inxam.amname, inxcls.relname LOOP
		v_IndexOID:=v_IndexRec.IndexOID;
		v_TableOID:=v_IndexRec.TableOID;
		v_IndexName:=v_IndexRec.IndexName;
		v_IndexAMName:=v_IndexRec.IndexAMName;
		v_isUnique:=v_IndexRec.isUnique;
		v_isPrimary:=v_IndexRec.isPrimary;
		v_IndexKeyNos:=v_IndexRec.IndexKeyNos;
		v_IndexKeyNames:='';
		FOREACH v_AttributeNum IN ARRAY v_IndexKeyNos
		LOOP
			SELECT INTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
				FROM pg_attribute attr 
				WHERE attr.attrelid=v_TableOID AND attr.attnum=v_AttributeNum;
			v_IndexKeyNames:=v_IndexKeyNames||
						CASE WHEN v_IndexKeyNames='' THEN '' 
									ELSE c_Delimiter||' ' END ||
									v_AttributeName;
		END LOOP;		
		RETURN QUERY SELECT v_IndexName,v_IndexAMName,v_isUnique,
					v_isPrimary,v_IndexKeyNames;					
	END LOOP;			
	RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список индексов таблицы	';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

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

RETURNS  TABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
$BODY$
DECLARE
	--******************************************************************************************************		
BEGIN		
	RETURN QUERY SELECT ti.r_IndexName::VARCHAR(256),
						ti.r_IndexType::VARCHAR(256),
						ti.r_isUnique::BOOLEAN,
						ti.r_isPrimary::BOOLEAN, 
						ti.r_IndexKeyNames::TEXT
		FROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список индексов таблицы	';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));	



Создание версии функции admtf_Table_Indexes без курсора


Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************/
/*  Функция возвращает список индексов таблицы								*/
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Indexes
		(a_SchemaName	NAME default 'public',	/* название схемы базы данных		*/
		a_TableName	NAME default NULL	/* Название таблицы */
 )										 
RETURNS  TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
$BODY$
DECLARE
	c_IndexKind	CONSTANT CHAR:='i';
	c_Delimiter		CONSTANT VARCHAR(2):=', ';
	--******************************************************************************************************		
BEGIN		
	RETURN QUERY SELECT inxcls.relname AS r_IndexName,
				inxam.amname AS r_IndexType,
				inx.indisunique AS r_isUnique,
				inx.indisprimary r_isPrimary,
			STRING_AGG(attr.attname||CASE inxam.amcanorder  WHEN true 
					THEN CASE inx.indoption[inx.No] & 1 
							WHEN 1 THEN ' DESC' 
								ELSE ' ASC' 
							END 
					ELSE '' 
				END, 
					c_Delimiter ORDER BY inx.No)
		FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,
					i.indisprimary,i.indkey::SMALLINT[], i.indoption::SMALLINT[],
					generate_subscripts(i.indkey, 1) as No 
				FROM pg_index i) inx 
			INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid 
			INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
			INNER JOIN  pg_class tbl ON inx.indrelid=tbl.oid
			INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
			INNER JOIN pg_attribute attr ON attr.attrelid=tbl.OID 
					AND attr.attnum=inx.indkey[inx.No]
		WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
			AND inxcls.relkind=c_IndexKind
			AND tbl.relname=LOWER(a_TableName)
		GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
		ORDER BY inxcls.relname; 			
	RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список индексов таблицы	';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************/
/*  Функция возвращает список индексов таблицы								*/
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Indexes
		(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных*/
		a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
 )										 
RETURNS  TABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
$BODY$
DECLARE
	--******************************************************************************************************		
BEGIN		
	RETURN QUERY SELECT ti.r_IndexName::VARCHAR(256),
					ti.r_IndexType::VARCHAR(256),
					ti.r_isUnique::BOOLEAN,
					ti.r_isPrimary::BOOLEAN,
					ti.r_IndexKeyNames::TEXT
		FROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список индексов таблицы	';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));


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


Функции для документирования баз данных PostgreSQL. Часть первая;
Функции для документирования баз данных PostgreSQL. Часть третья.
Функции для документирования баз данных PostgreSQL. Окончание(часть четвертая).
Tags:
Hubs:
+10
Comments 3
Comments Comments 3

Articles