Это третья часть статьи, которая описывает пользовательские функции для работы с системными каталогами: pg_class, pg_attribute, pg_constraints и т.д.
Первая и вторая части статьи опубликованы ранее.
В этой части статьи обсуждаются функции, возвращающие характеристики последовательностей, унаследованных таблиц, а также особенные характеристики атрибутов таблиц.
В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.
Структура функции, возвращающей список характеристик последовательностей таблицы
Рис. 2. Функции, от которых зависит admtf_Table_Sequences функции.
Таблица 11. Назначение функций.
| № | Название | Назначение |
|---|---|---|
| 1 | admtf_Sequence_Features | Функция возвращает список характеристик последовательности таблицы |
| 2 | admtf_Table_Sequences | Функция возвращает список последовательностей таблицы базы данных и их характеристик |
Функция admtf_Sequence_Features — список характеристики последовательности базы данных
Функция admtf_Sequence_Features возвращает список характеристик последовательности (SEQUENCE) базы данных. Исходный код можно посмотреть и скачать здесь.
Функция 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) таблицы базы данных, генерирующих значения ее полей, и характеристики этих последовательностей.Исходный код можно посмотреть и скачать здесь, а здесь приведена версия функции, в которой не используется курсор.
В качестве параметров функция принимает название исходной таблицы (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) таблицы базы данных. Исходный код можно посмотреть и скачать здесь.
В качестве параметров функция принимает название исходной таблицы (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, возвращающая точное число записей таблицы, а в списке возвращаемых значений 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) таблицы, и, если он присутствует, каков его порядковый номер в этом ключе, т.к. первичный ключ может быть составным.
Исходный код можно посмотреть и скачать здесь.
В качестве параметров функция принимает 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) таблицы, и, если он присутствует, каковы его порядковые номера в этих ключах, т.к. внешний ключ может быть составным.
Исходный код можно посмотреть и скачать здесь.
В качестве параметров функция принимает 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 возвращает список следующих характеристик атрибута таблицы.Исходный код можно посмотреть и скачать здесь.
| № | Название | Тип | Назначение |
|---|---|---|---|
| 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
Комментарии к исходному коду функции можно посмотреть здесь.
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
Комментарии к исходному коду функции можно посмотреть здесь.
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)
Комментарии к исходному коду функции можно посмотреть здесь.
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
Комментарии к исходному коду функции можно посмотреть здесь.
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
Комментарии к исходному коду функции можно посмотреть здесь.
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
Комментарии к исходному коду функции можно посмотреть здесь.
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
Комментарии к исходному коду функции можно посмотреть здесь.
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
Комментарии к исходному коду функции можно посмотреть здесь.
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. Часть первая;
Функции для документирования баз данных PostgreSQL. Часть вторая.
Автор: Сергей Гладков
