Это третья часть статьи, которая описывает пользовательские функции для работы с системными каталогами: pg_class, pg_attribute, pg_constraints и т.д.
Первая ^[1] и вторая ^[2] части статьи опубликованы ранее.

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

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

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

Рис. 2. Функции, от которых зависит admtf_Table_Sequences функции.

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

№	Название	Назначение
1	admtf_Sequence_Features	Функция возвращает список характеристик последовательности таблицы
2	admtf_Table_Sequences	Функция возвращает список последовательностей таблицы базы данных и их характеристик

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

Функция admtf_Sequence_Features возвращает список характеристик последовательности (SEQUENCE) базы данных. Исходный код можно посмотреть и скачать здесь ^[4].

Функция admtf_Sequence_Features возвращает список характеристик последовательности (SEQUENCE) базы данных

.

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

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

Замечание 6

В 10 версии PostgreSQL разделили характеристики последовательности и характеристики ее состояний. Для этого введен каталог pg_sequence с характеристиками последовательности, содержащий начальное значение (start_value), приращение (increment_by) и максимальное значение(max_value) последовательности. Последнее возвращенное последовательностью значение (last_value) так и оставили в «таблице» с названием последовательности.

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

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

Запись о последовательности в каталоге pg_class отличается от записи о таблице значением вида отношения (relkind='S').

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

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

Название	Комментарий	Текущее	Начало	Приращение	Конец
kr_road_network _vertices_pgr_id _seq	Последо-вательность	138023	1	1	9223372036854775807

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

Функция admtf_Table_Sequences возвращает список последовательностей (SEQUENCE) таблицы базы данных, генерирующих значения ее полей, и характеристики этих последовательностей.Исходный код можно посмотреть и скачать здесь ^[5], а здесь приведена версия функции, в которой не используется курсор ^[6].

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

a_SchemaName

).

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

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

Таблица 13. Атрибуты каталогаpg_depend, необходимые для реализации функции.

Название	Описание
objid	OID последовательности в каталоге pg_class
objsubid	Это поле содержит ноль
refobjid	OID таблицы, в заполнении полей которой используется последовательность
refobjsubid	Номер атрибута таблицы, значения которого заполняются с помощью последовательности

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

Для определения атрибута таблицы, значения которой заполняются при помощи последовательности, функция обращается к каталогу pg_attribute по условию: attrelid= refobjid AND attnum= refobjsubid. (В этом условии справа от знака равенства указаны названия атрибутов каталога pg_depend).

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

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

Название	Комментарий	Начало	При- ращение	Конец	Поле
kr_road_network _vertices_pgr_id _seq	Последователь-ность, генерирующая значения поля id	1	1	9223372036854775807	id

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

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

В приведенной реализации функции с помощью оконной функции RANK() OVER (PARTITION BY pseq.relname) вычисляется порядковый номер последовательности, используемой для заполнения исходной таблицы.

Замечание 7

.

Эта версия функции не возвращает последнее сгенерированное последовательностью значение (last_value).

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

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

Функция admtf_Table_InheritanceChildrens возвращает список характеристик унаследованных таблиц (INHERITS) таблицы базы данных. Исходный код можно посмотреть и скачать здесь ^[7].

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

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

Таблица 15. Атрибуты каталогаpg_depend, необходимые для реализации функции.

Название	Описание
objid	OID унаследованной таблицы в каталоге pg_class
refobjid	OID исходной таблицы

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

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

Название	Комментарий	Атрибуты	? первичный ключ	? индексы	? потомки	Число записей
np_house 04201 000000	Дома в населенных пунктах (Ачинский район)	15	f	f	f	5651
np_house 4208 000000	Дома в населенных пунктах (Боготольский район)	15	f	f	f	4314

Количество записей в порожденной таблицы выбирается из атрибута reltuple каталога pg_class. И хотя это значение часто в точности совпадает с действительным числом записей в таблице, все же это оценочное значение. А значит может возникнуть желание получить в результате точное значение. Например, так как показано на рисунке.

Но, во-первых, для того чтобы выполнить это утверждение в тексте функция admtf_Table_InheritanceChildrens придется использовать курсор.

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

Поэтому функция имеет еще одни необязательный параметр – режим получения количества записей таблицы (a_Mode), который принимает значения «оценочно» (estimate) или «точно» (exactly).

Дополнительно создана функция admfn_Table_RowCount ^[8], возвращающая точное число записей таблицы, а в списке возвращаемых значений SELECT атрибут reltuple заменен следующей конструкцией.

В результате функция возвращает оценочное значение показателя «число записей таблицы», если параметром a_Mode не задано требование возвращать точное значение.

Структура функции, возвращающей список характеристик атрибута таблицы

Рис. 3. Функции, которые вызывает admtf_Attribute_Features.

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

№	Название	Назначение
1	admtf_Attribute_PKFeatures	Функция возвращает признак присутствия атрибута в первичном ключе (PRIMARY KEY), а также некоторые его характеристики в качестве части этого ключа.
2	admtf_Attribute_FKFeatures	Функция возвращает признак присутствия атрибута во внешнем ключе ключе (FOREIGN KEY), а также некоторые его характеристики в качестве части этого ключа.
3	admtf_Attribute_Features	Функция возвращает список характеристик атрибута таблицы.

Функция admtf_Attribute_PKFeatures — — присутствует ли атрибут в первичном ключе

Функция admtf_Attribute_PKFeatures возвращает признак присутствия атрибута таблицы в первичном ключе (PRIMARY KEY) таблицы, и, если он присутствует, каков его порядковый номер в этом ключе, т.к. первичный ключ может быть составным.
Исходный код можно посмотреть и скачать здесь ^[9].

В качестве параметров функция принимает OID исходной таблицы (a_TableOID) и порядковый номер искомого атрибута в ней (a_AttributeNo).

Нужные данные функция извлекает из записи каталога pg_constraint, содержащей ограничения (CONSTRAINT) исходной таблицы, в том числе, ограничение первичного ключа. OID искомой таблицы хранится в поле conrelid, описание первичного ключа хранится в записи, в которой поле contype содержит значение ''p'

.

Поле conkey, найденной таким образом записи, содержит массив порядковых номеров атрибутов, которые составляют первичный ключ. Поэтому, для того чтобы проверить присутствие исходного атрибута в первичном ключе, достаточно вычислить логическое выражение ARRAY[a_AttributeNo]<@conkey.

Если атрибут присутствует в первичном ключе, то далее в цикле вычисляется его порядковый номер.

Функция admtf_Attribute_FKFeatures -присутствует ли атрибут во внешнем ключе

Функция admtf_Attribute_FKFeatures возвращает признак присутствия атрибута таблицы в одном или нескольких внешних ключах (FOREIGN KEY) таблицы, и, если он присутствует, каковы его порядковые номера в этих ключах, т.к. внешний ключ может быть составным.
Исходный код можно посмотреть и скачать здесь ^[10].

В качестве параметров функция принимает OID исходной таблицы (a_TableOID) и порядковый номер искомого атрибута в ней (a_AttributeNo).

Нужные данные функция извлекает из записи каталога pg_constraint, содержащей ограничения (CONSTRAINT) исходной таблицы, в том числе, в том числе, ограничения внешних ключей. OID искомой таблицы хранится в поле conrelid, описание первичного ключа хранится в записи, в которой поле contype содержит значение ''f'

.

Поле conkey, найденной таким образом записи, содержит массив порядковых номеров атрибутов, которые составляют внешний ключ. Поэтому, для того чтобы проверить присутствие исходного атрибута во внешнем ключе, достаточно вычислить логическое выражение ARRAY[a_AttributeNo]<@conkey.

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

Наименования таблиц извлекаются из записи каталога pg_class по идентификатору (OID) извлеченному из поля confrelid записи о внешнем ключе.

Для получения наименования атрибута внешней таблицы используется массив порядковых номеров из поля

confkey

(он отличается о рассмотренного выше массива буквой «f» в названии). Из этого массива извлекается порядковый номер атрибута внешней таблицы, которому соответствует внешний атрибут. По этому порядковому номеру атрибута внешней таблицы и ее OID, находится в каталоге pg_attribute находится записи о описание атрибута и извлекается его название.

Функция admtf_Attribute_Features — список характеристик атрибута таблицы

Функции admtf_Attribute_Features возвращает список следующих характеристик атрибута таблицы.Исходный код можно посмотреть и скачать здесь ^[11].

№	Название	Тип	Назначение
1	AttributeName	name	Название исходного атрибута.
2	UserTypeName	VARCHAR(256)	Пользовательский тип исходного атрибута
3	TypeName	VARCHAR(256)	Базовый тип исходного атрибута
4	isNotNULL	BOOLEAN	? Допустимость значения NULL
5	isAttributePK	BOOLEAN	? участие в PK
6	ColumnPKNo	SMALLINT	Порядковый номер атрибута в PK
7	Description	TEXT	Комментарий к исходному атрибуту
8	isAttributeFK	BOOLEAN	? участие в FK
9	FKeyName	name[]	Массив названий ограничений таблицы, в которых определен внешний ключ
10	ColumnFKNo	SMALLINT[]	Массив порядковых номеров атрибута во внешних ключах таблицы
11	FKTableName	name[]	Массив таблиц, на которые ссылаются внешние ключи
12	FKTableColumnName	name[]	Массив названий атрибутов в внешних таблицах, соответствующих исходному атрибуту

В качестве параметров функция принимает OID исходной таблицы (a_TableOID) и порядковый номер искомого атрибута в ней (a_AttributeNo).
Значения полей AttributeName и isNotNULL извлекаются из записи каталога pg_attribute, соответствующей значениям входных параметров.

Значения полей isAttributePK и ColumnPKNo возвращаются функцией admtf_Attribute_PKFeatures.

Значения полей isAttributeFK, FKeyName, ColumnFKNo, FKTableName, FKTableColumnName возвращаются функцией admtf_Attribute_FKFeatures.

Вызов функции admtf_Attribute_Features((SELECT OID FROM pg_class WHERE relname='street'),2::SMALLINT) приведет к следующему результату.

Таблица 18. Результат выполнения функции admtf_Attribute_Features

AttributeName	UserTypeName	TypeName	isNotNULL	isAttributePK	ColumnPKNo
localityid	localityid	integer	integer	integer	integer

Description	isAttributeFK	FKeyName	ColumnFKNo	FKTableName	FKTableColumnName
ИД населенного пункта	t	{fk_street_locality}	{2}	{locality}	{localityid}

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

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

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

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

RETURNS  TABLE (rs_SequenceName NAME,rs_SequenceDescription TEXT,rs_NumberOfAttribute INTEGER,rs_SequenceLastValue BIGINT,
		rs_SequenceStartValue BIGINT,rs_SequenceIncrementBy BIGINT,rs_SequenceMaxValue BIGINT) AS
$BODY$
DECLARE
	c_SequenceKind	CONSTANT CHAR:='S';
	v_SequenceOID		OID;		/* ИД последовательности */
	v_SequenceName		NAME;	/* Название последовательности */
	v_SequenceDescription	TEXT;		/* Описание последовательности */
	v_SequenceStartValue	BIGINT;		/* Начальное значение последовательности */
	v_SequenceIncrementBy	BIGINT;		/* Приращение последовательности */
	v_SequenceMaxValue	BIGINT;		/* Максимальное значение последовательности */
	v_SequenceLastValue	BIGINT;		/* Максимальное значение последовательности */
	v_SequenceNumberOfRowCalc	INTEGER;		/* Число записей в таблице */

	--************************************************************************		
BEGIN		
	SELECT INTO rs_SequenceName,rs_SequenceDescription,rs_NumberOfAttribute
		tbl.relname,
		COALESCE(dsc.description,'Последовательность') AS r_SequenceDescription,
		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_SequenceKind
		AND tbl.relname =LOWER(a_SequenceName);
	IF FOUND THEN		
		EXECUTE 'SELECT last_value,start_value,increment_by,max_value 
				FROM '||LOWER(a_SchemaName)||'.'||LOWER(a_SequenceName)  INTO
					v_SequenceLastValue,v_SequenceStartValue,
					v_SequenceIncrementBy,v_SequenceMaxValue ;				
		RETURN QUERY SELECT rs_SequenceName,rs_SequenceDescription,
					rs_NumberOfAttribute,v_SequenceLastValue,
					v_SequenceStartValue,v_SequenceIncrementBy,
					v_SequenceMaxValue;	
		END IF;	
	RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Sequence_Features(a_SchemaName NAME,a_SequenceName NAME) IS 'Функция возвращает список характеристик последовательности, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Sequence_Features (a_SchemaName VARCHAR(256),a_SequenceName VARCHAR(256));
/****************************************************************************/
/*  Функция возвращает список характеристик последовательности, принадлежащей схеме */
/****************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Sequence_Features
	(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных		*/
	a_SequenceName	VARCHAR(256) default NULL	/* Название последовательности */
 )										 
RETURNS  TABLE (rs_SequenceName VARCHAR(256),rs_SequenceDescription TEXT,
rs_NumberOfAttribute INTEGER,rs_SequenceLastValue BIGINT,
rs_SequenceStartValue BIGINT,rs_SequenceIncrementBy BIGINT,
rs_SequenceMaxValue BIGINT) AS
$BODY$
DECLARE
	c_SequenceKind	CONSTANT CHAR:='S';
--********************************************************		
BEGIN		
	RETURN QUERY SELECT sf.rs_SequenceName::VARCHAR(256),
				sf.rs_SequenceDescription::TEXT,
				sf.rs_NumberOfAttribute::INTEGER,
				sf.rs_SequenceLastValue::BIGINT,
				sf.rs_SequenceStartValue::BIGINT,
				sf.rs_SequenceIncrementBy::BIGINT,
				sf.rs_SequenceMaxValue::BIGINT
	FROM admtf_Sequence_Features(a_SchemaName::NAME,a_SequenceName::NAME) sf;		
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Sequence_Features(a_SchemaName VARCHAR(256),a_SequenceName VARCHAR(256)) IS 'Функция возвращает список характеристик последовательности, принадлежащей схеме';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Sequence_Features('public'::VARCHAR(255),'k_dorogi_dijkstra_seq_seq'::VARCHAR(255));
SELECT * FROM admtf_Sequence_Features('public'::NAME,'kr_road_network_vertices_pgr_id_seq'::NAME);

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

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

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

RETURNS  TABLE (r_SequenceNumber SMALLINT,r_SequenceName NAME,
r_SequenceSchemaName NAME,r_SequenceDescription TEXT,
r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
r_SequenceMaxValue BIGINT,r_DependType NAME,
r_RefTableName NAME,r_RefTableSchemaName NAME,
r_RefAttributeName NAME) AS
$BODY$
DECLARE
	v_TableOID                    INTEGER;/* OID таблицы*/
	v_Sequence                    RECORD;/* Запись о последовательности*/
	v_SequenceOID               INTEGER;/* OID ограничения*/
	v_SequenceName             NAME;    /* Название последовательности */
	v_SequenceSchemaName   NAME;   /* Название схемы последовательности */
	v_SequenceDescription      TEXT;	   /* Описание последовательности */
	v_SequenceStartValue	       BIGINT;  /* Начальное значение последовательности */
	v_SequenceIncrementBy	BIGINT; /* Приращение последовательности */
	v_SequenceMaxValue	       BIGINT;  /* Максимальное значение последовательности */
	v_DependcyType		NAME; /* Буквенное обозначение типа зависимости */
                                                          /* таблицы от последовательности */
	v_AttributeName		NAME;	/* Наименование аттрибута*/
	v_SequenceNumber 	SMALLINT;	/* Порядковый номер последовательности*/	
	c_Delimiter		CONSTANT VARCHAR(2):=',';
	--*********************************************************************		
BEGIN
	v_SequenceNumber:=0;
	FOR v_Sequence IN SELECT pseq.relname AS SequenceName,
					snsp.nspname AS SequenceSchemaName,
					COALESCE(dsc.description,'Последовательность, генерирующая 
значения поля '||da.attname) AS SequenceDescription,
					d.depType AS DependcyType,da.attname AS AttributeName
			FROM pg_depend d 
				INNER JOIN pg_class    pseq ON  d.objid = pseq.oid
				INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid 
				LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid
								AND dsc.objsubid=0
				INNER JOIN pg_class tbl ON  d.refobjid = tbl.oid
				INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
				INNER JOIN pg_attribute da ON  da.attrelid= d.refobjid
					AND d.refobjsubid=da.attnum
			WHERE tbl.relkind = 'r' AND pseq.relkind = 'S' 
				AND LOWER(nsp.nspname)=LOWER(a_SchemaName)
				 AND LOWER(tbl.relname)=LOWER(a_TableName)
			ORDER BY pseq.relname
	LOOP
		v_SequenceNumber:=v_SequenceNumber+1;
		v_SequenceName:=v_Sequence.SequenceName;
		v_SequenceSchemaName:=v_Sequence.SequenceSchemaName;
		v_DependcyType:=v_Sequence.DependcyType;
		v_AttributeName:=v_Sequence.AttributeName;
		v_SequenceDescription:=v_Sequence.SequenceDescription;
		SELECT INTO v_SequenceStartValue,v_SequenceIncrementBy,
			v_SequenceMaxValue
			rs_SequenceStartValue,rs_SequenceIncrementBy,
			rs_SequenceMaxValue
		FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName);
		RETURN QUERY SELECT v_SequenceNumber,v_SequenceName,
			v_SequenceSchemaName,v_SequenceDescription,
			v_SequenceStartValue,v_SequenceIncrementBy,
			v_SequenceMaxValue,v_DependcyType,
			a_TableName,a_SchemaName,v_AttributeName;					
	END LOOP;			
	RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/**********************************************************************/
/*  Функция возвращает список последовательностей, от которых зависит таблица  */
/**********************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Sequences
	(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных		*/
	a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
 )										 
RETURNS  TABLE (r_SequenceNumber SMALLINT,r_SequenceName VARCHAR(256),
r_SequenceSchemaName VARCHAR(256),r_SequenceDescription TEXT,
r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
r_SequenceMaxValue BIGINT,r_DependType VARCHAR(256),
r_RefTableName VARCHAR(256),r_RefTableSchemaName VARCHAR(256),
r_RefAttributeName VARCHAR(256)) AS
$BODY$
DECLARE
	c_Delimiter		CONSTANT VARCHAR(2):=',';
--******************************************************		
BEGIN		
	RETURN QUERY SELECT ts.r_SequenceNumber::SMALLINT,
				ts.r_SequenceName::VARCHAR(256),
				ts.r_SequenceSchemaName::VARCHAR(256)	,
				ts.r_SequenceDescription::TEXT,
				ts.r_SequenceStartValue::BIGINT,
				ts.r_SequenceIncrementBy::BIGINT,
				ts.r_SequenceMaxValue::BIGINT,
				ts.r_DependType::VARCHAR(256),
				ts.r_RefTableName::VARCHAR(256),
				ts.r_RefTableSchemaName::VARCHAR(256),
				ts.r_RefAttributeName::VARCHAR(256)
		FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME) ts;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_Sequences('public'::VARCHAR(255),'kr_road_network_vertices_pgr'::VARCHAR(255));
SELECT * FROM admtf_Table_Sequences('public'::NAME,'kr_road_network_vertices_pgr'::NAME);

Создание функции admtf_Table_Sequences без курсора (PostgreSQL 10)

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

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME);
/*********************************************************************/
/*  Функция возвращает список последовательностей, от которых зависит таблица */
/**********************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Sequences
	(a_SchemaName	NAME default 'public',	/* название схемы базы данных		*/
	a_TableName	NAME default NULL	/* Название таблицы */
 )										 
RETURNS  TABLE (r_SequenceNumber SMALLINT,r_SequenceName NAME,
			r_SequenceSchemaName NAME,r_SequenceDescription TEXT,
			r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
			r_SequenceMaxValue BIGINT,r_DependType NAME,
			r_RefTableName NAME,r_RefTableSchemaName NAME,
			r_RefAttributeName NAME) AS
$BODY$
DECLARE
	v_TableOID		INTEGER;		/* OID таблицы*/
	v_Sequence 		RECORD;			/* Запись о последовательности*/
	v_SequenceOID		INTEGER;		/* OID ограничения*/
	v_SequenceName		NAME;		/* Название последовательности */
	v_SequenceSchemaName	NAME;		/* Название схемы последовательности */
	v_SequenceDescription	TEXT;		/* Описание последовательности */
	v_SequenceStartValue	BIGINT;		/* Начальное значение последовательности */
	v_SequenceIncrementBy	BIGINT;		/* Приращение последовательности */
	v_SequenceMaxValue	BIGINT;		/* Максимальное значение последовательности */
	v_DependcyType		NAME;		/* Буквенное обозначение типа зависимости таблицы от последовательности */
	v_AttributeName		NAME;		/* Наименование аттрибута*/
	v_SequenceNumber 	SMALLINT;	/* Порядковый номер последовательности*/	
	c_Delimiter		CONSTANT VARCHAR(2):=',';
--******************************************************************		
BEGIN
	v_SequenceNumber:=0;
	FOR v_Sequence IN SELECT pseq.relname AS SequenceName,
					snsp.nspname AS SequenceSchemaName,
					COALESCE(dsc.description,'Последовательность, генерирующая
						 значения поля '||da.attname) AS SequenceDescription,
					d.depType AS DependcyType,da.attname AS AttributeName
		FROM pg_depend d 
			INNER JOIN pg_class    pseq ON  d.objid = pseq.oid
			INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid 
			LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid
					AND dsc.objsubid=0
			INNER JOIN pg_class tbl ON  d.refobjid = tbl.oid
			INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
			INNER JOIN pg_attribute da ON  da.attrelid= d.refobjid
					ND d.refobjsubid=da.attnum
		WHERE tbl.relkind = 'r' AND pseq.relkind = 'S' 
			AND LOWER(nsp.nspname)=LOWER(a_SchemaName) 
			AND LOWER(tbl.relname)=LOWER(a_TableName)
		ORDER BY pseq.relname
	LOOP
		v_SequenceNumber:=v_SequenceNumber+1;
		v_SequenceName:=v_Sequence.SequenceName;
		v_SequenceSchemaName:=v_Sequence.SequenceSchemaName;
		v_DependcyType:=v_Sequence.DependcyType;
		v_AttributeName:=v_Sequence.AttributeName;
		v_SequenceDescription:=v_Sequence.SequenceDescription;
		SELECT INTO v_SequenceStartValue,v_SequenceIncrementBy,v_SequenceMaxValue
			rs_SequenceStartValue,rs_SequenceIncrementBy,rs_SequenceMaxValue
			FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName);
		RETURN QUERY SELECT v_SequenceNumber,v_SequenceName,
				v_SequenceSchemaName,v_SequenceDescription,
				v_SequenceStartValue,v_SequenceIncrementBy,
				v_SequenceMaxValue,v_DependcyType,
				a_TableName,a_SchemaName,v_AttributeName;					
	END LOOP;			
	RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/**********************************************************************/
/*  Функция возвращает список последовательностей, от которых зависит таблица  */
/**********************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_Sequences
	(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных		*/
	a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
 )										 

RETURNS  TABLE (r_SequenceNumber SMALLINT,r_SequenceName VARCHAR(256),
		r_SequenceSchemaName VARCHAR(256),r_SequenceDescription TEXT,
		r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
		r_SequenceMaxValue BIGINT,r_DependType VARCHAR(256),
		r_RefTableName VARCHAR(256),r_RefTableSchemaName VARCHAR(256),
		r_RefAttributeName VARCHAR(256)) AS
$BODY$
DECLARE
	c_Delimiter		CONSTANT VARCHAR(2):=',';
--*******************************************************		
BEGIN		
	RETURN QUERY SELECT ts.r_SequenceNumber::SMALLINT,
				ts.r_SequenceName::VARCHAR(256),
				ts.r_SequenceSchemaName::VARCHAR(256),
				ts.r_SequenceDescription::TEXT,
				ts.r_SequenceStartValue::BIGINT,
				ts.r_SequenceIncrementBy::BIGINT,
				ts.r_SequenceMaxValue::BIGINT,
				ts.r_DependType::VARCHAR(256),
				ts.r_RefTableName::VARCHAR(256),
				ts.r_RefTableSchemaName::VARCHAR(256),
				ts.r_RefAttributeName::VARCHAR(256)
			FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME) ts;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;

SELECT * FROM admtf_Table_Sequences('public'::VARCHAR(255),
				'kr_road_network_vertices_pgr'::VARCHAR(255));
SELECT * FROM admtf_Table_Sequences('public'::NAME,
				'kr_road_network_vertices_pgr'::NAME);

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

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

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

RETURNS  BIGINT AS
$BODY$
DECLARE	v_TableNumberOfRowCalc	BIGINT;		/* Количество  */
	v_Found			BOOLEAN;
	--***********************************************************		
BEGIN
	IF a_SchemaName ~ E'^[a-z_0-9]+$' AND  a_TableName ~ E'^[a-z_0-9]+$' THEN	
		EXECUTE 'SELECT count(*) FROM ' ||a_SchemaName ||'.'|| a_TableName 
				INTO v_TableNumberOfRowCalc;
	ELSE 
		SELECT INTO v_Found true 
			FROM pg_class tbl 
				INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid 
			WHERE tbl.relkind='r' AND tbl.relname=a_TableName 
				AND nspc.nspname=a_SchemaName;
		IF FOUND THEN
 			EXECUTE 'SELECT count(*) FROM ' ||
				CASE WHEN a_SchemaName ~ E'^[a-z_0-9]+$'  THEN
					  	a_SchemaName 
					ELSE quote_ident(a_SchemaName) END 
					||'.'|| 
				CASE WHEN a_TableName ~ E'^[a-z_0-9]+$' THEN 
						a_TableName  
					ELSE quote_ident(a_TableName) END  
				INTO v_TableNumberOfRowCalc;
		ELSE
			SELECT INTO v_Found true 
				FROM pg_class tbl 
					INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid 
				WHERE tbl.relkind='r' AND LOWER(tbl.relname)= LOWER(a_TableName)  
					AND nspc.nspname=LOWER(a_SchemaName);
			IF FOUND THEN
				EXECUTE 'SELECT count(*) FROM ' || a_SchemaName	||'.'||a_TableName
					 INTO v_TableNumberOfRowCalc;
			END IF;			
		END IF;
	END IF;	
	RETURN v_TableNumberOfRowCalc;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admfn_Table_RowCount(a_SchemaName NAME,a_TableName NAME) IS 'Возвращает число строк в таблице';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admfn_Table_RowCount (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256));
/********************************************************************/
/*  Функция возвращает число строк в таблице                                                    */
/********************************************************************/
CREATE OR REPLACE FUNCTION admfn_Table_RowCount
	(a_SchemaName	VARCHAR(256) default 'public',/* название схемы базы данных */
	a_TableName	VARCHAR(256) default NULL   /* Название таблицы  */
 )										 
RETURNS  BIGINT AS
$BODY$
DECLARE	v_TableNumberOfRowCalc	BIGINT;		/* Количество  */
--*********************************************************		
BEGIN		
	RETURN admfn_Table_RowCount(a_SchemaName::NAME,a_TableName::NAME);
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admfn_Table_RowCount(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS 'Возвращает число строк в таблице';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECt admfn_Table_RowCount('public'::NAME,'Street'::NAME);
SELECt admfn_Table_RowCount('public'::VARCHAR(256),'Street'::VARCHAR(256));

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

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

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_InheritanceChildrens (a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10));
/************************************************************/
/*  Функция возвращает список характеристик унаследованных таблиц */
/************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens
	(a_SchemaName		NAME default 'public',	/* название схемы базы данных	*/
	a_TableName		NAME default NULL,	/* Название таблицы */
	a_Mode			VARCHAR(10) default 'estimate' 
                                           /*Режим вычисления количества записей в таблице*/
 )										 
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';
	c_ExactlyMode		CONSTANT VARCHAR(10):='exactly';
	c_EstimateMode		CONSTANT VARCHAR(10):='estimate';
	v_TableOID		OID;		/* ИД таблицы */
	v_SchemaName		NAME;	/* Название схемы таблицы */
	v_TableName		NAME;	/* Название таблицы */
	v_TableDescription	TEXT;		/* Описание таблицы */
	v_TableNumberOfRowCalc		INTEGER;		/* Число записей в таблице */
	v_InheritanceRECORD	RECORD;		/* Запись унаследованной таблицы */
	v_InheritanceOID	OID;		/* ИД унаследованной таблицы */
BEGIN
	RETURN QUERY SELECT  rtbl.relname,rdsc.description,rtbl.relnatts::INTEGER,
				rtbl.relchecks::INTEGER,rtbl.relhaspkey,rtbl.relhasindex,
				rtbl.relhassubclass,
				CASE WHEN a_Mode=c_ExactlyMode THEN 
					admfn_Table_RowCount(rnspc.nspname,rtbl.relname)::INTEGER  
					ELSE rtbl.reltuples::INTEGER END
				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
					INNER JOIN pg_depend dp ON tbl.oid=dp.refobjid
					INNER JOIN pg_class rtbl ON rtbl.OID=dp.objid
					INNER JOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oid 
					LEFT OUTER JOIN pg_Description rdsc ON rtbl.oid=rdsc.objoid
									AND rdsc.objsubid=0
				WHERE nspc.nspname=LOWER(a_SchemaName) 
					AND tbl.relkind=c_TableKind AND rtbl.relkind=c_TableKind
					AND tbl.relname =LOWER(a_TableName)
				ORDER BY rtbl.relname;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10)) IS 'Возвращает список характеристик унаследованных таблиц';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Table_InheritanceChildrens (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_TableName NAME,a_Mode VARCHAR(10));
/************************************************************************/
/*  Функция возвращает список характеристик унаследованных таблиц                       */
/************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens
	(a_SchemaName		VARCHAR(256) default 'public',/* название схемы базы данных	*/
	a_TableName		VARCHAR(256) default NULL,/* Название таблицы */
	a_Mode			VARCHAR(10) default 'estimate'
                                                           /*Режим вычисления количества записей в таблице*/
)										 

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';
BEGIN
	RETURN QUERY SELECT tic.rs_TableName::VARCHAR(256),tic.rs_TableDescription::TEXT,
				tic.rs_NumberOfAttribute::INTEGER,tic.rs_NumberOfChecks::INTEGER,
				tic.rs_hasPKey::BOOLEAN,tic.rs_hasIndex::BOOLEAN,
				tic.rs_hasSubClass::BOOLEAN,tic.rs_NumberOfRow::INTEGER
			FROM admtf_Table_InheritanceChildrens(a_SchemaName::NAME,
					a_TableName::NAME,a_Mode::VARCHAR(10)) tic;	
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_Mode VARCHAR(10)) IS 'Возвращает список характеристик унаследованных таблиц';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Table_InheritanceChildrens('public'::NAME,'np_house'::NAME);
SELECT * FROM admtf_Table_InheritanceChildrens('public'::VARCHAR(256),'np_house'::VARCHAR(256));

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

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

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Attribute_PKFeatures (a_TableOID OID,a_AttributeNo SMALLINT);
/***************************************************************************/
/*  Функция возвращает признак присутствия колонки в первичном ключе.                        */
/* Если колонка присутствует, то возвращается и ее порядковый номер в списке колонок  */
/* первичного ключа 				                                                                       */
/***************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Attribute_PKFeatures
	(a_TableOID		OID,		/* ИД таблицы */
	a_AttributeNo		SMALLINT	/* Порядковый номер атрибута в таблице*/	
 )										 
RETURNS  TABLE (rs_isAttributePK BOOLEAN,rs_PKeyName name,rs_ColumnPKNo SMALLINT) AS
$BODY$
DECLARE C_PKAttributeList_NDims CONSTANT INTEGER:=1;
					/* Размерность массива атрибутов первичного ключа*/
	v_PKAttributeList	SMALLINT[];
					/* Список номеров атребутов в составе первичного ключа*/
	v_PKAttributeIndx	INTEGER;
					/* Текущий индекс масссива атрибутов первичного ключа*/
	v_PKAttributeLBound	INTEGER;
					/* Нижний индекс масссива атрибутов первичного ключа*/		
	v_PKAttributeUBound	INTEGER;
					/* Верхний индекс масссива атрибутов первичного ключа*/
--**********************************************************************	BEGIN		
	rs_isAttributePK:=false;
	rs_ColumnPKNo:=NULL;
	SELECT INTO rs_PKeyName,v_PKAttributeList,rs_isAttributePK 
				conname,conkey,ARRAY[a_AttributeNo]<@conkey   
			FROM pg_constraint c 
			WHERE c.contype='p' and c.conrelid=a_TableOID;
	IF FOUND AND rs_isAttributePK THEN -- колонка присутсвует в первичном ключе
		v_PKAttributeLBound:=array_lower(v_PKAttributeList,C_PKAttributeList_NDims);
		v_PKAttributeUBound:=array_upper(v_PKAttributeList,C_PKAttributeList_NDims);
		v_PKAttributeIndx:=v_PKAttributeLBound;
		WHILE v_PKAttributeIndx <= v_PKAttributeUBound 
			AND a_AttributeNo<>v_PKAttributeList[v_PKAttributeIndx] 
		LOOP
			v_PKAttributeIndx:=v_PKAttributeIndx+1;
		END LOOP;
		IF v_PKAttributeIndx<=v_PKAttributeUBound THEN
			rs_ColumnPKNo:=v_PKAttributeIndx;
		END IF;
	END IF;			
	RETURN QUERY SELECT rs_isAttributePK,rs_PKeyName,rs_ColumnPKNo;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Attribute_PKFeatures(a_TableOID OID,a_AttributeNo SMALLINT) IS 'Возвращает признак присутствия колонки в первичном ключе и порядковый номер в списке колонок первичного ключа';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Attribute_PKFeatures((SELECT OID FROM pg_class WHERE relname='street'),3::SMALLINT);

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

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

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Attribute_FKFeatures (a_TableOID OID,a_AttributeNo SMALLINT);
/****************************************************************************/
/* Функция возвращает признак присутсвия колонки во внешнем ключе.           */
/* Если колонка присутствует, то возвращается и ее порядковый номер в списке */
/* колонок внешнего ключа.                                                   */
/****************************************************************************/
/****************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Attribute_FKFeatures
	(a_TableOID		OID,		/* ИД таблицы */
	a_AttributeNo		SMALLINT	/* Порядковый номер атрибута в таблице*/
 )										 

RETURNS  TABLE (rs_isAttributeFK BOOLEAN,rs_FKeyName name[],rs_ColumnFKNo SMALLINT[],rs_FKTableName name[],rs_FKTableColumnName name[]) AS
$BODY$
DECLARE C_FKAttributeList_NDims CONSTANT INTEGER:=1;
				/* Размерность массива атрибутов внешнего ключа*/
	v_FKAttributeList	SMALLINT[];
				/* Список номеров атрибутов в составе внешнего ключа*/
	v_RefAttributeList	SMALLINT[];
				/* Список номеров атрибутов в таблице, */
				/* на которую ссылается внешний ключ*/
	v_FKAttributeIndx	INTEGER;
				/* Текущий индекс масссива атрибутов внешнего ключа*/		
	v_RefAttributeListIndx	INTEGER;
				/* Текущий индекс масссива атрибутов таблицы, */
				/* на которую ссылается внешний ключ*/		
	v_FKAttributeLBound	INTEGER;
				/* Нижний индекс масссива атрибутов внешнего ключа*/		
	v_FKAttributeUBound	INTEGER;
				/* Верхний индекс масссива атрибутов внешнего ключа*/	
	v_FKConstraintIndx	INTEGER;
				/* Текущий индекс ограничения внешнего ключа*/		
	v_FKeyName		name;
				/* Название ограничения таблицы, */
				/* в котором определен внешний ключ*/
	v_FKTableName		name;
				/* Название таблицы, на которую ссылается внешний ключ*/
	v_FKTableColumnName 	name;
				/* Название атрибута в таблице, */
				/* на которую ссылается внешний ключ*/
	v_RefAttributeNo	SMALLINT;
				/* Порядковый номер атрибута в таблице, */
				/* на которую ссылается внешний ключ*/	
	v_Constraint		pg_constraint%ROWTYPE; 
				/* Запись Системной таблицы описания */
				/* ограничений (CONSTRANT) */
	
	
	--******************************************************************************************************		
BEGIN		
	rs_isAttributeFK:=false;
	rs_ColumnFKNo:=NULL;
	v_FKConstraintIndx:=0;
	FOR v_Constraint IN SELECT *  FROM pg_constraint c 
				WHERE c.contype='f' and c.conrelid=a_TableOID 
					AND ARRAY[a_AttributeNo]<@conkey 
				ORDER BY c.oid
	  LOOP
		v_FKConstraintIndx:=v_FKConstraintIndx+1;
		rs_isAttributeFK:=true;
		v_FKeyName:=v_Constraint.conname;
		v_FKAttributeList:=v_Constraint.conkey;
		v_RefAttributeList:=v_Constraint.confkey;
		v_FKAttributeLBound:=array_lower(v_FKAttributeList,C_FKAttributeList_NDims);
		v_FKAttributeUBound:=array_upper(v_FKAttributeList,C_FKAttributeList_NDims);
		v_FKAttributeIndx:=v_FKAttributeLBound;
		WHILE v_FKAttributeIndx <= v_FKAttributeUBound 
			AND a_AttributeNo<>v_FKAttributeList[v_FKAttributeIndx] 
		LOOP
			v_FKAttributeIndx:=v_FKAttributeIndx+1;
		END LOOP;
		rs_FKeyName[v_FKConstraintIndx]:=v_FKeyName;
		rs_ColumnFKNo[v_FKConstraintIndx]:=v_FKAttributeIndx;
		SELECT INTO v_FKTableName ftbl.relname 
			FROM pg_class ftbl WHERE ftbl.oid=v_Constraint.confrelid;
		rs_FKTableName[v_FKConstraintIndx]:=v_FKTableName;
		v_RefAttributeNo:=v_RefAttributeList[v_FKAttributeIndx];
		v_FKTableColumnName:=NULL;
		SELECT INTO v_FKTableColumnName attname 
			FROM pg_attribute a 
			WHERE a.attrelid=v_Constraint.confrelid AND a.attnum=v_RefAttributeNo;
		rs_FKTableColumnName[v_FKConstraintIndx]:=v_FKTableColumnName;
	END LOOP;
	RETURN QUERY SELECT rs_isAttributeFK,rs_FKeyName,rs_ColumnFKNo,
				rs_FKTableName,rs_FKTableColumnName;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Attribute_FKFeatures(a_TableOID OID,a_AttributeNo SMALLINT) IS 'Возвращает признак присутсвия колонки в первичном ключе и порядковый номер в списке колонок внешнего ключа';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Attribute_FKFeatures((SELECT OID FROM pg_class WHERE relname='street'),4::SMALLINT);

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

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

BEGIN TRANSACTION;
DROP FUNCTION IF EXISTS admtf_Attribute_Features (a_TableOID OID,a_AttributeNo SMALLINT);
/****************************************************************************/
/* Функция возвращает характеристики колонки таблицы           */
/****************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Attribute_Features
	(a_TableOID	OID,	/* ИД таблицы */
	a_AttributeNo	SMALLINT/* Порядковый номер атрибута в таблице*/	
 )										 

RETURNS  TABLE (rsa_AttributeName name,rsa_UserTypeName VARCHAR(256),rsa_TypeName VARCHAR(256),rsa_isNotNULL BOOLEAN,rsa_isAttributePK BOOLEAN,
		rsa_ColumnPKNo SMALLINT,rsa_Description Text,rsa_isAttributeFK BOOLEAN,rsa_FKeyName name[],rsa_ColumnFKNo SMALLINT[],rsa_FKTableName name[],rsa_FKTableColumnName name[]) AS
$BODY$
DECLARE 	
	v_Return_Error 	Integer := 0;		/* Код возврата*/		
	
	--*********************************************************************		
BEGIN	
	SELECT INTO rsa_AttributeName,rsa_UserTypeName,rsa_TypeName,
			rsa_isNotNULL,rsa_Description
		attr.attname,
		CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) 
					ELSE ''END AS r_UserTypeName,
		FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), 
			COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256) AS r_TypeName,				
		attr.attnotnull AS r_isNotNULL,
		dsc.description AS r_Description 
	FROM pg_attribute attr 
			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 attr.attrelid =a_TableOID AND attr.attnum=a_AttributeNo;	
	SELECT INTO rsa_isAttributePK,rsa_ColumnPKNo  rs_isAttributePK,rs_ColumnPKNo 
		FROM admtf_Attribute_PKFeatures(a_TableOID,a_AttributeNo);
	SELECT INTO rsa_isAttributeFK,rsa_FKeyName,rsa_ColumnFKNo,rsa_FKTableName,
			rsa_FKTableColumnName rs_isAttributeFK,rs_FKeyName,
			rs_ColumnFKNo,rs_FKTableName,rs_FKTableColumnName
		FROM admtf_Attribute_FKFeatures(a_TableOID,a_AttributeNo);
	RETURN QUERY SELECT rsa_AttributeName,rsa_UserTypeName,rsa_TypeName,rsa_isNotNULL,
			rsa_isAttributePK,rsa_ColumnPKNo,rsa_Description,rsa_isAttributeFK,
			rsa_FKeyName,rsa_ColumnFKNo,rsa_FKTableName,rsa_FKTableColumnName;
	
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTION admtf_Attribute_Features(a_TableOID OID,a_AttributeNo SMALLINT) IS 'Возвращает характеристики колонки таблицы';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
SELECT * FROM admtf_Attribute_Features ((SELECT OID FROM pg_class WHERE relname='street'),2::SMALLINT);

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

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

Автор: Сергей Гладков

Источник ^[19]