Views/Просмотры:
1 837
pg_class pg_attribute pg_constraint
В этой части статьи обсуждаются функции, возвращающие характеристики: последовательностей , унаследованных таблиц ,атрибутов таблиц , первичных и внешних ключей , индексов . И завершится обзор функцией,с которой начиналась статья, т.е. функцией, возвращающей весь комплекс рассмотренных характеристик.
В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению или
Содержание
Структура функции admtf_Table_Sequences Функция admtf_Sequence_Features — список характеристики последовательности Функция admtf_Table_Sequences список последовательностей таблицы базы данных и их характеристик Версия admtf_Table_Sequences без курсора Функция admtf_Table_InheritanceChildrens – список характеристик унаследованных таблиц Дополнительно создана функция admfn_Table_RowCount Структура функции, возвращающей список характеристик атрибута таблицы Функция admtf_Attribute_Features — список характеристик атрибута таблицы Функци admtf_Attribute_PKFeatures — — присутствует ли атрибут в первичном ключе Функция admtf_Attribute_FKFeatures -присутствует ли атрибут во внешнем ключе Структура функции, возвращающей список характеристик первичного ключа таблицы Функция admtf_PrimaryKey_ComplexFeatures – комплексный список характеристик первичного ключа таблицы Функция admtf_PrimaryKey_Features Функция admtf_PrimaryKey_Attributes Структура функции, возвращающей список характеристик внешнего ключа таблицы Функция admtf_ForeignKey_ComplexFeatures – комплексный список характеристик внешнего ключа таблицы Функция admtf_ForeignKey_Features Функция admtf_ForeignKey_Attributes Функция admtf_ForeignKey_Attributes Функция admtf_ForeignKey_ReferenceTableComplexFeatures Функция admtf_ForeignKey_ReferenceTableFeatures Функция admtf_ForeignKey_ReferenceTableFeatures Функция admtf_ForeignKey_ReferenceTableAttributes Структура функции, возвращающей список характеристик индекса таблицы Функция admtf_Index_ComplexFeatures – комплексный список характеристик индекса таблицы Функция admtf_Index_Features Функция admtf_ Index_Attributes Функция admtf_Index_ComplexFeatures – комплексный список характеристик индекса таблицы Функция admtf_Index_Features Функция admtf_ Index_Attributes Функции admtf_Table_ComplexFeatures — комплексный список характеристик таблицы базы данных Где использовались функции?
ПРИЛОЖЕНИЕ. Создание функции admtf_Sequence_Features Создание функции admtf_Table_Sequences Создание функции admtf_Table_Sequences без курсора (PostgreSQL 10) Создание функции admfn_Table_RowCount Создание функции admtf_Table_InheritanceChildrens Создание функции admtf_Attribute_PKFeatures Создание функции admtf_Attribute_FKFeatures Создание функции admtf_Attribute_Features Создание функции admtf_PrimaryKey_Features Создание функции admtf_PrimaryKey_Attributes Создание функции admtf_PrimaryKey_ComplexFeatures Создание функции admtf_ForeignKey_Features Создание функции admtf_ForeignKey_Attributes Создание функции admtf_ForeignKey_ReferenceTableFeatures Создание функции admtf_ForeignKey_ReferenceTableAttributes Создание функции admtf_ForeignKey_ReferenceTableComplexFeatures Создание функции admtf_ForeignKey_ComplexFeatures Создание функции admtf_Index_Features Создание функции admtf_Index_Attributes Создание функции admtf_Index_ComplexFeatures Создание функции admtf_Table_ComplexFeatures
Структура функции, возвращающей список характеристик последовательностей таблицы
Рис. 2. Функции, от которых зависит admtf_Table_Sequences функции
Таблица 11. Назначение функций
Функция 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
Текстовая версия оператора на рисунке
SELECT * FROM kr_road_network_vertices_pgr_id_seq ;
[crayon-66214412cecc0290560273/]
Замечание 6
В 10 версии PostgreSQL разделили характеристики последовательности и характеристики ее состояний. Для этого введен каталог pg_sequence start_value ), приращение (increment_by ) и максимальное значение(max_value ) последовательности. Последнее возвращенное последовательностью значение (last_value ) так и оставили в «таблице» с названием последовательности.
Конец замечания.
Представление каждой последовательности в виде аналога таблицы, думаю, продиктовано необходимостью хранения последнего использованного значения последовательности (last_value ), которое является характеристикой состояния последовательности, но не последовательности как таковой.
Запись о последовательности в каталоге pg_class ‘S’ ).
Для того, чтобы извлекать характеристики произвольной последовательности, приходится использовать динамический SQL.
Текстовая версия оператора на рисунке
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 ;
[crayon-66214412ceccd198556650/]
Таблица 12. Результат выполнения функции admtf_Sequence_Features(‘public’,’kr_road_network_vertices_pgr_id_seq’).
Функция admtf_Table_Sequences список последовательностей таблицы базы данных и их характеристик
Функция admtf_Table_Sequences возвращает список последовательностей (SEQUENCE) таблицы базы данных, генерирующих значения ее полей, и характеристики этих последовательностей.Исходный код можно посмотреть и скачать здесь , а здесь приведена версия функции, в которой не используется курсор .
В качестве параметров функция принимает название исходной таблицы (a_TableName ) и название схемы, в пределах которой создана таблица (a_SchemaName ).
Текстовая версия оператора на рисунке
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
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 da . attnum = d . refobjsubid
WHERE LOWER ( nsp . nspname ) = LOWER ( a_SchemaName )
AND LOWER ( tbl . relname ) = LOWER ( a_TableOID )
AND tbl . relkind = 'r' AND pseq . relkind = 'S'
ORDER BY pseq . relname ;
[crayon-66214412cecd2499875002/]
Описание отдельной последовательности представляет собой совокупность записи в
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 без использования курсора, скорее всего, невозможно.pg_sequence
В приведенной реализации функции с помощью оконной функции RANK() OVER (PARTITION BY pseq.relname) вычисляется порядковый номер последовательности, используемой для заполнения исходной таблицы.
Текстовая версия оператора на рисунке
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 ) AS SequenceNo ,
pseq . relname AS SequenceName , snsp . nspname AS SequenceSchemaName ,
COALESCE ( dsc . description , 'Последовательность, генерирующая значения поля '
|| da . attname ) AS SequenceDescription ,
seq . seqstart AS SequenceStartValue , seq . seqincrement AS SequenceIncrementBy ,
seq . seqmax AS SequenceMaxValue ,
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_sequence seq ON seq . seqrelid = 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 da . attnum = d . refobjsubid
WHERE LOWER ( nsp . nspname ) = LOWER ( a_SchemaName )
AND LOWER ( tbl . relname ) = LOWER ( a_TableOID )
AND tbl . relkind = 'r' AND pseq . relkind = 'S'
ORDER BY pseq . relname ;
[crayon-66214412cecd8433112296/]
Замечание 7 .
Эта версия функции не возвращает последнее сгенерированное последовательностью значение (last_value ).
Конец замечания.
Функция admtf_Table_InheritanceChildrens – список характеристик унаследованных таблиц
Функция admtf_Table_InheritanceChildrens возвращает список характеристик унаследованных таблиц (INHERITS ) таблицы базы данных. Исходный код можно посмотреть и скачать здесь .
В качестве параметров функция принимает название исходной таблицы (a_TableName ) и название схемы, в пределах которой создана таблица (a_SchemaName ).
Описание отдельной унаследованной таблицы находится в записи в pg_class pg_depend
Таблица 15. Атрибуты каталога pg_depend
исходный код оператора на рисунке
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT rtbl . relname , rnspc . nspname , rdsc . description , rtbl . relnatts :: INTEGER ,
rtbl . relchecks :: INTEGER ,
rtbl . relhaspkey , rtbl . relhasindex , rtbl . relhassubclass ,
rtbl . 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
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 LOWER ( nspc . nspname ) = LOWER ( a_SchemaName )
AND LOWER ( tbl . relname ) = LOWER ( a_TableOID )
AND tbl . relkind = 'r' AND rtbl . relkind = 'r'
ORDER BY rtbl . relname ;
[crayon-66214412cecdd385617194/]
Дополнительно функция обращается к данным каталогов
pg_namespace и
pg_description , для того чтобы извлечь схемы и комментарии как к унаследованным, так и к исходной таблице.
Таблица 16. Результат выполнения функции admtf_Table_InheritanceChildrens (‘public’,’np_house’).
pg_class
исходный код оператора на рисунке
EXECUTE 'SELECT COUNT(*) FROM ' || LOWER ( a_SchemaName ) || '.' || LOWER ( a_TableName )
INTO v_TableNumberOfRowCalc ;
[crayon-66214412cece2429875981/]
Но, во-первых, для того чтобы выполнить это утверждение в тексте функция
admtf_Table_InheritanceChildrens придется использовать курсор.
Во-вторых, хотелось бы, чтобы функция позволяла выводить как оценочное, так и точное количество записей таблицы.
Поэтому функция имеет еще одни необязательный параметр – режим получения количества записей таблицы (a_Mode ), который принимает значения «оценочно» (estimate ) или «точно» (exactly ).
admfn_Table_RowCount , возвращающая точное число записей таблицы, а в списке возвращаемых значений SELECT атрибут reltuple заменен следующей конструкцией.
исходный код оператора на рисунке
CASE WHEN a_Mode = 'exactly' THEN admfn_Table_RowCount ( rnspc . nspname , rtbl . relname )
ELSE reltuples END
[crayon-66214412cece5476282682/]
В результате функция возвращает оценочное значение показателя «число записей таблицы», если параметром
a_Mode не задано требование возвращать точное значение.
Структура функции, возвращающей список характеристик атрибута таблицы
Рис. 3. Функции, которые вызывает admtf_Attribute_Features
Таблица 17. Назначение функций.
Функция admtf_Attribute_PKFeatures — — присутствует ли атрибут в первичном ключе
Функция admtf_Attribute_PKFeatures возвращает признак присутствия атрибута таблицы в первичном ключе (PRIMARY KEY) таблицы, и, если он присутствует, каков его порядковый номер в этом ключе, т.к. первичный ключ может быть составным.
Исходный код можно посмотреть и скачать здесь .
В качестве параметров функция принимает OID исходной таблицы (a_TableOID ) и порядковый номер искомого атрибута в ней (a_AttributeNo ).pg_constraint conrelid , описание первичного ключа хранится в записи, в которой поле contype содержит значение ‘p’ .
исходный код оператора на рисунке
SELECT INTO v_PKAttributeList , rs_isAttributePK conkey , ARRAY [ a_AttributeNo ] < @ conkey
FROM pg _ constraint c
WHERE c . contype = 'p' AND c . conrelid = a_TableOID ;
[crayon-66214412cecea815646838/]
Поле conkey , найденной таким образом записи, содержит массив порядковых номеров атрибутов, которые составляют первичный ключ. Поэтому, для того чтобы проверить присутствие исходного атрибута в первичном ключе, достаточно вычислить логическое выражение ARRAY[a_AttributeNo]<@conkey .
Если атрибут присутствует в первичном ключе, то далее в цикле вычисляется его порядковый номер.
Функция admtf_Attribute_FKFeatures -присутствует ли атрибут во внешнем ключе
Функция admtf_Attribute_FKFeatures возвращает признак присутствия атрибута таблицы в одном или нескольких внешних ключах (FOREIGN KEY) таблицы, и, если он присутствует, каковы его порядковые номера в этих ключах, т.к. внешний ключ может быть составным.
Исходный код можно посмотреть и скачать здесь .
В качестве параметров функция принимает OID исходной таблицы (a_TableOID ) и порядковый номер искомого атрибута в ней (a_AttributeNo ).
Нужные данные функция извлекает из записи каталога pg_constraint conrelid , описание первичного ключа хранится в записи, в которой поле contype содержит значение ‘f’ .
исходный код оператора на рисунке
SELECT * FROM pg _ constraint c
WHERE c . contype = 'f ' AND c . conrelid = a_TableOID AND ARRAY [ a_AttributeNo ] < @ conkey
ORDER BY c . oid ;
[crayon-66214412cecee142805242/]
Поле conkey , найденной таким образом записи, содержит массив порядковых номеров атрибутов, которые составляют внешний ключ. Поэтому, для того чтобы проверить присутствие исходного атрибута во внешнем ключе, достаточно вычислить логическое выражение ARRAY[a_AttributeNo]<@conkey .
Если атрибут присутствует во внешнем ключе, то далее в цикле формируется массив его порядковых номеров в содержащих его внешних ключах. Дополнительно формируется еще два массива из наименований таблиц и их атрибутов, на которые ссылается исходный атрибут в содержащих его внешних ключах.
Наименования таблиц извлекаются из записи каталога pg_class
Для получения наименования атрибута внешней таблицы используется массив порядковых номеров из поля confkey f » в названии). Из этого массива извлекается порядковый номер атрибута внешней таблицы, которому соответствует внешний атрибут. По этому порядковому номеру атрибута внешней таблицы и ее OID, находится в каталоге pg_attribute
Функция admtf_Attribute_Features — список характеристик атрибута таблицы
Функции admtf_Attribute_Features возвращает список следующих характеристик атрибута таблицы.Исходный код можно посмотреть и скачать здесь .
В качестве параметров функция принимает OID исходной таблицы (a_TableOID ) и порядковый номер искомого атрибута в ней (a_AttributeNo ). Значения полей AttributeName и isNotNULL извлекаются из записи каталога pg_attribute
исходный код оператора на рисунке
SELECT attr . attname , attr . attnotnull FROM pg_attribute attr
WHERE attr . attrelid = a_TableOID AND attr . attnum = a_AttributeNo ;
SELECT rs_isAttributePK , rs_ColumnPKNo
FROM admtf_Attribute_PKFeatures ( a_TableOID , a_AttributeNo ) ;
SELECT rs_isAttributeFK , rs_FKeyName , rs_ColumnFKNo ,
rs_FKTableName , rs_FKTableColumnName
FROM admtf_Attribute_FKFeatures ( a_TableOID , a_AttributeNo ) ;
[crayon-66214412cecf3650635249/]
Значения полей 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 ). Код функции представляет собой последовательный вызов двух табличных функций.
Исходный код можно посмотреть и скачать здесь.
Первая функция (admtf_PrimaryKey_Features ) подготавливает и выполняет SELECT, возвращающий характеристики первичного ключа.
исходный код оператора на рисунке
SELECT con . conname ,
COALESCE ( dsc . description , 'Первичный ключ таблицы ' || tbl . relname )
FROM pg_constraint con
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
AND dsc . objsubid = 0
WHERE con . contype = 'p' AND nspc . nspname = LOWER ( a_SchemaName )
AND con . conname = LOWER ( a_PrimaryKeyName ) ;
[crayon-66214412cecf8263401571/]
Вторая функция (admtf_PrimaryKey_Attributes ) возвращает характеристики атрибутов, составляющих первичный ключ.
исходный код оператора на рисунке
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT ( rank ( ) OVER ( PARTITION BY con . conrelid ORDER BY con . No ) ) :: SMALLINT ,
attr . attnum , attr . attname :: NAME ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0 THEN typ . typname :: VARCHAR ( 100 ) ELSE '' END ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) ,
COALESCE ( NULLIF ( typ . typtypmod , - 1 ) , attr . atttypmod ) ) :: VARCHAR ( 256 ) ,
attr . attnotnull
FROM ( SELECT c . oid , c . conrelid , c . connamespace , c . confrelid , c . conname , c . contype ,
c . conkey :: SMALLINT [ ] , generate_subscripts ( c . conkey , 1 ) as No
FROM pg _ constraint c ) con
INNER JOIN pg_namespace nspc ON con . connamespace = nspc . oid
INNER JOIN pg_attribute attr ON attr . attrelid = con . conrelid
AND attr . 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' AND nspc . nspname = LOWER ( a_SchemaName )
AND con . conname = LOWER ( a_PrimaryKeyName )
ORDER BY con . No ;
[crayon-66214412cecfc363016637/]
Здесь следует обратить внимание на порядок вывода записей об атрибутах первичного ключа. Они выводятся в порядке описания в первичном ключе (con.No ), а не в порядке их описания в таблице (attr.attnum ).
Особенности соединения записей, используемых системных каталогов, подробно рассмотрены в разделе «Функция admtf_Table_Constraintes список ограничений таблицы базы данных и их характеристик» .
Структура функции, возвращающей список характеристик внешнего ключа таблицы
Рис. 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, возвращающий характеристики внешнего ключа.
исходный код оператора на рисунке
SELECT con . conname , COALESCE ( dsc . description , 'Внешний ключ таблицы ' || tbl . relname )
FROM pg_constraint con
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 AND dsc . objsubid = 0
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = 'f'
AND con . conname = LOWER ( a_ForeignKeyName ) ;
[crayon-66214412ced02798239665/]
Вторая функция (admtf_ForeignKey_Attributes ) возвращает характеристики атрибутов внешнего ключа.
Здесь следует обратить внимание на порядок вывода записей об атрибутах внешнего ключа. Они выводятся в порядке описания во внешнем ключе (con.No ), а не в порядке их описания в таблице (attr.attnum ).
Особенности соединения записей, используемых системных каталогов, подробно рассмотрены в разделе «Функция admtf_Table_Constraintes — список ограничений таблицы базы данных и их характеристик» .
исходный код оператора на рисунке
SELECT ( rank ( ) OVER ( PARTITION BY con . conrelid ORDER BY con . No ) ) :: SMALLINT AS r_ForeingKeyNo ,
attr . attnum AS r_AttributeNumber , attr . attname :: NAME AS r_AttributeName ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0 THEN typ . typname :: NAME ELSE '' END AS r_UserTypeName ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) , COALESCE ( NULLIF ( typ . typtypmod , - 1 ) , attr . atttypmod ) ) :: NAME AS r_TypeName ,
attr . attnotnull AS r_isNotNULL , TRIM ( dsc . description ) AS r_Description
FROM ( SELECT c . oid , c . conrelid , c . confrelid , c . conname , c . connamespace , c . contype , c . conkey :: SMALLINT [ ] , c . consrc ,
c . confkey :: SMALLINT [ ] , generate_subscripts ( c . conkey , 1 ) as No FROM pg _ constraint c ) con
INNER JOIN pg_namespace nspc ON con . connamespace = nspc . oid
INNER JOIN pg_attribute attr ON attr . attrelid = con . conrelid AND attr . 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 AND dsc . objsubid = attr . attnum
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = 'f' AND con . conname = LOWER ( a_ForeignKeyName )
ORDER BY con . No ;
[crayon-66214412ced06502246061/]
Третья функция (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 .
исходный код оператора на рисунке
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 ,
attr . attnum AS r_AttributeNumber , attr . attname :: NAME AS r_AttributeName ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0 THEN typ . typname :: NAME ELSE '' END ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) ,
COALESCE ( NULLIF ( typ . typtypmod , - 1 ) , attr . atttypmod ) ) :: NAME ,
attr . attnotnull AS r_isNotNULL , TRIM ( dsc . description ) AS r_Description
FROM ( SELECT c . oid , c . conrelid , c . confrelid , c . conname , c . connamespace , c . contype ,
c . conkey :: SMALLINT [ ] , c . consrc , c . confkey :: SMALLINT [ ] ,
generate_subscripts ( c . conkey , 1 ) as No
FROM pg _ constraint c ) con
INNER JOIN pg_namespace nspc ON con . connamespace = nspc . oid
INNER JOIN pg_attribute attr ON attr . attrelid = con . confrelid
AND attr . attnum = con . confkey [ 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
AND dsc . objsubid = attr . attnum
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = 'f'
AND con . conname = LOWER ( a_ForeignKeyName )
ORDER BY con . No ;
[crayon-66214412ced0b163774435/]
Структура функции, возвращающей список характеристик индекса таблицы
Рис. 6. Функции, которые вызывает admtf_Index_ComplexFeatures.
Таблица 24 . Назначение функций.
Функция admtf_Index_ComplexFeatures – комплексный список характеристик индекса таблицы
Функции admtf_Index_ComplexFeatures возвращает список следующих характеристик индекса таблицы.
Таблица 25. Результат выполнения функции admtf_Index_ComplexFeatures (‘public’,’xie9street’,7).
В качестве параметров функция принимает название индекса (a_ Index ) и название схемы, в пределах которой создан индекс (a_SchemaName ).
Исходный код можно посмотреть и скачать здесь.
У функции есть еще одни необязательный параметр — порядковый номер индекса таблицы (a_IndexNo ). Этот параметр нужен для того, чтобы в значения категории добавлять порядковый номер индекса таблицы. В частности, в приведенном примере функция выполнялась со значением этого параметра равным 7 . Поэтому запись с характеристиками индекса помечена значением «idx07 », а записи с характеристиками атрибутов — «idx07att ». Если при вызове функции опустить этот параметр, то значения категорий в записях были бы «idx » и «idxatt » соответственно.
Подробнее смотри в разделе «О каких расширенных характеристиках идет речь?» . По этой же причине значение категории формируется внутри функции admtf_Index_ComplexFeatures , а не в коде вызывающей ее функции.
Код функции представляет собой последовательный вызов двух табличных функций: admtf_Index_Features , admtf_ Index_Attributes .
Первая функция (admtf_Index_Features ) подготавливает и выполняет SELECT, возвращающий характеристики индекса.
исходный код оператора на рисунке
SELECT inxcls . relname ,
CASE WHEN COALESCE ( TRIM ( dsc . description ) , '' ) = '' THEN
'Индекс' || CASE WHEN inx . indisunique THEN ' уникальный'
|| CASE WHEN inx . indisprimary THEN '(первичный ключ)' ELSE '' END ELSE '' END
|| CASE WHEN inxam . amname = 'gist' THEN ' пространственный' ELSE '' END || ' таблицы '
|| tbl . relname
ELSE dsc . description END
FROM pg_index inx
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
AND dsc . 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' AND nsp . nspname = LOWER ( a_SchemaName )
AND inxcls . relname = LOWER ( a_IndexName ) ;
[crayon-66214412ced12702335598/]
Вторая функция (admtf_ Index_Attributes ) возвращает характеристики атрибутов, включенных в состав индекса. Обратите внимание, что порядок записей об атрибутах определяется порядком их описания в индексе (inx.No ), а не порядком физического следования в таблице (attr.attnum ).
исходный код оператора на рисунке
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT ( inx . No + 1 ) :: SMALLINT , attr . attnum :: SMALLINT , attr . attname :: NAME ,
CASE WHEN NOT inxam . amcanorder THEN NULL
ELSE
CASE WHEN inx . indoption [ inx . No ] & 1 = 1 THEN 'DESC'
ELSE 'ASC' END END :: VARCHAR ( 10 ) ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0 THEN typ . typname :: NAME ELSE '' END ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) ,
COALESCE ( NULLIF ( typ . typtypmod , - ) , attr . atttypmod ) ) :: NAME ,
attr . attnotnull , dsc . description
FROM ( SELECT i . indrelid , i . indexrelid , i . indkey :: SMALLINT [ ] , i . indoption :: SMALLINT [ ] ,
generate_subscripts ( i . indkey , 1 ) as No FROM pg _ index i ) inx
INNER JOIN pg_class inxcls ON inx . indexrelid = inxcls . oid
INNER JOIN pg_namespace nsp ON inxcls . relnamespace = nsp . oid
LEFT OUTER JOIN pg_am inxam ON inxcls . relam = inxam . oid
LEFT OUTER JOIN pg_class tbl ON inx . indrelid = tbl . oid
INNER JOIN pg_attribute attr ON attr . attrelid = tbl . oid AND attr . attnum = inx . indkey [ inx . 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
AND dsc . objsubid = attr . attnum
WHERE nsp . nspname = LOWER ( a_SchemaName ) AND inxcls . relkind = 'i'
AND inxcls . relname = LOWER ( a_IndexName )
ORDER BY nsp . nspname , inxcls . relname , inx . No ;
[crayon-66214412ced16070060573/]
Особенности соединения записей, используемых системных каталогов, подробно рассмотрены в разделе «Функция admtf_Table_Indexes список индексов таблицы базы данных и их характеристик.
Функци\я admtf_Table_ComplexFeatures — комплексный список характеристик таблицы базы данных
Функция admtf_Table_ComplexFeatures возвращает комплексный список характеристик таблицы базы данных, который включает в себя характеристики, возвращаемые описанными в статье функциями. В качестве параметров функция принимает название исходной таблицы (a_TableName ) и название схемы, в пределах которой создана таблица (a_SchemaName ).
Исходный код можно посмотреть и скачать здесь.
Таблица 26. Результат выполнения функции admtf_Table_ComplexFeatures(‘public’, ‘street’).
В процессе своего выполнения функция последовательно вызывает 9 дополнительных функций, список которых приведен в разделе «Структура головной функции» .
Совместное выполнение головной и дополнительных функций приводит в результате к созданию таблицы с расширенными характеристиками таблицы .
Где использовались функции?
Обязательной частью заявки является документ «Материалы, идентифицирующие базу данных». Правила трактуют содержание этого документа следующим образом.
«Материалы, идентифицирующие базу данных, должны отражать объективную форму представления совокупности содержащихся в ней самостоятельных материалов в виде примеров реального наполнения и принципы их систематизации (структуру базы данных), позволяющие осуществить нахождение и обработку этих материалов с помощью ЭВМ.»
>Другими словами, документ должен содержать описание структуры базы данных и примеры ее реального наполнения.
Как видно из рисунка, при подготовке заявки на регистрацию базы данных использовались не только функции, описанные в этой статье. Дополнительно были созданы 3- 4 функции для преобразования описаний таблиц базы данных в формат PlantUML .Точнее, эти функции создают код в формате плагина для системы управления проектами TRAC , поэтому если вам захочется проверить созданный этими функциями код, то не забудьте убрать две строчки сверху перед @startuml и все строчки снизу после @enduml .
Созданный при помощи функций код PlantUML скрипта
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
{ { {
#!plantuml
@ startuml
object public . ID_DISTRICTS {
id_np : integer NOT NULL ( PK1 ) ( FK1 id_nps ( id_np ) )
id_district : integer NOT NULL ( PK2 )
name_district : character varying ( 25 ) NULL
type_district : character varying ( 25 ) NULL
okato : character varying ( 11 ) NULL
oktmo : character varying ( 11 ) NULL
}
object public . ID_NPS {
id_region : integer NOT NULL ( FK1 id_regions ( id_region ) )
id_atu : integer NULL ( FK1 id_rayons ( id_atu ) )
id_selsov : integer NULL ( FK1 id_selsovs ( id_selsov ) )
id_np : integer NOT NULL ( PK1 )
name_np : character varying ( 25 ) NULL
type_np : character varying ( 25 ) NULL ( FK1 type_np ( scname ) )
okato : character varying ( 11 ) NULL
oktmo : character varying ( 11 ) NULL
}
public . ID_DISTRICTS * -- public . ID_NPS
legend center
< b > < i > < u > ТАБЛИЦЫ< / u > < / i > < / b >
< b > ID_DISTRICTS < / b > - Справочник - список городских районов
< b > ID_NPS < / b > - Справочник - список населенных пунктов
endlegend
@ enduml
} } }
-- --
P.S. Почему здесь не приведены дополнительные функции преобразования описания таблиц базы данных в формат плагина PlantUML для системы управления проектами TRAC? Во-первых, они не вписывались в заявленную тему. Во-вторых, похоже, я утомил читателей текстами функций. Но, если кто-то заинтересуется этими функциями, то пишите мне, и я вышлю их тексты.
Смотрите также
Функции для документирования баз данных PostgreSQL. Начало ;
ПРИЛОЖЕНИЕ. Скрипты
Создание функции admtf_Sequence_Features
Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
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 ) ;
[crayon-66214412ced21613825218/]
Создание функции admtf_Table_Sequences
Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
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 ) ;
[crayon-66214412ced28612139371/]
Создание функции admtf_Table_Sequences без курсора (PostgreSQL 10)
Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
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 ) ;
[crayon-66214412ced30403401097/]
Создание функции admfn_Table_RowCount
Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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 ) ) ;
[crayon-66214412ced36366225710/]
Создание функции admtf_Table_InheritanceChildrens
Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
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 ) ) ;
[crayon-66214412ced3c409304378/]
Создание функции admtf_Attribute_PKFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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 ) ;
[crayon-66214412ced52883620973/]
Создание функции admtf_Attribute_FKFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
исходный код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
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 ) ;
[crayon-66214412ced57189199208/]
Создание функции admtf_Attribute_Features
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
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 ) ;
[crayon-66214412ced5e227135841/]
Создание функции admtf_PrimaryKey_Features
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
BEGIN TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Features ( a_SchemaName NAME , a_PrimaryKeyName NAME ) ;
/******************************************************************************/
/* Функция возвращает список характеристик первичного ключа таблицы, принадлежащего */
/* схеме */
/******************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Features
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_PrimaryKeyName NAME default NULL /* Название первичного ключа таблицы */
)
RETURNS TABLE ( rs_PrimaryKeyName NAME , rs_PrimaryKeyDescription TEXT ) AS
$ BODY $
DECLARE c_PrimaryKeyKind CONSTANT CHAR : = 'p' ;
v_PrimaryKeyOID OID ; /* ИД первичного ключа таблицы */
v_PrimaryKeyName NAME ; /* Название первичного ключа таблицы */
v_PrimaryKeyDescription TEXT ; /* Описание первичного ключа таблицы */
v_MasterTableName NAME ; /* Название таблицы, которой принадлежит первичный ключ */
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
SELECT INTO rs_PrimaryKeyName , rs_PrimaryKeyDescription
con . conname , COALESCE ( dsc . description , 'Первичный ключ таблицы ' || tbl . relname )
FROM pg_constraint con
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
AND dsc . objsubid = 0
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = c_PrimaryKeyKind
AND con . conname = LOWER ( a_PrimaryKeyName ) ;
RETURN QUERY SELECT rs_PrimaryKeyName , rs_PrimaryKeyDescription ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_PrimaryKey_Features ( a_SchemaName NAME , a_PrimaryKeyName NAME ) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Features ( a_SchemaName VARCHAR ( 256 ) , a_PrimaryKeyName VARCHAR ( 256 ) ) ;
/******************************************************************************/
/* Функция возвращает список характеристик первичного ключа таблицы, принадлежащего */ /* схеме */
/******************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Features
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_PrimaryKeyName VARCHAR ( 256 ) default NULL /* Название первичного ключа таблицы */
)
RETURNS TABLE ( rs_PrimaryKeyName VARCHAR ( 256 ) , rs_PrimaryKeyDescription TEXT ) AS
$ BODY $
DECLARE c_PrimaryKeyKind CONSTANT CHAR : = 'p' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT pkf . rs_PrimaryKeyName :: VARCHAR ( 256 ) ,
pkf . rs_PrimaryKeyDescription :: TEXT
FROM admtf_PrimaryKey_Features ( a_SchemaName :: NAME , a_PrimaryKeyName :: NAME ) pkf ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_PrimaryKey_Features ( a_SchemaName VARCHAR ( 256 ) , a_PrimaryKeyName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_PrimaryKey_Features ( 'public' :: NAME , 'xpkstreet' :: NAME ) ;
SELECt * FROM admtf_PrimaryKey_Features ( 'public' :: VARCHAR ( 256 ) , 'xpkstreet' :: VARCHAR ( 256 ) ) ;
[crayon-66214412ced63494228447/]
Создание функции admtf_PrimaryKey_Attributes
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Attributes ( a_SchemaName NAME , a_PrimaryKeyName NAME ) ;
/********************************************************************/
/* Функция возвращает список атрибутов первичного ключа и их характеристик */
/********************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Attributes
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_PrimaryKeyName NAME default NULL /* Название первичного ключа таблицы */
)
RETURNS TABLE ( r_PrimaryKeyNo SMALLINT , r_AttributeNumber SMALLINT ,
r_AttributeName NAME , r_UserTypeName NAME , r_TypeName NAME ,
r_isNotNULL BOOLEAN , r_Description Text ) AS
$ BODY $
DECLARE
c_PrimaryKeyKind CONSTANT CHAR : = 'p' ;
v_PrimaryKeyOID OID ; /* ИД первичного ключа таблицы */
v_PrimaryKeyName NAME ; /* Название первичного ключа таблицы */
v_PrimaryKeyDescription TEXT ; /* Описание первичного ключа таблицы */
v_MasterTableName NAME ; /* Название таблицы, которой принадлежит первичный ключ */
v_PrimaryKeyArray SMALLINT [ ] ; /* Массив порядновых номеров в таблице */
v_MasterTableOID OID ; /* ИД таблицы, которой принадлежит первичный ключ */
v_AttributeNumber SMALLINT ; /* Номер аттрибута в таблице */
v_PKAttributeCount SMALLINT ; /* Счетчик атрибутов первичного ключа*/
v_AttNo SMALLINT ; /* Порядковый номер атрибута первичного ключа*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT ( rank ( ) OVER ( PARTITION BY con . conrelid ORDER BY
attr . attnum ) ) :: SMALLINT AS r_PrimaryKeyNo ,
attr . attnum AS r_AttributeNumber , attr . attname :: NAME AS r_AttributeName ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0 THEN typ . typname :: NAME
ELSE '' :: NAME END AS r_UserTypeName ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) ,
COALESCE ( NULLIF ( typ . typtypmod , - 1 ) , attr . atttypmod ) ) :: NAME AS r_TypeName ,
attr . attnotnull AS r_isNotNULL ,
TRIM ( dsc . description ) AS r_Description
FROM ( SELECT c . oid , c . conrelid , c . connamespace , c . confrelid , c . conname ,
c . contype , c . conkey :: SMALLINT [ ] ,
consrc , c . confkey :: SMALLINT [ ] , generate_subscripts ( c . conkey , 1 ) as No
FROM pg _ constraint c ) con
INNER JOIN pg_namespace nspc ON con . connamespace = nspc . oid
INNER JOIN pg_attribute attr ON attr . attrelid = con . conrelid
AND attr . 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
AND dsc . objsubid = attr . attnum
WHERE con . contype = c_PrimaryKeyKind
AND LOWER ( nspc . nspname ) = LOWER ( a_SchemaName )
AND LOWER ( con . conname ) = LOWER ( a_PrimaryKeyName )
ORDER BY attr . attnum ;
RETURN ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_PrimaryKey_Attributes ( a_SchemaName NAME , a_PrimaryKeyName NAME ) IS 'Функция возвращает список атрибутов первичного ключа и их характеристик ' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_Attributes ( a_SchemaName VARCHAR ( 256 ) , a_PrimaryKeyName VARCHAR ( 256 ) ) ;
/********************************************************************/
/* Функция возвращает список атрибутов первичного ключа и их характеристик */
/********************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Attributes
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_PrimaryKeyName VARCHAR ( 256 ) default NULL /* Название первичного ключа таблицы */
)
RETURNS TABLE ( r_PrimaryKeyNo SMALLINT , r_AttributeNumber SMALLINT , r_AttributeName VARCHAR ( 256 ) , r_UserTypeName VARCHAR ( 256 ) , r_TypeName VARCHAR ( 256 ) , r_isNotNULL BOOLEAN , r_Description TEXT ) AS
$ BODY $
DECLARE
c_PrimaryKeyKind CONSTANT CHAR : = 'p' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT pka . r_PrimaryKeyNo :: SMALLINT , pka . r_AttributeNumber :: SMALLINT ,
pka . r_AttributeName :: VARCHAR ( 256 ) , pka . r_UserTypeName :: VARCHAR ( 256 ) ,
pka . r_TypeName :: VARCHAR ( 256 ) , pka . r_isNotNULL :: BOOLEAN ,
pka . r_Description :: TEXT
FROM admtf_PrimaryKey_Attributes ( a_SchemaName :: NAME , a_PrimaryKeyName :: NAME ) pka ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_PrimaryKey_Attributes ( a_SchemaName VARCHAR ( 256 ) , a_PrimaryKeyName VARCHAR ( 256 ) ) IS 'Функция возвращает список атрибутов первичного ключа и их характеристик' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_PrimaryKey_Attributes ( 'public' :: NAME , 'xpkstreet' :: NAME ) ;
SELECt * FROM admtf_PrimaryKey_Attributes ( 'public' :: VARCHAR ( 256 ) , 'xpkstreet' :: VARCHAR ( 256 ) ) ;
[crayon-66214412ced6a274887486/]
Создание функции admtf_PrimaryKey_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_ComplexFeatures ( a_SchemaName NAME , a_PrimaryKeyName NAME ) ;
/*****************************************************************************/
/* Функция возвращает список характеристик первичного ключа таблицы, принадлежащей */
/* схеме, а также список характеристик его атрибутов */
/*****************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_ComplexFeatures
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_PrimaryKeyName NAME default NULL /* Название первичного ключа таблицы */
)
RETURNS TABLE ( rpk_FeatureCategory VARCHAR ( 10 ) , rpk_FeatureNumber SMALLINT , rpk_FeatureName NAME , rpk_FeatureDescription TEXT ,
rpk_UserTypeName NAME , rpk_TypeName NAME , rpk_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE
c_PrimaryKeyCategory CONSTANT VARCHAR ( 10 ) : = 'pk' ; /* Категория характеристик */
/* первичного ключа таблицы */
c_AttributeCategory CONSTANT VARCHAR ( 10 ) : = 'pkatt' ; /* Категория характеристик атрибутов*/
/* первичного ключа таблицы */
v_PrimaryKeyOID OID ; /* ИД первичного ключа таблицы */
v_PrimaryKeyName NAME ; /* Название первичного ключа таблицы */
v_PrimaryKeyDescription TEXT ; /* Описание первичного ключа таблицы */
v_FeatureCategory VARCHAR ( 10 ) ; /* Категория текущей характеристики */
v_FeatureNumber SMALLINT ; /* Порядковый номер характеристики заданной категории*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
v_FeatureCategory : = c_PrimaryKeyCategory ;
v_FeatureNumber : = 0 ;
SELECT INTO v_PrimaryKeyName , v_PrimaryKeyDescription
rs_PrimaryKeyName , rs_PrimaryKeyDescription
FROM admtf_PrimaryKey_Features ( a_SchemaName , a_PrimaryKeyName ) ;
IF FOUND AND v_PrimaryKeyName IS NOT NULL THEN
RETURN QUERY SELECT v_FeatureCategory , v_FeatureNumber , v_PrimaryKeyName ,
v_PrimaryKeyDescription ,
NULL :: NAME AS rpk_UserTypeName , NULL :: NAME AS rpk_TypeName ,
NULL :: BOOLEAN AS rpk_isNotNULL ;
v_FeatureCategory : = c_AttributeCategory ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT v_FeatureCategory , r_PrimaryKeyNo , r_AttributeName , r_Description ,
r_UserTypeName , r_TypeName , r_isNotNULL
FROM admtf_PrimaryKey_Attributes ( a_SchemaName , a_PrimaryKeyName ) ;
END IF ;
RETURN ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_PrimaryKey_ComplexFeatures ( a_SchemaName NAME , a_PrimaryKeyName NAME ) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_PrimaryKey_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_PrimaryKeyName VARCHAR ( 256 ) ) ;
/******************************************************************************/
/* Функция возвращает список характеристик первичного ключа таблицы, принадлежащей */
/* схеме, а также список характеристик его атрибутов */
/******************************************************************************/
CREATE OR REPLACE FUNCTION admtf_PrimaryKey_ComplexFeatures
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_PrimaryKeyName VARCHAR ( 256 ) default NULL /* Название первичного ключа таблицы */
)
RETURNS TABLE ( rpk_FeatureCategory VARCHAR ( 10 ) , rpk_FeatureNumber SMALLINT , rpk_FeatureName VARCHAR ( 256 ) , rpk_FeatureDescription TEXT ,
rpk_UserTypeName VARCHAR ( 256 ) , rpk_TypeName VARCHAR ( 256 ) , rpk_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE
c_PrimaryKeyCategory CONSTANT VARCHAR ( 10 ) : = 'pk' ; /* Категория характеристик */
/* первичного ключа таблицы */
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT pk . rpk_FeatureCategory :: VARCHAR ( 10 ) ,
pk . rpk_FeatureNumber :: SMALLINT ,
pk . rpk_FeatureName :: VARCHAR ( 256 ) , pk . rpk_FeatureDescription :: TEXT ,
pk . rpk_UserTypeName :: VARCHAR ( 256 ) , pk . rpk_TypeName :: VARCHAR ( 256 ) ,
pk . rpk_isNotNULL :: BOOLEAN
FROM admtf_PrimaryKey_ComplexFeatures ( a_SchemaName :: NAME ,
a_PrimaryKeyName :: NAME ) pk ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_PrimaryKey_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_PrimaryKeyName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_PrimaryKey_ComplexFeatures ( 'public' :: NAME , 'xpkstreet' :: NAME ) ;
SELECt * FROM admtf_PrimaryKey_ComplexFeatures ( 'public' :: VARCHAR ( 256 ) , 'xpkstreet' :: VARCHAR ( 256 ) ) ;
[crayon-66214412ced71768948882/]
Создание функции admtf_ForeignKey_Features
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_Features ( a_SchemaName NAME , a_ForeignKeyName NAME ) ;
/**************************************************************/
/* Функция возвращает список характеристик внешнего ключа таблицы, */
/* принадлежащего схеме */
/**************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_Features
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_ForeignKeyName NAME default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( rs_ForeignKeyName NAME , rs_ForeignKeyDescription TEXT ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
v_ForeignKeyOID OID ; /* ИД внешнего ключа таблицы */
v_ForeignKeyName NAME ; /* Название внешнего ключа таблицы */
v_ForeignKeyDescription TEXT ; /* Описание внешнего ключа таблицы */
v_MasterTableName NAME ; /* Название таблицы, которой принадлежит первичный ключ */
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
SELECT INTO rs_ForeignKeyName , rs_ForeignKeyDescription
con . conname , COALESCE ( dsc . description , 'Внешний ключ таблицы ' || tbl . relname )
FROM pg_constraint con
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
AND dsc . objsubid = 0
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = c_ForeignKeyKind
AND con . conname = LOWER ( a_ForeignKeyName ) ;
RETURN QUERY SELECT rs_ForeignKeyName , rs_ForeignKeyDescription ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_Features ( a_SchemaName NAME , a_ForeignKeyName NAME ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_Features ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) ;
/********************************************************************************************************/
/* Функция возвращает список характеристик внешнего ключа таблицы, принадлежащего схеме */
/********************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_Features
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_ForeignKeyName VARCHAR ( 256 ) default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( rs_ForeignKeyName VARCHAR ( 256 ) , rs_ForeignKeyDescription TEXT ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT fkf . rs_ForeignKeyName :: VARCHAR ( 256 ) ,
fkf . rs_ForeignKeyDescription :: TEXT
FROM admtf_ForeignKey_Features
( a_SchemaName :: NAME , a_ForeignKeyName :: NAME ) fkf ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_Features ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_ForeignKey_Features ( 'public' :: VARCHAR ( 256 ) , 'fk_street_locality' :: VARCHAR ( 256 ) ) ;
SELECt * FROM admtf_ForeignKey_Features ( 'public' :: NAME , 'fk_street_locality' :: NAME ) ;
[crayon-66214412ced79646094844/]
Создание функции admtf_ForeignKey_Attributes
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_Attributes ( a_SchemaName NAME , a_ForeignKeyName NAME ) ;
/**************************************************************/
/* Функция возвращает список характеристик внешнего ключа таблицы, */
/* принадлежащего схеме */
/**************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_Attributes
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_ForeignKeyName NAME default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( r_ForeignKeyNo SMALLINT , r_AttributeNumber SMALLINT , r_AttributeName NAME , r_UserTypeName NAME , r_TypeName NAME , r_isNotNULL BOOLEAN , r_Description Text ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT ( rank ( ) OVER ( PARTITION BY con . conrelid ORDER BY con . No ) ) :: SMALLINT
AS r_ForeingKeyNo ,
attr . attnum AS r_AttributeNumber , attr . attname :: NAME AS r_AttributeName ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0 THEN typ . typname :: NAME
ELSE '' END AS r_UserTypeName ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) ,
COALESCE ( NULLIF ( typ . typtypmod , - 1 ) , attr . atttypmod ) ) :: NAME
AS r_TypeName ,
attr . attnotnull AS r_isNotNULL , TRIM ( dsc . description ) AS r_Description
FROM ( SELECT c . oid , c . conrelid , c . confrelid , c . conname , c . connamespace ,
c . contype , c . conkey :: SMALLINT [ ] , c . consrc , c . confkey :: SMALLINT [ ] ,
generate_subscripts ( c . conkey , 1 ) as No FROM pg _ constraint c ) con
INNER JOIN pg_namespace nspc ON con . connamespace = nspc . oid
INNER JOIN pg_attribute attr ON attr . attrelid = con . conrelid
AND attr . 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
AND dsc . objsubid = attr . attnum
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = c_ForeignKeyKind
AND con . conname = LOWER ( a_ForeignKeyName )
ORDER BY con . No ;
RETURN ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_Attributes ( a_SchemaName NAME , a_ForeignKeyName NAME ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_Attributes ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) ;
/**************************************************************/
/* Функция возвращает список характеристик внешнего ключа таблицы, */
/* принадлежащего схеме */
/**************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_Attributes
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_ForeignKeyName VARCHAR ( 256 ) default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( r_ForeignKeyNo SMALLINT , r_AttributeNumber SMALLINT , r_AttributeName VARCHAR ( 256 ) , r_UserTypeName VARCHAR ( 256 ) , r_TypeName VARCHAR ( 256 ) , r_isNotNULL BOOLEAN , r_Description Text ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT fka . r_ForeignKeyNo :: SMALLINT , fka . r_AttributeNumber :: SMALLINT ,
fka . r_AttributeName :: VARCHAR ( 256 ) ,
fka . r_UserTypeName :: VARCHAR ( 256 ) , fka . r_TypeName :: VARCHAR ( 256 ) ,
fka . r_isNotNULL :: BOOLEAN , fka . r_Description :: TEXT
FROM admtf_ForeignKey_Attributes ( a_SchemaName :: NAME , a_ForeignKeyName :: NAME ) fka ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_Attributes ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_ForeignKey_Attributes ( 'public' :: NAME , 'fk_mapHouse_MapStreet' :: NAME ) ;
SELECt * FROM admtf_ForeignKey_Attributes ( 'public' :: NAME , 'fk_street_locality' :: NAME ) ;
SELECt * FROM admtf_ForeignKey_Attributes ( 'public' :: NAME , 'fk_street_streettype' :: NAME ) ;
SELECt * FROM admtf_ForeignKey_Attributes ( 'public' :: VARCHAR ( 256 ) , 'fk_street_locality' :: VARCHAR ( 256 ) ) ;
SELECt * FROM admtf_ForeignKey_Attributes ( 'public' :: VARCHAR ( 256 ) , 'fk_street_streettype' :: VARCHAR ( 256 ) ) ;
[crayon-66214412ced7e191530282/]
Создание функции admtf_ForeignKey_ReferenceTableFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableFeatures ( a_SchemaName NAME , a_ForeignKeyName NAME ) ;
/*******************************************************************/
/* Функция возвращает список характеристик таблицы, на которую ссылается */
/* внешний ключ */
/*******************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableFeatures
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_ForeignKeyName NAME default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( rfkrt_ReferenceTableName NAME , rfkrt_ReferenceTableDescription TEXT ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
v_ReferenceTableOID OID ; /* ИД таблицы, на которую ссылается внешний ключ */
v_ReferenceTableName NAME ; /* Название таблицы, на которую ссылается внешний ключ*/
v_ReferenceTableDescription TEXT ; /*Описание таблицы, на которую ссылается внешний ключ */
v_MasterTableName NAME ; /* Название таблицы, которой принадлежит внешний ключ */
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
SELECT INTO v_ReferenceTableName rtbl . relname
FROM pg_constraint con
INNER JOIN pg_namespace nspc ON con . connamespace = nspc . oid
INNER JOIN pg_class rtbl ON con . confrelid = rtbl . oid
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = c_ForeignKeyKind
AND con . conname = LOWER ( a_ForeignKeyName ) ;
IF FOUND THEN
RETURN QUERY SELECT rs_TableName , rs_TableDescription FROM
admtf_Table_Features ( a_SchemaName , v_ReferenceTableName ) ;
END IF ;
RETURN ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableFeatures ( a_SchemaName NAME , a_ForeignKeyName NAME ) IS 'Возвращает список характеристик таблицы, на которую ссылается внешний ключ' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableFeatures ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) ;
/*******************************************************************/
/* Функция возвращает список характеристик таблицы, на которую ссылается */
/* внешний ключ */
/******************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableFeatures
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_ForeignKeyName VARCHAR ( 256 ) default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( rfkrt_ReferenceTableName VARCHAR ( 256 ) , rfkrt_ReferenceTableDescription TEXT ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT fkrt . rfkrt_ReferenceTableName :: VARCHAR ( 256 ) ,
fkrt . rfkrt_ReferenceTableDescription :: TEXT
FROM admtf_ForeignKey_ReferenceTableFeatures ( a_SchemaName :: NAME ,
a_ForeignKeyName :: NAME ) fkrt ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableFeatures ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик таблицы, на которую ссылается внешний ключ' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_ForeignKey_ReferenceTableFeatures ( 'public' :: VARCHAR ( 256 ) , 'fk_street_locality' :: VARCHAR ( 256 ) ) ;
SELECt * FROM admtf_ForeignKey_ReferenceTableFeatures ( 'public' :: NAME , 'fk_street_locality' :: NAME ) ;
[crayon-66214412ced84205391319/]
Создание функции admtf_ForeignKey_ReferenceTableAttributes
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
Комментарии к исходному коду функции можно посмотреть здесь.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableAttributes ( a_SchemaName NAME , a_ForeignKeyName NAME ) ;
/******************************************************************/
/* Функция возвращает список характеристик атрибутов таблицы, на которую */
/* сылается внешний ключ */
/******************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableAttributes
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_ForeignKeyName NAME default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( r_ReferenceTableKeyNo SMALLINT , r_AttributeNumber SMALLINT , r_AttributeName NAME , r_UserTypeName NAME , r_TypeName NAME , r_isNotNULL BOOLEAN , r_Description Text ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
v_ForeignKeyName NAME ; /* Название внешнего ключа таблицы */
v_ForeignKeyDescription TEXT ; /* Описание внешнего ключа таблицы */
v_ReferenceTableKeyArray SMALLINT [ ] ; /* Массив порядновых номеров в таблице, */
/* на которую сылается внешний ключ */
v_ReferenceTableName NAME ; /* Наименование таблицы, на которую ссылается внешний ключ */
v_ReferenceTableDescription TEXT ; /* Описание таблицы, на которую ссылается внешний ключ */
v_ReferenceTableOID OID ; /* ИД таблицы, которой принадлежит внешний ключ */
v_AttributeNumber SMALLINT ; /* Номер аттрибута в таблице */
v_FKAttributeCount INTEGER ; /* Счетчик атрибутов внешнего ключа*/
v_AttNo SMALLINT ; /* Порядковый номер атрибута внешнего ключа*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT ( rank ( ) OVER ( PARTITION BY con . confrelid
ORDER BY con . No ) ) :: SMALLINT AS r_ReferenceTableKeyNo ,
attr . attnum AS r_AttributeNumber , attr . attname :: NAME AS r_AttributeName ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0 THEN typ . typname :: NAME
ELSE '' END AS r_UserTypeName ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) ,
COALESCE ( NULLIF ( typ . typtypmod , - 1 ) ,
attr . atttypmod ) ) :: NAME AS r_TypeName ,
attr . attnotnull AS r_isNotNULL , TRIM ( dsc . description ) AS r_Description
FROM ( SELECT c . oid , c . conrelid , c . confrelid , c . conname , c . connamespace , c . contype ,
c . conkey :: SMALLINT [ ] , c . consrc , c . confkey :: SMALLINT [ ] ,
generate_subscripts ( c . conkey , 1 ) as No FROM pg _ constraint c ) con
INNER JOIN pg_namespace nspc ON con . connamespace = nspc . oid
INNER JOIN pg_attribute attr ON attr . attrelid = con . confrelid
AND attr . attnum = con . confkey [ 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
AND dsc . objsubid = attr . attnum
WHERE nspc . nspname = LOWER ( a_SchemaName ) AND con . contype = c_ForeignKeyKind
AND con . conname = LOWER ( a_ForeignKeyName )
ORDER BY con . No ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableAttributes ( a_SchemaName NAME , a_ForeignKeyName NAME ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableAttributes ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) ;
/*********************************************************/
/* Функция возвращает список характеристик атрибутов таблицы, */
/* на которую сылается внешний ключ */
/********************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableAttributes
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_ForeignKeyName VARCHAR ( 256 ) default NULL /* Название внешнего ключа таблицы */
)
RETURNS TABLE ( r_ReferenceTableKeyNo SMALLINT , r_AttributeNumber SMALLINT , r_AttributeName VARCHAR ( 256 ) , r_UserTypeName VARCHAR ( 256 ) , r_TypeName VARCHAR ( 256 ) , r_isNotNULL BOOLEAN , r_Description TEXT ) AS
$ BODY $
DECLARE c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT fkra . r_ReferenceTableKeyNo :: SMALLINT ,
fkra . r_AttributeNumber :: SMALLINT , fkra . r_AttributeName :: VARCHAR ( 256 ) ,
fkra . r_UserTypeName :: VARCHAR ( 256 ) , fkra . r_TypeName :: VARCHAR ( 256 ) ,
fkra . r_isNotNULL :: BOOLEAN , fkra . r_Description :: TEXT
FROM admtf_ForeignKey_ReferenceTableAttributes ( a_SchemaName :: NAME ,
a_ForeignKeyName :: NAME ) fkra ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableAttributes ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_ForeignKey_ReferenceTableAttributes ( 'public' :: VARCHAR ( 256 ) , 'fk_street_locality' :: VARCHAR ( 256 ) ) ;
SELECt * FROM admtf_ForeignKey_ReferenceTableAttributes ( 'public' :: NAME , 'fk_street_locality' :: NAME ) ;
Создание функции admtf_ForeignKey_ReferenceTableComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableComplexFeatures ( a_SchemaName NAME , a_ForeignKeyName NAME , a_ForeignKeyNo SMALLINT ) ;
/*************************************************************/
/* Функция возвращает список характеристик таблицы, которую */
/* ссылается внешний ключ, а также список характеристик ее атрибутов */
/*************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_ForeignKeyName NAME default NULL , /* Название внешнего ключа таблицы */
a_ForeignKeyNo SMALLINT default NULL /* Порядковый номер внешнего ключа таблицы*/
)
RETURNS TABLE ( fkrt_FeatureCategory VARCHAR ( 10 ) , fkrt_FeatureNumber SMALLINT , fkrt_FeatureName NAME , fkrt_FeatureDescription TEXT , fkrt_UserTypeName NAME , fkrt_TypeName NAME , fkrt_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE c_WildChar CONSTANT VARCHAR ( 1 ) : = '%' ;
c_ForeignKeyCategory CONSTANT VARCHAR ( 10 ) : = 'fk' || c_WildChar || 'rtbl' ; /* Категория */
/* характеристик таблицы, на которую ссылается внешний ключ */
c_AttributeCategory CONSTANT VARCHAR ( 10 ) : = 'fk' || c_WildChar || 'ratt' ; /* Категория */
/* характеристик атрибутов таблицы, на которую ссылается внешний ключ */
v_ForeignKeyCharNo VARCHAR ( 2 ) ; /* Порядковый номер внешнего ключа таблицы*/
v_ForeignKeyOID OID ; /* ИД внешнего ключа таблицы */
v_ForeignKeyName NAME ; /* Название внешнего ключа таблицы */
v_ForeignKeyDescription TEXT ; /* Описание внешнего ключа таблицы */
v_FeatureCategory VARCHAR ( 10 ) ; /* Категория текущей характеристики */
v_FeatureNumber SMALLINT ; /* Порядковый номер характеристики заданной категории*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
v_ForeignKeyCharNo : = COALESCE ( TRIM ( TO_CHAR ( a_ForeignKeyNo , '09' ) ) , '' ) ;
v_FeatureCategory : = REPLACE ( c_ForeignKeyCategory , c_WildChar , v_ForeignKeyCharNo ) ;
v_FeatureNumber : = 0 ;
SELECT INTO v_ForeignKeyName , v_ForeignKeyDescription
rfkrt_ReferenceTableName , rfkrt_ReferenceTableDescription
FROM admtf_ForeignKey_ReferenceTableFeatures ( a_SchemaName , a_ForeignKeyName ) ;
IF FOUND AND v_ForeignKeyName IS NOT NULL THEN
RETURN QUERY SELECT
v_FeatureCategory , v_FeatureNumber , v_ForeignKeyName ,
v_ForeignKeyDescription , NULL :: NAME AS fkrt_UserTypeName ,
NULL :: NAME AS fkrt_TypeName , NULL :: BOOLEAN AS fkrt _ isNotNULL ;
END IF ;
v_FeatureCategory : = REPLACE ( c_AttributeCategory , c_WildChar , v_ForeignKeyCharNo ) ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT v_FeatureCategory , r_ReferenceTableKeyNo , r_AttributeName ,
r_Description , r_UserTypeName , r_TypeName , r_isNotNULL
FROM admtf_ForeignKey_ReferenceTableAttributes ( a_SchemaName , a_ForeignKeyName ) ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures ( a_SchemaName NAME , a_ForeignKeyName NAME , a_ForeignKeyNo SMALLINT ) IS 'Возвращает список характеристик таблицы, которую ссылается внешний ключ, а также список характеристик ее атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) , a_ForeignKeyNo SMALLINT ) ;
/*************************************************************/
/* Функция возвращает список характеристик таблицы, которую */
/* ссылается внешний ключ, а также список характеристик ее атрибутов */
/*************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_ForeignKeyName VARCHAR ( 256 ) default NULL , /* Название внешнего ключа таблицы */
a_ForeignKeyNo SMALLINT default NULL /* Порядковый номер внешнего ключа таблицы*/
)
RETURNS TABLE ( fkrt_FeatureCategory VARCHAR ( 10 ) , fkrt_FeatureNumber SMALLINT , fkrt_FeatureName VARCHAR ( 256 ) , fkrt_FeatureDescription TEXT ,
fkrt_UserTypeName VARCHAR ( 256 ) , fkrt_TypeName VARCHAR ( 256 ) , fkrt_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE c_WildChar CONSTANT VARCHAR ( 1 ) : = '%' ; /* Категория характеристик */
/* внешнего ключа таблицы */
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT fkrt . fkrt_FeatureCategory :: VARCHAR ( 10 ) ,
fkrt . fkrt_FeatureNumber :: SMALLINT ,
fkrt . fkrt_FeatureName :: VARCHAR ( 256 ) , fkrt . fkrt_FeatureDescription :: TEXT ,
fkrt . fkrt_UserTypeName :: VARCHAR ( 256 ) ,
fkrt . fkrt_TypeName :: VARCHAR ( 256 ) , fkrt . fkrt_isNotNULL :: BOOLEAN
FROM admtf_ForeignKey_ReferenceTableComplexFeatures ( a_SchemaName :: NAME ,
a_ForeignKeyName :: NAME , a_ForeignKeyNo :: SMALLINT ) fkrt ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) , a_ForeignKeyNo SMALLINT ) IS 'Возвращает список характеристик таблицы, которую ссылается внешний ключ, а также список характеристик ее атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_ForeignKey_ReferenceTableComplexFeatures ( 'public' :: NAME , 'fk_street_locality' :: NAME ) ;
SELECt * FROM admtf_ForeignKey_ReferenceTableComplexFeatures ( 'public' :: NAME , 'fk_street_locality' :: NAME , 10 :: SMALLINT ) ;
SELECt * FROM admtf_ForeignKey_ReferenceTableComplexFeatures ( 'public' :: VARCHAR ( 256 ) , 'fk_street_locality' :: VARCHAR ( 256 ) , 10 :: SMALLINT ) ;
[crayon-66214412ced92002744134/]
Создание функции admtf_ForeignKey_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ComplexFeatures ( a_SchemaName NAME , a_ForeignKeyName NAME , a_ForeignKeyNo SMALLINT ) ;
/**************************************************************/
/* Функция возвращает список характеристик внешнего ключа таблицы, */
/* принадлежащей схеме, а также список характеристик его атрибутов */
/**************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ComplexFeatures
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_ForeignKeyName NAME default NULL , /* Название внешнего ключа таблицы */
a_ForeignKeyNo SMALLINT default NULL /* Порядковый номер внешнего ключа таблицы*/
)
RETURNS TABLE ( rfk_FeatureCategory VARCHAR ( 10 ) , rfk_FeatureNumber SMALLINT , rfk_FeatureName NAME , rfk_FeatureDescription TEXT ,
rfk_UserTypeName NAME , rfk_TypeName NAME , rfk_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE c_WildChar CONSTANT VARCHAR ( 1 ) : = '%' ;
c_ForeignKeyCategory CONSTANT VARCHAR ( 10 ) : = 'fk' || c_WildChar ; /* Категория характеристик */
/* внешнего ключа таблицы */
c_AttributeCategory CONSTANT VARCHAR ( 10 ) : = 'fk' || c_WildChar || 'att' ; /* Категория */
/* характеристик */
/* атрибутов внешнего ключа таблицы */
v_ForeignKeyOID OID ; /* ИД внешнего ключа таблицы */
v_ForeignKeyName NAME ; /* Название внешнего ключа таблицы */
v_ForeignKeyCharNo VARCHAR ( 2 ) ; /* Порядковый номер внешнего ключа таблицы*/
v_ForeignKeyDescription TEXT ; /* Описание внешнего ключа таблицы */
v_FeatureCategory VARCHAR ( 10 ) ; /* Категория текущей характеристики */
v_FeatureNumber SMALLINT ; /* Порядковый номер характеристики заданной категории*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
v_ForeignKeyCharNo : = COALESCE ( TRIM ( TO_CHAR ( a_ForeignKeyNo , '09' ) ) , '' ) ;
v_FeatureCategory : = REPLACE ( c_ForeignKeyCategory , c_WildChar , v_ForeignKeyCharNo ) ;
v_FeatureNumber : = 0 ;
SELECT INTO v_ForeignKeyName , v_ForeignKeyDescription
rs_ForeignKeyName , rs_ForeignKeyDescription
FROM admtf_ForeignKey_Features ( a_SchemaName , a_ForeignKeyName ) ;
IF FOUND AND v_ForeignKeyName IS NOT NULL THEN
RETURN QUERY SELECT v_FeatureCategory , COALESCE ( a_ForeignKeyNo , v_FeatureNumber ) ,
v_ForeignKeyName , v_ForeignKeyDescription ,
NULL :: NAME AS rfk_UserTypeName , NULL :: NAME AS rfk_TypeName ,
NULL :: BOOLEAN AS rfk_isNotNULL ;
END IF ;
v_FeatureCategory : = REPLACE ( c_AttributeCategory , c_WildChar , v_ForeignKeyCharNo ) ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT v_FeatureCategory , r_ForeignKeyNo , r_AttributeName , r_Description ,
r_UserTypeName , r_TypeName , r_isNotNULL
FROM admtf_ForeignKey_Attributes ( a_SchemaName , a_ForeignKeyName ) ;
RETURN QUERY SELECT fkrt_FeatureCategory , fkrt_FeatureNumber , fkrt_FeatureName ,
fkrt_FeatureDescription , fkrt_UserTypeName , fkrt_TypeName ,
fkrt_isNotNULL AS rfk_isNotNULL
FROM admtf_ForeignKey_ReferenceTableComplexFeatures ( a_SchemaName ,
a_ForeignKeyName , a_ForeignKeyNo ) ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ComplexFeatures ( a_SchemaName NAME , a_ForeignKeyName NAME , a_ForeignKeyNo SMALLINT ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_ForeignKey_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) , a_ForeignKeyNo SMALLINT ) ;
/**************************************************************/
/* Функция возвращает список характеристик внешнего ключа таблицы, */
/* принадлежащей схеме, а также список характеристик его атрибутов */
/**************************************************************/
CREATE OR REPLACE FUNCTION admtf_ForeignKey_ComplexFeatures
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_ForeignKeyName VARCHAR ( 256 ) default NULL , /* Название внешнего ключа таблицы */
a_ForeignKeyNo SMALLINT default NULL /* Порядковый номер внешнего ключа таблицы*/
)
RETURNS TABLE ( rfk_FeatureCategory VARCHAR ( 10 ) , rfk_FeatureNumber SMALLINT , rfk_FeatureName VARCHAR ( 256 ) , rfk_FeatureDescription TEXT ,
rfk_UserTypeName VARCHAR ( 256 ) , rfk_TypeName VARCHAR ( 256 ) , rfk_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE c_WildChar CONSTANT VARCHAR ( 1 ) : = '%' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT fkcf . rfk_FeatureCategory :: VARCHAR ( 10 ) ,
fkcf . rfk_FeatureNumber :: SMALLINT ,
fkcf . rfk_FeatureName :: VARCHAR ( 256 ) , fkcf . rfk_FeatureDescription :: TEXT ,
fkcf . rfk_UserTypeName :: VARCHAR ( 256 ) , fkcf . rfk_TypeName :: VARCHAR ( 256 ) ,
fkcf . rfk_isNotNULL :: BOOLEAN
FROM admtf_ForeignKey_ComplexFeatures ( a_SchemaName :: NAME ,
a_ForeignKeyName :: NAME , a_ForeignKeyNo :: SMALLINT ) fkcf ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_ForeignKey_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_ForeignKeyName VARCHAR ( 256 ) , a_ForeignKeyNo SMALLINT ) IS 'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_ForeignKey_ComplexFeatures ( 'public' :: NAME , 'fk_street_locality' :: NAME ) ;
SELECt * FROM admtf_ForeignKey_ComplexFeatures ( 'public' :: NAME , 'fk_street_locality' :: NAME , 1 :: SMALLINT ) ;
SELECt * FROM admtf_ForeignKey_ComplexFeatures ( 'public' :: VARCHAR , 'fk_street_locality' :: VARCHAR , 3 :: SMALLINT ) ;
[crayon-66214412ced9a768467277/]
Создание функции admtf_Index_Features
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Index_Features ( a_SchemaName NAME , a_IndexName NAME ) ;
/*******************************************************/
/* Функция возвращает список характеристик индекса таблицы, */
/* принадлежащей схеме */
/*******************************************************/
CREATE OR REPLACE FUNCTION admtf_Index_Features
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_IndexName NAME default NULL /* Название индекса таблицы */
)
RETURNS TABLE ( rs_IndexName NAME , rs_IndexDescription TEXT ) AS
$ BODY $
DECLARE c_IndexKind CONSTANT CHAR : = 'i' ;
v_IndexOID OID ; /* ИД индекса таблицы */
v_IndexName NAME ; /* Название индекса таблицы */
v_IndexDescription TEXT ; /* Описание индекса таблицы */
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
SELECT INTO rs_IndexName , rs_IndexDescription
inxcls . relname ,
CASE WHEN COALESCE ( TRIM ( dsc . description ) , '' ) = ''
THEN 'Индекс' || CASE WHEN inx . indisunique
THEN ' уникальный' ||
CASE WHEN inx . indisprimary
THEN '(первичный ключ)' ELSE '' END
ELSE '' END ||
CASE WHEN inxam . amname = 'gist'
THEN ' пространственный' ELSE '' END ||
' таблицы ' || tbl . relname
ELSE dsc . description END
FROM pg_index inx
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
AND dsc . 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 nsp . nspname = LOWER ( a_SchemaName )
AND inxcls . relkind = c_IndexKind
AND inxcls . relname = LOWER ( a_IndexName ) ;
RETURN QUERY SELECT rs_IndexName , rs_IndexDescription ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Index_Features ( a_SchemaName NAME , a_IndexName NAME ) IS 'Возвращает список характеристик индекса таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Index_Features ( a_SchemaName VARCHAR ( 256 ) , a_IndexName VARCHAR ( 256 ) ) ;
/*******************************************************/
/* Функция возвращает список характеристик индекса таблицы, */
/* принадлежащей схеме */
/*******************************************************/
CREATE OR REPLACE FUNCTION admtf_Index_Features
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_IndexName VARCHAR ( 256 ) default NULL /* Название индекса таблицы */
)
RETURNS TABLE ( rs_IndexName VARCHAR ( 256 ) , rs_IndexDescription TEXT ) AS
$ BODY $
DECLARE c_IndexKind CONSTANT CHAR : = 'i' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT ixf . rs_IndexName :: VARCHAR ( 256 ) ,
ixf . rs_IndexDescription :: TEXT
FROM admtf_Index_Features ( a_SchemaName :: NAME , a_IndexName :: NAME ) ixf ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Index_Features ( a_SchemaName VARCHAR ( 256 ) , a_IndexName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик индекса таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_Index_Features ( 'public' :: NAME , 'xie1street' :: NAME ) ;
SELECt * FROM admtf_Index_Features ( 'public' :: VARCHAR ( 256 ) , 'xie1street' :: VARCHAR ( 256 ) ) ;
SELECt * FROM admtf_Index_Features ( 'public' :: VARCHAR ( 256 ) , 'xie9street' :: VARCHAR ( 256 ) ) ;
[crayon-66214412ceda3641409186/]
Создание функции Создание функции admtf_Index_Attributes
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Index_Attributes ( a_SchemaName NAME , a_IndexName NAME ) ;
/*******************************************************/
/* Функция возвращает список характеристик индекса таблицы, */
/* принадлежащего схеме */
/*******************************************************/
CREATE OR REPLACE FUNCTION admtf_Index_Attributes
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_IndexName NAME default NULL /* Название индекса таблицы */
)
RETURNS TABLE ( r_IndexNo SMALLINT , r_AttributeNumber SMALLINT , r_AttributeName NAME , r_OrderDirect VARCHAR ( 10 ) , r_UserTypeName NAME , r_TypeName NAME , r_isNotNULL BOOLEAN , r_Description Text ) AS
$ BODY $
DECLARE cursor_IndexNoOfAttribute refcursor ; /* номера атрибутов индекса*/
c_IndexKind CONSTANT CHAR : = 'i' ;
v_IndexOID OID ; /* ИД индекса таблицы */
v_IndexName NAME ; /* Название индекса таблицы */
v_IndexDescription TEXT ; /* Описание индекса таблицы */
v_MasterTableName NAME ; /* Название таблицы, которой принадлежит индекс */
v_IndexArray SMALLINT [ ] ; /* Массив порядновых номеров в таблице */
v_IndexKeyOps SMALLINT [ ] ; /* Массив опций атрибутов индекса*/
v_AmCanOrder BOOLEAN ; /* Поддерживает ли метод доступа */
/* упорядоченное сканирование */
/*по значению индексируемого столбца?*/
v_MasterTableOID OID ; /* ИД таблицы, которой принадлежит индекс */
v_AttributeNumber SMALLINT ; /* Номер аттрибута в таблице */
v_AttributeOrderCode INTEGER ; /* Признак упорядоченности атрибута*/
v_IndexAttributeCount INTEGER ; /* Счетчик атрибутов индекса*/
v_AttNo SMALLINT ; /* Порядковый номер атрибута индекса*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT ( inx . No + 1 ) :: SMALLINT AS r_IndexNo ,
attr . attnum :: SMALLINT AS r_AttributeNumber ,
attr . attname :: NAME AS r_AttributeName ,
CASE WHEN NOT inxam . amcanorder
THEN NULL
ELSE CASE WHEN inx . indoption [ inx . No ] & 1 = 1
THEN 'DESC'
ELSE 'ASC' END
END :: VARCHAR ( 10 ) AS r_OrderDirect ,
CASE WHEN COALESCE ( typ . typbasetype , 0 ) > 0
THEN typ . typname :: NAME
ELSE '' END AS r_UserTypeName ,
FORMAT_TYPE ( COALESCE ( NULLIF ( typ . typbasetype , 0 ) , typ . oid ) ,
COALESCE ( NULLIF ( typ . typtypmod , - 1 ) ,
attr . atttypmod ) ) :: NAME AS r_TypeName ,
attr . attnotnull AS r_isNotNULL ,
dsc . description AS r_Description
FROM ( SELECT i . indrelid , i . indexrelid , i . indkey :: SMALLINT [ ] ,
i . indoption :: SMALLINT [ ] ,
generate_subscripts ( i . indkey , 1 ) as No FROM pg _ index i ) inx
INNER JOIN pg_class inxcls ON inx . indexrelid = inxcls . oid
INNER JOIN pg_namespace nsp ON inxcls . relnamespace = nsp . oid
LEFT OUTER JOIN pg_am inxam ON inxcls . relam = inxam . oid
LEFT OUTER JOIN pg_class tbl ON inx . indrelid = tbl . oid
INNER JOIN pg_attribute attr ON attr . attrelid = tbl . oid
AND attr . attnum = inx . indkey [ inx . 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
AND dsc . objsubid = attr . attnum
WHERE nsp . nspname = LOWER ( a_SchemaName )
AND inxcls . relkind = c_IndexKind
AND inxcls . relname = LOWER ( a_IndexName )
ORDER BY nsp . nspname , inxcls . relname , inx . No ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Index_Attributes ( a_SchemaName NAME , a_IndexName NAME ) IS 'Возвращает список характеристик индекса таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Index_Attributes ( a_SchemaName VARCHAR ( 256 ) , a_IndexName VARCHAR ( 256 ) ) ;
/*******************************************************/
/* Функция возвращает список характеристик индекса таблицы, */
/* принадлежащего схеме */
/******************************************************/
CREATE OR REPLACE FUNCTION admtf_Index_Attributes
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_IndexName VARCHAR ( 256 ) default NULL /* Название индекса таблицы */
)
RETURNS TABLE ( r_IndexNo SMALLINT , r_AttributeNumber SMALLINT , r_AttributeName VARCHAR ( 256 ) , r_OrderDirect VARCHAR ( 10 ) , r_UserTypeName VARCHAR ( 256 ) , r_TypeName VARCHAR ( 256 ) , r_isNotNULL BOOLEAN , r_Description Text ) AS
$ BODY $
DECLARE c_IndexKind CONSTANT CHAR : = 'i' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT ia . r_IndexNo :: SMALLINT ,
ia . r_AttributeNumber :: SMALLINT ,
ia . r_AttributeName :: VARCHAR ( 256 ) ,
ia . r_OrderDirect :: VARCHAR ( 10 ) ,
ia . r_UserTypeName :: VARCHAR ( 256 ) ,
ia . r_TypeName :: VARCHAR ( 256 ) ,
ia . r_isNotNULL :: BOOLEAN ,
ia . r_Description :: TEXT
FROM admtf_Index_Attributes ( a_SchemaName :: NAME , a_IndexName :: NAME ) ia ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Index_Attributes ( a_SchemaName VARCHAR ( 256 ) , a_IndexName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик индекса таблицы, принадлежащей схеме' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECt * FROM admtf_Index_Attributes ( 'public' :: NAME , 'xie1street' :: NAME ) ;
SELECt * FROM admtf_Index_Attributes ( 'public' :: VARCHAR ( 256 ) , 'xie1street' :: VARCHAR ( 256 ) ) ;
SELECt * FROM admtf_Index_Attributes ( 'public' :: VARCHAR ( 256 ) , 'xie9street' :: VARCHAR ( 256 ) ) ;
[crayon-66214412cedaa391959558/]
Создание функции admtf_Index_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Index_ComplexFeatures ( a_SchemaName NAME , a_IndexName NAME , a_IndexNo SMALLINT ) ;
/************************************************************/
/* Функция возвращает список характеристик индекса таблицы, */
/* принадлежащей схеме, а также список характеристик его атрибутов */
/************************************************************/
CREATE OR REPLACE FUNCTION admtf_Index_ComplexFeatures
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_IndexName NAME default NULL , /* Название индекса таблицы */
a_IndexNo SMALLINT default NULL /* Порядковый индекса таблицы*/
)
RETURNS TABLE ( rix_FeatureCategory VARCHAR ( 10 ) , rix_FeatureNumber SMALLINT , rix_FeatureName NAME , rix_OrderDirect VARCHAR ( 10 ) , rix_FeatureDescription TEXT ,
rix_UserTypeName NAME , rix_TypeName NAME , rix_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE
c_WildChar CONSTANT VARCHAR ( 1 ) : = '%' ;
c_IndexCategory CONSTANT VARCHAR ( 10 ) : = 'idx' || c_WildChar ; /* Категория */
/* характеристик индекса таблицы */
c_AttributeCategory CONSTANT VARCHAR ( 10 ) : = 'idx' || c_WildChar || 'att' ; /* Категория */
/* характеристик атрибутов индекса таблицы */
v_IndexOID OID ; /* ИД индекса таблицы */
v_IndexName NAME ; /* Название индекса таблицы */
v_IndexCharNo VARCHAR ( 2 ) ; /* Порядковый номер индекса таблицы*/
v_IndexDescription TEXT ; /* Описание индекса таблицы */
v_FeatureCategory VARCHAR ( 10 ) ; /* Категория текущей характеристики */
v_FeatureNumber SMALLINT ; /* Порядковый номер характеристики заданной категории*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
v_IndexCharNo : = COALESCE ( TRIM ( TO_CHAR ( a_IndexNo , '09' ) ) , '' ) ;
v_FeatureCategory : = REPLACE ( c_IndexCategory , c_WildChar , v_IndexCharNo ) ;
v_FeatureNumber : = 0 ;
SELECT INTO v_IndexName , v_IndexDescription rs_IndexName , rs_IndexDescription
FROM admtf_Index_Features ( a_SchemaName , a_IndexName ) ;
IF FOUND AND v_IndexName IS NOT NULL THEN
RETURN QUERY SELECT v_FeatureCategory ,
COALESCE ( a_IndexNo , v_FeatureNumber ) ,
v_IndexName , NULL :: VARCHAR ( 10 ) ,
v_IndexDescription ,
NULL :: NAME AS rix_UserTypeName ,
NULL :: NAME AS rix_TypeName ,
NULL :: BOOLEAN AS rix_isNotNULL ;
END IF ;
v_FeatureCategory : = REPLACE ( c_AttributeCategory , c_WildChar , v_IndexCharNo ) ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT v_FeatureCategory , r_IndexNo ,
r_AttributeName , r_OrderDirect , r_Description ,
r_UserTypeName , r_TypeName , r_isNotNULL
FROM admtf_Index_Attributes ( a_SchemaName , a_IndexName ) ;
RETURN ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Index_ComplexFeatures ( a_SchemaName NAME , a_IndexName NAME , a_IndexNo SMALLINT ) IS 'Возвращает список характеристик индекса таблицы, принадлежащей схеме, а также список характеристик его атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Index_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_IndexName VARCHAR ( 256 ) , a_IndexNo SMALLINT ) ;
/************************************************************/
/* Функция возвращает список характеристик индекса таблицы, */
/* принадлежащей схеме, а также список характеристик его атрибутов */
/************************************************************************************************************************/
CREATE OR REPLACE FUNCTION admtf_Index_ComplexFeatures
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных */
a_IndexName VARCHAR ( 256 ) default NULL , /* Название индекса таблицы */
a_IndexNo SMALLINT default NULL /* Порядковый номер индекса таблицы*/
)
RETURNS TABLE ( rix_FeatureCategory VARCHAR ( 10 ) , rix_FeatureNumber SMALLINT ,
rix_FeatureName VARCHAR ( 256 ) , rix_OrderDirect VARCHAR ( 10 ) , rix_FeatureDescription TEXT ,
rix_UserTypeName VARCHAR ( 256 ) , rix_TypeName VARCHAR ( 256 ) , rix_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE c_WildChar CONSTANT VARCHAR ( 1 ) : = '%' ;
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT icf . rix_FeatureCategory :: VARCHAR ( 10 ) ,
icf . rix_FeatureNumber :: SMALLINT ,
icf . rix_FeatureName :: VARCHAR ( 256 ) ,
icf . rix_OrderDirect :: VARCHAR ( 10 ) ,
icf . rix_FeatureDescription :: TEXT ,
icf . rix_UserTypeName :: VARCHAR ( 256 ) ,
icf . rix_TypeName :: VARCHAR ( 256 ) ,
icf . rix_isNotNULL :: BOOLEAN
FROM admtf_Index_ComplexFeatures ( a_SchemaName :: NAME ,
a_IndexName :: NAME , a_IndexNo :: SMALLINT ) icf ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Index_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_IndexName VARCHAR ( 256 ) , a_IndexNo SMALLINT ) IS 'Возвращает список характеристик индекса таблицы, принадлежащей схеме, а также список характеристик его атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECT * FROM admtf_Index_ComplexFeatures ( 'public' :: NAME , 'xie1street' :: NAME , 7 :: SMALLINT ) ;
SELECT * FROM admtf_Index_ComplexFeatures ( 'public' :: VARCHAR ( 256 ) , 'xie1street' :: VARCHAR ( 256 ) , 7 :: SMALLINT ) ;
SELECT * FROM admtf_Index_ComplexFeatures ( 'public' :: VARCHAR ( 256 ) , 'xie9street' :: VARCHAR ( 256 ) , 7 :: SMALLINT ) ;
[crayon-66214412cedb1602855540/]
>
Создание функции admtf_Table_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь , а также в первой части статьи в разделах «Структура головной функции» и «О каких расширенных характеристиках идет речь?» .
код функции
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Table_ComplexFeatures ( a_SchemaName NAME , a_TableName NAME ) ;
/*******************************************************************/
/* Функция возвращает список характеристик таблицы, принадлежащей схеме, */
/* а также список характеристик ее атрибутов */
/********************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_ComplexFeatures
( a_SchemaName NAME default 'public' , /* название схемы базы данных */
a_TableName NAME default NULL /* Название таблицы */
)
RETURNS TABLE ( rr_FeatureCategory VARCHAR ( 12 ) , rr_FeatureNumber SMALLINT , rr_FeatureName NAME , rr_FeatureDescription TEXT , rr_UserTypeName NAME , rr_TypeName NAME , rr_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE cursor_ForeignKeys refcursor ; /* внешние ключи таблицы*/
cursor_Indexes refcursor ; /* индексы таблицы*/
c_WildChar CONSTANT VARCHAR ( 1 ) : = '%' ;
c_TableCategory CONSTANT VARCHAR ( 10 ) : = 'tbl' ; /* Категория */
/* характеристик таблицы */
c_SequenceCategory CONSTANT VARCHAR ( 10 ) : = 'seq' || c_WildChar ; /* Категория */
/* характеристик последовательности таблицы */
c_InheritanceTableCategory CONSTANT VARCHAR ( 10 ) : = 'inhtbl' ; /* Категория */
/* характеристик порожденной таблицы */
c_AttributeCategory CONSTANT VARCHAR ( 10 ) : = 'att' ; /* Категория */
/* характеристик атрибутов таблицы */
c_PrimaryKeyCategory CONSTANT VARCHAR ( 10 ) : = 'pk' ; /* Категория */
/* характеристик первичного ключа таблицы */
c_ForeignKeyCategory CONSTANT VARCHAR ( 10 ) : = 'fk' ; /* Категория */
/* характеристик внешнего ключа таблицы */
c_IndexCategory CONSTANT VARCHAR ( 10 ) : = 'idx' ; /* Категория */
/* характеристик индекса таблицы */
c_PrimaryKeyKind CONSTANT CHAR : = 'p' ;
c_ForeignKeyKind CONSTANT CHAR : = 'f' ;
v_TableOID OID ; /* ИД таблицы */
v_TableName NAME ; /* Название таблицы */
v_TableDescription TEXT ; /* Описание таблицы */
v_FeatureCategory VARCHAR ( 12 ) ; /* Категория текущего аргумета */
v_FeatureNumber SMALLINT ; /* Порядковый номер характеристики заданной категории*/
v_PrimaryKeyName NAME ; /* Название первичного ключа таблицы */
v_ForeignKeyName NAME ; /* Название внешнего ключа таблицы */
v_FKeyCount INTEGER ; /* Счетчик внешних ключей таблицы*/
v_IndexName NAME ; /* Название индекса таблицы */
v_IndexCount INTEGER ; /* Счетчик индексаов таблицы*/
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
v_FeatureCategory : = c_TableCategory ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT v_FeatureCategory , v_FeatureNumber , rs_TableName ,
rs_TableDescription , NULL :: NAME AS rr_UserTypeName ,
NULL :: NAME AS rr_TypeName , NULL :: BOOLEAN AS rr_isNotNULL
FROM admtf_Table_Features ( a_SchemaName , a_TableName ) ;
v_FeatureCategory : = c_AttributeCategory ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT v_FeatureCategory , r_AttributeNumber , r_AttributeName , r_Description ,
r_UserTypeName , r_TypeName , r_isNotNULL
FROM admtf_Table_Attributes ( a_SchemaName , a_TableName ) ;
v_FeatureCategory : = c_SequenceCategory ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT REPLACE ( c_SequenceCategory , c_WildChar ,
COALESCE ( TRIM ( TO_CHAR ( r_SequenceNumber , '09' ) ) , '' ) ) :: VARCHAR ( 12 )
AS rr_FeatureCategory ,
r_SequenceNumber , r_SequenceName , r_SequenceDescription ,
NULL :: NAME AS rr_UserTypeName , NULL :: NAME AS rr_TypeName ,
NULL :: BOOLEAN AS rr_isNotNULL
FROM admtf_Table_Sequences ( a_SchemaName , a_TableName ) ;
v_FeatureCategory : = c_PrimaryKeyCategory ;
v_FeatureNumber : = 0 ;
SELECT INTO v_PrimaryKeyName r_ConstraintName
FROM admtf_Table_Constraintes ( a_SchemaName , a_TableName )
WHERE r_ConstraintType = c_PrimaryKeyKind ;
IF FOUND THEN
RETURN QUERY SELECT rpk _ FeatureCategory , rpk_FeatureNumber ,
rpk_FeatureName , rpk_FeatureDescription , rpk_UserTypeName ,
rpk_TypeName , rpk_isNotNULL
FROM admtf_PrimaryKey_ComplexFeatures ( a_SchemaName , v_PrimaryKeyName ) ;
END IF ;
OPEN cursor_ForeignKeys FOR SELECT r_ConstraintName
FROM admtf_Table_Constraintes ( a_SchemaName , a_TableName )
WHERE r_ConstraintType = c_ForeignKeyKind
ORDER BY r_ConstraintName ;
v_FeatureCategory : = c_ForeignKeyCategory ;
v_FKeyCount : = 0 ;
FETCH FIRST FROM cursor_ForeignKeys INTO v_ForeignKeyName ;
WHILE FOUND
LOOP
v_FKeyCount : = v_FKeyCount + 1 ;
RETURN QUERY SELECT rfk _ FeatureCategory ,
CASE WHEN rfk_FeatureCategory = c_ForeignKeyCategory
THEN v_FKeyCount :: SMALLINT ELSE rfk_FeatureNumber END ,
rfk_FeatureName , rfk_FeatureDescription ,
rfk_UserTypeName , rfk_TypeName , rfk_isNotNULL
FROM admtf_ForeignKey_ComplexFeatures ( a_SchemaName ,
v_ForeignKeyName , v_FKeyCount :: SMALLINT ) ;
FETCH NEXT FROM cursor_ForeignKeys INTO v_ForeignKeyName ;
END LOOP ;
CLOSE cursor _ ForeignKeys ;
OPEN cursor_Indexes FOR SELECT r_IndexName
FROM admtf_Table_Indexes ( a_SchemaName , a_TableName )
ORDER BY r _ IndexName ;
v_FeatureCategory : = c_IndexCategory ;
v_IndexCount : = 0 ;
FETCH FIRST FROM cursor_Indexes INTO v_IndexName ;
WHILE FOUND
LOOP
v_IndexCount : = v_IndexCount + 1 ;
RETURN QUERY SELECT rix _ FeatureCategory ,
CASE WHEN rix_FeatureCategory = c_IndexCategory
THEN v_IndexCount :: SMALLINT ELSE rix_FeatureNumber END ,
rix_FeatureName , rix_FeatureDescription ,
rix_UserTypeName , rix_TypeName , rix_isNotNULL
FROM admtf_Index_ComplexFeatures ( a_SchemaName , v_IndexName ,
v_IndexCount :: SMALLINT ) ;
FETCH NEXT FROM cursor_Indexes INTO v_IndexName ;
END LOOP ;
CLOSE cursor _ Indexes ;
v_FeatureCategory : = c_InheritanceTableCategory ;
v_FeatureNumber : = 0 ;
RETURN QUERY SELECT v_FeatureCategory , RANK ( )
OVER ( PARTITION BY v_FeatureCategory ORDER BY rs _ TableName ) :: SMALLINT ,
rs_TableName , rs_TableDescription ,
NULL :: NAME AS rr_UserTypeName , NULL :: NAME AS rr_TypeName ,
NULL :: BOOLEAN rr_isNotNULL
FROM admtf_Table_InheritanceChildrens ( a_SchemaName , a_TableName ) ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Table_ComplexFeatures ( a_SchemaName NAME , a_TableName NAME ) IS 'Возвращает список характеристик таблицы, принадлежащей схеме, а также список характеристик ее атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
BEGIN TRANSACTION ;
DROP FUNCTION IF EXISTS admtf_Table_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_TableName VARCHAR ( 256 ) ) ;
/********************************************************************/
/* Функция возвращает список характеристик таблицы, принадлежащей схеме, */
/* а также список характеристик ее атрибутов */
/*******************************************************************/
CREATE OR REPLACE FUNCTION admtf_Table_ComplexFeatures
( a_SchemaName VARCHAR ( 256 ) default 'public' , /* название схемы базы данных*/
a_TableName VARCHAR ( 256 ) default NULL /* Название таблицы */
)
RETURNS TABLE ( rr_FeatureCategory VARCHAR ( 12 ) , rr_FeatureNumber SMALLINT , rr_FeatureName VARCHAR ( 256 ) , rr_FeatureDescription TEXT , rr_UserTypeName VARCHAR ( 256 ) , rr_TypeName VARCHAR ( 256 ) , rr_isNotNULL BOOLEAN ) AS
$ BODY $
DECLARE c_TableCategory CONSTANT VARCHAR ( 10 ) : = 'tbl' ; /* Категория */
/* характеристик таблицы */
-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
BEGIN
RETURN QUERY SELECT tcf . rr_FeatureCategory :: VARCHAR ( 12 ) ,
tcf . rr_FeatureNumber :: SMALLINT ,
tcf . rr_FeatureName :: VARCHAR ( 256 ) ,
tcf . rr_FeatureDescription :: TEXT ,
tcf . rr_UserTypeName :: VARCHAR ( 256 ) ,
tcf . rr_TypeName :: VARCHAR ( 256 ) ,
tcf . rr_isNotNULL :: BOOLEAN
FROM admtf_Table_ComplexFeatures ( a_SchemaName :: NAME , a_TableName :: NAME ) tcf ;
END
$ BODY $
LANGUAGE plpgsql ;
COMMENT ON FUNCTION admtf_Table_ComplexFeatures ( a_SchemaName VARCHAR ( 256 ) , a_TableName VARCHAR ( 256 ) ) IS 'Возвращает список характеристик таблицы, принадлежащей схеме, а также список характеристик ее атрибутов' ;
-- ROLLBACK TRANSACTION ;
COMMIT TRANSACTION ;
SELECT * FROM admtf_Table_ComplexFeatures ( 'public' :: NAME , 'Street' :: NAME ) ;
[crayon-66214412cedba812244109/]
Смотрите также
Функции для документирования баз данных PostgreSQL. Начало ;
Места постоянного размещения статьи:
Это окончание статьи, которая описывает пользовательские функции для работы с системными каталогами: pg_class, pg_attribute, pg_constraint и т.д.
Описанные в статье функции были созданы в процессе подготовки заявки на государственную регистрацию базы данных. Порядок подачи заявки и требования ее оформлению изложены в документе «Правила оформления заявки на государственную регистрацию программы для электронных вычислительных машин или базы данных», утвержденном приказом Минэкономразвития России от 5 апреля 2016 года N 211 (далее Правила).