Это окончание статьи, которая описывает пользовательские функции для работы с системными каталогами: pg_class, pg_attribute, pg_constraint и т.д.
В этой части статьи обсуждаются функции, возвращающие характеристики: последовательностей, унаследованных таблиц,атрибутов таблиц, первичных и внешних ключей, индексов. И завершится обзор функцией,с которой начиналась статья, т.е. функцией, возвращающей весь комплекс рассмотренных характеристик.
В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению или скачать все скрипты одним архивом.
Функция admtf_Sequence_Features возвращает список характеристик последовательности (SEQUENCE) базы данных.
В качестве параметров функция принимает название последовательности (a_SequenceName) и название схемы, в пределах которой последовательность создана (a_SchemaName).
Необходимость в функции admtf_Sequence_Features возникла из-за того, что основные характеристики последовательности хранятся фактически в таблице, название которой совпадает с названием последовательности, а данные из нее извлекаются с помощью оператора SELECT. При этом название последовательности, название схемы и комментарий к последовательности хранятся в каталогах pg_class, pg_namespace и pg_description.
Текстовая версия оператора на рисунке
SELECT_Table_of_Sequence
1
SELECT *FROM kr_road_network_vertices_pgr_id_seq;
Замечание 6
В 10 версии PostgreSQL разделили характеристики последовательности и характеристики ее состояний. Для этого введен каталог pg_sequence с характеристиками последовательности, содержащий начальное значение (start_value), приращение (increment_by) и максимальное значение(max_value) последовательности. Последнее возвращенное последовательностью значение (last_value) так и оставили в «таблице» с названием последовательности.
Конец замечания.
Представление каждой последовательности в виде аналога таблицы, думаю, продиктовано необходимостью хранения последнего использованного значения последовательности (last_value), которое является характеристикой состояния последовательности, но не последовательности как таковой.
Запись о последовательности в каталоге pg_class отличается от записи о таблице значением вида отношения (relkind=‘S’).
Для того, чтобы извлекать характеристики произвольной последовательности, приходится использовать динамический SQL.
Текстовая версия оператора на рисунке
SELECT_Table_F_inside
1
2
3
4
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;
Таблица 12.Результат выполнения функции admtf_Sequence_Features(‘public’,’kr_road_network_vertices_pgr_id_seq’).
Функция admtf_Table_Sequences список последовательностей таблицы базы данных и их характеристик
INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid
LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid
ANDdsc.objsubid=0
INNER JOIN pg_class tbl ONd.refobjid=tbl.oid
INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNER JOIN pg_attribute da ON da.attrelid=d.refobjid
ANDda.attnum=d.refobjsubid
WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
ANDtbl.relkind='r'ANDpseq.relkind='S'
ORDER BY pseq.relname;
Описание отдельной последовательности представляет собой совокупность записи в pg_class, описывающей его как физическое отношение, и условной таблицы с названием последовательности, содержащей данные о специфических характеристиках последовательности
Информация о связи последовательности и исходной таблицы хранится в системном каталоге pg_depend.
Таблица 13.Атрибуты каталога pg_depend, необходимые для реализации функции.
Дополнительно функция обращается к данным каталогов 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’).
Версия admtf_Table_Sequences без курсора
В среде PostgreSQL, версия которой меньше 10, реализовать функцию admtf_Table_Sequences без использования курсора, скорее всего, невозможно.
Но счастливые обладатели 10 версии вполне могут обойтись без курсора, т.к. в их распоряжении есть каталог pg_sequence. В этом случае все характеристики последовательности можно извлечь одним оператором SELECT.
В приведенной реализации функции с помощью оконной функции RANK() OVER (PARTITION BY pseq.relname) вычисляется порядковый номер последовательности, используемой для заполнения исходной таблицы.
Текстовая версия оператора на рисунке
SELECT_Table_S_woc_inside
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT RANK()OVER(PARTITION BY pseq.relname)ASSequenceNo,
В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).
Описание отдельной унаследованной таблицы находится в записи в pg_class. Но для поиска унаследованных таблиц по названию исходной таблицы приходится использовать системный каталог pg_depend.
Таблица 15. Атрибуты каталога pg_depend, необходимые для реализации функции.
INNER JOIN pg_namespace nspc ON tbl.relnamespace=nspc.oid
LEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoid
ANDdsc.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
ANDrdsc.objsubid=0
WHERE LOWER(nspc.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
ANDtbl.relkind='r'ANDrtbl.relkind='r'
ORDER BY rtbl.relname;
Дополнительно функция обращается к данным каталогов pg_namespace и pg_description, для того чтобы извлечь схемы и комментарии как к унаследованным, так и к исходной таблице.
Таблица 16. Результат выполнения функции admtf_Table_InheritanceChildrens (‘public’,’np_house’).
Количество записей в порожденной таблицы выбирается из атрибута reltuple каталога pg_class. И хотя это значение часто в точности совпадает с действительным числом записей в таблице, все же это оценочное значение. А значит может возникнуть желание получить в результате точное значение. Например, так как показано на рисунке.
исходный код оператора на рисунке
Table_RowCount
1
2
EXECUTE'SELECT COUNT(*) FROM '||LOWER(a_SchemaName)||'.'||LOWER(a_TableName)
INTO v_TableNumberOfRowCalc;
Но, во-первых, для того чтобы выполнить это утверждение в тексте функция admtf_Table_InheritanceChildrens придется использовать курсор.
Во-вторых, хотелось бы, чтобы функция позволяла выводить как оценочное, так и точное количество записей таблицы.
Поэтому функция имеет еще одни необязательный параметр – режим получения количества записей таблицы (a_Mode), который принимает значения «оценочно» (estimate) или «точно» (exactly).
Дополнительно создана функция admfn_Table_RowCount, возвращающая точное число записей таблицы, а в списке возвращаемых значений SELECT атрибут reltuple заменен следующей конструкцией.
В результате функция возвращает оценочное значение показателя «число записей таблицы», если параметром a_Mode не задано требование возвращать точное значение.
Структура функции, возвращающей список характеристик атрибута таблицы
Рис. 3. Функции, которые вызывает admtf_Attribute_Features
Таблица 17. Назначение функций.
Функция admtf_Attribute_PKFeatures — — присутствует ли атрибут в первичном ключе
Функция admtf_Attribute_PKFeatures возвращает признак присутствия атрибута таблицы в первичном ключе (PRIMARY KEY) таблицы, и, если он присутствует, каков его порядковый номер в этом ключе, т.к. первичный ключ может быть составным.
В качестве параметров функция принимает OID исходной таблицы (a_TableOID) и порядковый номер искомого атрибута в ней (a_AttributeNo).
Нужные данные функция извлекает из записи каталога pg_constraint, содержащей ограничения (CONSTRAINT) исходной таблицы, в том числе, ограничение первичного ключа. OID искомой таблицы хранится в поле conrelid, описание первичного ключа хранится в записи, в которой поле contype содержит значение ‘p’.
исходный код оператора на рисунке
Attribute_PKF
1
2
3
SELECT INTO v_PKAttributeList,rs_isAttributePK conkey,ARRAY[a_AttributeNo]<@conkey
FROM pg_constraintc
WHEREc.contype='p'ANDc.conrelid=a_TableOID;
Поле 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 — список характеристик атрибута таблицы
В качестве параметров функция принимает OID исходной таблицы (a_TableOID) и порядковый номер искомого атрибута в ней (a_AttributeNo). Значения полей AttributeName и isNotNULL извлекаются из записи каталога pg_attribute, соответствующей значениям входных параметров.
исходный код оператора на рисунке
Attribute_F
1
2
3
4
5
6
7
SELECT attr.attname,attr.attnotnull FROM pg_attribute attr
WHERE attr.attrelid=a_TableOID ANDattr.attnum=a_AttributeNo;
SELECT rs_isAttributePK,rs_ColumnPKNo
FROM admtf_Attribute_PKFeatures(a_TableOID,a_AttributeNo);
FROM admtf_Attribute_FKFeatures(a_TableOID,a_AttributeNo);
Значения полей 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
Структура функции, возвращающей список характеристик первичного ключа таблицы
Рис. 4. Функции, которые вызывает admtf_PrimaryKey_ComplexFeatures.
Таблица 20. Назначение функций.
Функция admtf_PrimaryKey_ComplexFeatures – комплексный список характеристик первичного ключа таблицы
Таблица 21. Результат выполнения функции admtf_PrimaryKey_ComplexFeatures (‘public’,’ xpkstreet’).
В качестве обязательных параметров функция принимает название первичного ключа (a_PrimaryKeyName) и название схемы, в пределах которой создана таблица (a_SchemaName). Код функции представляет собой последовательный вызов двух табличных функций.
INNER JOIN pg_namespace nspc ON con.connamespace=nspc.oid
INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid
ANDattr.attnum=con.conkey[con.No]
LEFT OUTER JOIN pg_type typ ON attr.atttypid=typ.oid
LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid
WHERE con.contype='p'ANDnspc.nspname=LOWER(a_SchemaName)
ANDcon.conname=LOWER(a_PrimaryKeyName)
ORDER BY con.No;
Здесь следует обратить внимание на порядок вывода записей об атрибутах первичного ключа. Они выводятся в порядке описания в первичном ключе (con.No), а не в порядке их описания в таблице (attr.attnum).
Структура функции, возвращающей список характеристик внешнего ключа таблицы
Рис. 5. Функции, которые вызывает admtf_ForeignKey_ComplexFeatures.
Таблица 22. Назначение функций.
Функция admtf_ForeignKey_ComplexFeatures – комплексный список характеристик внешнего ключа таблицы
Функции admtf_ForeignKey_ComplexFeatures возвращает список следующих характеристик внешнего ключа таблицы.
Таблица 23. Результат выполнения функции admtf_ForeignKey_ComplexFeatures (‘public’,’fk_street_locality’,3).
В качестве параметров функция принимает название внешнего ключа (a_ ForeignKey) и название схемы, в пределах которой создан внешний ключ (a_SchemaName).
У функции есть еще одни необязательный параметр — порядковый номер индекса таблицы (a_ForeignKeyNo). Этот параметр нужен для того, чтобы в значения категории добавлять порядковый номер внешнего ключа таблицы. В частности, в приведенном примере функция выполнялась со значением этого параметра равным 3. Поэтому запись с характеристиками внешнего ключа помечена значением «fk03», записи с характеристиками атрибутов — «fk03att», запись о внешней таблице — «fk03rtbl», а записи об атрибутах внешней таблицы — «fk03ratt». Если при вызове функции опустить этот параметр, то значения категорий в записях были бы «fk», «fkatt», «fkrtbl» и «fkratt» соответственно. По этой же причине значение категории формируется внутри функции admtf_ForeignKey_ComplexFeatures, а не в коде вызывающей ее функции. Подробнее смотри в разделе «О каких расширенных характеристиках идет речь?»
Код функция представляет собой последовательный вызов трех табличных функций: admtf_ForeignKey_Features, admtf_ForeignKey_Attributes, admtf_ForeignKey_ReferenceTableComplexFeatures
.
Первая функция (admtf_ForeignKey_Features) подготавливает и выполняет SELECT, возвращающий характеристики внешнего ключа.
INNER JOIN pg_namespace nspc ON con.connamespace=nspc.oid
INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid ANDdsc.objsubid=0
WHERE nspc.nspname=LOWER(a_SchemaName)ANDcon.contype='f'
ANDcon.conname=LOWER(a_ForeignKeyName);
Вторая функция (admtf_ForeignKey_Attributes) возвращает характеристики атрибутов внешнего ключа.
Здесь следует обратить внимание на порядок вывода записей об атрибутах внешнего ключа. Они выводятся в порядке описания во внешнем ключе (con.No), а не в порядке их описания в таблице (attr.attnum).
c.confkey::SMALLINT[],generate_subscripts(c.conkey,1)asNo FROM pg_constraintc)con
INNER JOIN pg_namespace nspc ON con.connamespace=nspc.oid
INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid ANDattr.attnum=con.conkey[con.No]
INNER 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 ANDdsc.objsubid=attr.attnum
WHERE nspc.nspname=LOWER(a_SchemaName)ANDcon.contype='f'ANDcon.conname=LOWER(a_ForeignKeyName)
ORDER BY con.No;
Третья функция (admtf_ForeignKey_ReferenceTableComplexFeatures) возвращает характеристики таблицы, на которую ссылается внешний ключ. Для решения своей задачи она последовательно вызывает две дополнительные функции.
Функция admtf_ForeignKey_ReferenceTableComplexFeatures – комплексный список характеристик таблицы, на которую ссылается внешний ключ
В качестве параметров функция принимает название внешнего ключа (a_ForeignKey) и название схемы, в пределах которой создан внешний ключ (a_SchemaName).
У функции есть еще одни необязательный параметр — порядковый номер индекса таблицы (a_ForeignKeyNo). Этот параметр нужен для того, чтобы в значениях категории замещать порядковым номером символ ‘%’ в «fk%rtbl» и «fk%ratt» соответственно.
Функция последовательно вызывает две дополнительные функции:admtf_ForeignKey_ReferenceTableFeatures,admtf_ForeignKey_ReferenceTableAttributes.
Первая admtf_ForeignKey_ReferenceTableFeatures возвращает непосредственно характеристики таблицы, на которую ссылается внешний ключ, и представляет собой упрощенную версию функции admtf_Table_Features.
Вторая admtf_ForeignKey_ReferenceTableAttributes – характеристики атрибутов внешней таблицы, соответствующие атрибутам внешнего ключа. Она почти полностью повторяет код функции admtf_ForeignKey_Attributes. Только в некоторых местах вместо идентификатора con.conrelid используется con.confrelid, и вместо массива con.conkey используется con.confkey.
исходный код оператора на рисунке
SELECT_FK_RTA_confrelid_vs_conrelid
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT(rank()OVER(PARTITION BY con.confrelid ORDER BY con.No))::SMALLINT,
У функции есть еще одни необязательный параметр — порядковый номер индекса таблицы (a_IndexNo). Этот параметр нужен для того, чтобы в значения категории добавлять порядковый номер индекса таблицы. В частности, в приведенном примере функция выполнялась со значением этого параметра равным 7. Поэтому запись с характеристиками индекса помечена значением «idx07», а записи с характеристиками атрибутов — «idx07att». Если при вызове функции опустить этот параметр, то значения категорий в записях были бы «idx» и «idxatt» соответственно.
Подробнее смотри в разделе «О каких расширенных характеристиках идет речь?». По этой же причине значение категории формируется внутри функции admtf_Index_ComplexFeatures, а не в коде вызывающей ее функции.
Код функции представляет собой последовательный вызов двух табличных функций: admtf_Index_Features, admtf_ Index_Attributes.
Первая функция (admtf_Index_Features) подготавливает и выполняет SELECT, возвращающий характеристики индекса.
INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNER JOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid
LEFT OUTER JOIN pg_Description dsc ON inxcls.oid=dsc.objoid
ANDdsc.objsubid=0
LEFT OUTER JOIN pg_am inxam ON inxcls.relam=inxam.oid
LEFT OUTER JOIN pg_class tbl ON inx.indrelid=tbl.oid
WHERE inxcls.relkind='i'ANDnsp.nspname=LOWER(a_SchemaName)
ANDinxcls.relname=LOWER(a_IndexName);
Вторая функция (admtf_ Index_Attributes) возвращает характеристики атрибутов, включенных в состав индекса. Обратите внимание, что порядок записей об атрибутах определяется порядком их описания в индексе (inx.No), а не порядком физического следования в таблице (attr.attnum).
Функци\я admtf_Table_ComplexFeatures — комплексный список характеристик таблицы базы данных
Функция admtf_Table_ComplexFeatures возвращает комплексный список характеристик таблицы базы данных, который включает в себя характеристики, возвращаемые описанными в статье функциями. В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).
Таблица 26. Результат выполнения функции admtf_Table_ComplexFeatures(‘public’, ‘street’).
В процессе своего выполнения функция последовательно вызывает 9 дополнительных функций, список которых приведен в разделе «Структура головной функции».
Описанные в статье функции были созданы в процессе подготовки заявки на государственную регистрацию базы данных. Порядок подачи заявки и требования ее оформлению изложены в документе «Правила оформления заявки на государственную регистрацию программы для электронных вычислительных машин или базы данных», утвержденном приказом Минэкономразвития России от 5 апреля 2016 года N 211 (далее Правила).
Обязательной частью заявки является документ «Материалы, идентифицирующие базу данных». Правила трактуют содержание этого документа следующим образом.
«Материалы, идентифицирующие базу данных, должны отражать объективную форму представления совокупности содержащихся в ней самостоятельных материалов в виде примеров реального наполнения и принципы их систематизации (структуру базы данных), позволяющие осуществить нахождение и обработку этих материалов с помощью ЭВМ.»
>Другими словами, документ должен содержать описание структуры базы данных и примеры ее реального наполнения.
Как видно из рисунка, при подготовке заявки на регистрацию базы данных использовались не только функции, описанные в этой статье. Дополнительно были созданы 3- 4 функции для преобразования описаний таблиц базы данных в формат PlantUML.Точнее, эти функции создают код в формате плагина для системы управления проектами TRAC, поэтому если вам захочется проверить созданный этими функциями код, то не забудьте убрать две строчки сверху перед @startuml и все строчки снизу после @enduml.
P.S. Почему здесь не приведены дополнительные функции преобразования описания таблиц базы данных в формат плагина PlantUML для системы управления проектами TRAC? Во-первых, они не вписывались в заявленную тему. Во-вторых, похоже, я утомил читателей текстами функций. Но, если кто-то заинтересуется этими функциями, то пишите мне, и я вышлю их тексты.
FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME)ts;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_Table_Sequences(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256))IS'Возвращает список последовательностей, от которых зависит таблица';
FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME)ts;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_Table_Sequences(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256))IS'Возвращает список последовательностей, от которых зависит таблица';
COMMENT ON FUNCTIONadmtf_Table_InheritanceChildrens(a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10))IS'Возвращает список характеристик унаследованных таблиц';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTIONIFEXISTS admtf_Table_InheritanceChildrens(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_TableName NAME,a_Mode VARCHAR(10));
FROM admtf_Table_InheritanceChildrens(a_SchemaName::NAME,
a_TableName::NAME,a_Mode::VARCHAR(10))tic;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_Table_InheritanceChildrens(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_Mode VARCHAR(10))IS'Возвращает список характеристик унаследованных таблиц';
COMMENT ON FUNCTIONadmtf_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);
COMMENT ON FUNCTIONadmtf_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);
INNER JOIN pg_namespace nspc ON con.connamespace=nspc.oid
INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid
ANDattr.attnum=con.conkey[con.No]
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
ANDdsc.objsubid=attr.attnum
WHERE con.contype=c_PrimaryKeyKind
ANDLOWER(nspc.nspname)=LOWER(a_SchemaName)
ANDLOWER(con.conname)=LOWER(a_PrimaryKeyName)
ORDER BY attr.attnum;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_PrimaryKey_Attributes(a_SchemaName NAME,a_PrimaryKeyName NAME)IS'Функция возвращает список атрибутов первичного ключа и их характеристик ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTIONIFEXISTS admtf_PrimaryKey_Attributes(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
FROM admtf_PrimaryKey_Attributes(a_SchemaName::NAME,a_PrimaryKeyName::NAME)pka;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_PrimaryKey_Attributes(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256))IS'Функция возвращает список атрибутов первичного ключа и их характеристик';
FROM admtf_PrimaryKey_Attributes(a_SchemaName,a_PrimaryKeyName);
ENDIF;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_PrimaryKey_ComplexFeatures(a_SchemaName NAME,a_PrimaryKeyName NAME)IS'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTIONIFEXISTS admtf_PrimaryKey_ComplexFeatures(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
FROM admtf_PrimaryKey_ComplexFeatures(a_SchemaName::NAME,
a_PrimaryKeyName::NAME)pk;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_PrimaryKey_ComplexFeatures(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256))IS'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов';
COMMENT ON FUNCTIONadmtf_ForeignKey_ReferenceTableFeatures(a_SchemaName NAME,a_ForeignKeyName NAME)IS'Возвращает список характеристик таблицы, на которую ссылается внешний ключ';
--ROLLBACK TRANSACTION;
COMMIT TRANSACTION;
BEGIN TRANSACTION;
DROP FUNCTIONIFEXISTS admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256));
FROM admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName::NAME,
a_ForeignKeyName::NAME)fkrt;
END
$BODY$
LANGUAGE plpgsql;
COMMENT ON FUNCTIONadmtf_ForeignKey_ReferenceTableFeatures(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256))IS'Возвращает список характеристик таблицы, на которую ссылается внешний ключ';
Это окончание статьи, которая описывает пользовательские функции для работы с системными каталогами: pg_class, pg_attribute, pg_constraint и т.д.
Описанные в статье функции были созданы в процессе подготовки заявки на государственную регистрацию базы данных. Порядок подачи заявки и требования ее оформлению изложены в документе «Правила оформления заявки на государственную регистрацию программы для электронных вычислительных машин или базы данных», утвержденном приказом Минэкономразвития России от 5 апреля 2016 года N 211 (далее Правила).