Хабрахабр

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

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

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

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

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

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

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

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

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

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

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

Таблица 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)

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

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

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

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

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

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

Название ограничения

Тип

Массив номеров атрибутов

Указатель на позицию массива

fk_street_locality

f

1

fk_street_locality

f

{1,2}

2

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

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

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

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

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

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

a_SchemaName

).

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

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

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

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

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

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

Название индекса

Метод

? Уникальный

? первичный ключ

Атрибуты, входящие в индекс

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.

Время выполнения обеих версий функций у меня совпало на вывод данных в таблице результатов ушло 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));

Первая часть статьи находится здесь.

Показать больше

Похожие публикации

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»