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

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

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

Первая и вторая части статьи опубликованы ранее.

Во второй- исходные тексты функций. <img src=«habrastorage.org/webt/zc/rx/ta/zcrxta08dnfat5cqpgid4lyq7by.png» align = «center»/>
В этой части статьи обсуждаются функции, возвращающие характеристики последовательностей, унаследованных таблиц, а также особенные характеристики атрибутов таблиц.
В первой половине статьи изложены комментарии к реализации функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.

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


Рис. 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).

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

Замечание 6

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

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

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

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

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

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

Название

Комментарий

Текущее

Начало

Приращение

Конец

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.

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

Название

Описание

objid

OID последовательности в каталоге pg_class

objsubid

Это поле содержит ноль

refobjid

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

refobjsubid

Номер атрибута таблицы, значения которого заполняются с помощью последовательности

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

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

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

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

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

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

Замечание 7

.

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

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

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

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

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

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

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

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

.

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

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

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

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

Исходный код можно посмотреть и скачать здесь.

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

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

.

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

{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. Часть вторая.


Оставить комментарий

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

*

x

Ещё Hi-Tech Интересное!

Клонируем бесконтактную карту с помощью мобильного приложения

Всегда было интересно посмотреть, что происходит у банковской карточки под «капотом». Как реализуется протокол общения банковской карточки и POS-терминала, как это работает и насколько это безопасно. Такая возможность предстала передо мной, когда я проходил стажировку в компании Digital Security. В ...

Чудесный форпост на орбите

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