Это четвертая и последняя часть статьи, которая описывает пользовательские функции для работы с системными каталогами: pg_class, pg_attribute, pg_constraints и т.д. Первая, вторая и третья части статьи опубликованы ранее.
Предчувствую, что должен заранее извиниться перед теми из читателей, кого интересовали только устройство системных каталогов PostgrSQL, а также приемы извлечения данных из них. Функции, которые описываются в этой части статьи, не обращаются к еще не рассмотренным системным каталогам, да и приемы извлечения данных ничем не отличаются от тех, что изложены в предыдущих частях. Такие читатели могут завершить просмотр статьи прямо здесь.
Всем тем, кто решил продолжить чтение, сообщаю, что в этой части статьи рассматриваются функции, возвращающие расширенные характеристики первичных и внешних ключей, а также индексов таблиц. И конечно приведено краткое описание и код функции admtf_Table_ComplexFeatures, которая была заявлена как цель публикации в первой части статьи.
В первой половине статьи изложены комментарии к реализации функций. Во второй- исходные тексты функций. Тем из читателей, кого интересуют только исходные тексты, предлагаем сразу перейти к Приложению.
Структура функции, возвращающей список характеристик первичного ключа таблицы
Рис. 4. Функции, которые вызывает admtf_PrimaryKey_ComplexFeatures.
Таблица 20. Назначение функций.
Текстовая версия таблицы на рисунке
| № | Название | Назначение |
|---|---|---|
| 1 | admtf_PrimaryKey_Features | Функция возвращает характеристики первичного ключа (PRIMARY KEY) таблицы |
| 2 | admtf_PrimaryKey_Attributes | Функция возвращает список атрибутов первичного ключа (PRIMARY KEY) и их характеристик. |
| 3 | admtf_PrimaryKey_ComplexFeatures | Функция возвращает характеристики первичного ключа (PRIMARY KEY) таблицы, а также список составляющих ключ атрибутов. |
Функция admtf_PrimaryKey_ComplexFeatures – комплексный список характеристик первичного ключа таблицы
Таблица 21. Результат выполнения функции admtf_PrimaryKey_ComplexFeatures ('public',' xpkstreet').
Текстовая версия таблицы на рисунке
| Категория | № | Название | Комментарий | тип | Базовый тип | ? not NULL |
|---|---|---|---|---|---|---|
| pkl | 0 | xpkstreet | Первичный ключ таблицы street | |||
| pkatt | 1 | wcrccode | Код страны | wcrccode | smallint | t |
| pkatt | 2 | localityid | ИД населенного пункта | localityid | integer | t |
| pkatt | 3 | streetid | ИД улицы населенного пункта | streetid | smallint | t |
В качестве обязательных параметров функция принимает название первичного ключа (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);
Вторая функция (admtf_PrimaryKey_Attributes) возвращает характеристики атрибутов, составляющих первичный ключ.
исходный код оператора на рисунке
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;
Здесь следует обратить внимание на порядок вывода записей об атрибутах первичного ключа. Они выводятся в порядке описания в первичном ключе (con.No), а не в порядке их описания в таблице (attr.attnum).
Особенности соединения записей, используемых системных каталогов, подробно рассмотрены в разделе «Функция admtf_Table_Constraintes список ограничений таблицы базы данных и их характеристик».
Структура функции, возвращающей список характеристик внешнего ключа таблицы
Рис. 5. Функции, которые вызывает admtf_ForeignKey_ComplexFeatures.
Таблица 22. Назначение функций.
Текстовая версия таблицы на рисунке
| № | Название | Назначение |
|---|---|---|
| 1 | admtf_ ForeignKey _Features | Функция возвращает характеристики внешнего ключа (FOREIGN KEY) таблицы. |
| 2 | admtf_ ForeignKey_Attributes | Функция возвращает список атрибутов внешнего ключа таблицы и их характеристик. |
| 3 | admtf_ForeignKey_ReferenceTableFeatures | Функция возвращает список характеристик таблицы базы данных, на которую ссылается внешний ключ. |
| 4 | admtf_ForeignKey_ReferenceTableAttributes | Функция возвращает список атрибутов таблицы базы данных, на которую ссылается внешний ключ, и их характеристик. |
| 5 | admtf_ForeignKey_ReferenceTableComplexFeatures | Функция возвращает полный (расширенный) список характеристик таблицы базы данных, на которую ссылается внешний ключ. |
| 6 | admtf_ForeignKey_ComplexFeatures | Функция возвращает характеристики внешнего ключа (FOREIGN KEY) таблицы, а также список атрибутов, включенных в индекс. |
Функция admtf_ForeignKey_ComplexFeatures – комплексный список характеристик внешнего ключа таблицы
Функции admtf_ForeignKey_ComplexFeatures возвращает список следующих характеристик внешнего ключа таблицы.
<strongТаблица 23. Результат выполнения функции admtf_PrimaryKey_ComplexFeatures ('public','fk_street_locality',3).
Текстовая версия таблицы на рисунке
| Категория | № | Название | Комментарий | тип | Базовый тип | ? not NULL |
|---|---|---|---|---|---|---|
| fk03 | 3 | fk_street_locality | Внешний ключ таблицы street | |||
| fk03att | 1 | wcrccode | Код страны | wcrccode | smallint | t |
| fk03att | 2 | localityid | ИД населенного пункта | localityid | integer | t |
| fk03rtbl | 0 | locality | Список населенных пунктов | |||
| fk03ratt | 1 | wcrccode | Код страны | wcrccode | smallint | t |
| fk03ratt | 2 | localityid | ИД населенного пункта | localityid | integer | t |
В качестве параметров функция принимает название внешнего ключа (a_ ForeignKey) и название схемы, в пределах которой создан внешний ключ (a_SchemaName).
Исходный код можно посмотреть и скачать здесь.
У функции есть еще одни необязательный параметр — порядковый номер индекса таблицы (a_ForeignKeyNo). Этот параметр нужен для того, чтобы в значения категории добавлять порядковый номер внешнего ключа таблицы. В частности, в приведенном примере функция выполнялась со значением этого параметра равным 3. Поэтому запись с характеристиками внешнего ключа помечена значением «fk03», записи с характеристиками атрибутов — «fk03att», запись о внешней таблице — «fk03rtbl», а записи об атрибутах внешней таблицы — «fk03ratt». Если при вызове функции опустить этот параметр, то значения категорий в записях были бы «fk», «fkatt», «fkrtbl» и «fkratt» соответственно. По этой же причине значение категории формируется внутри функции admtf_ForeignKey_ComplexFeatures, а не в коде вызывающей ее функции.
Подробнее смотри в разделе «О каких расширенных характеристиках идет речь?»
.
Код функция представляет собой последовательный вызов трех табличных функций.
Первая функция (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);
Вторая функция (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;
Третья функция (admtf_ForeignKey_ReferenceTableComplexFeatures) возвращает характеристики таблицы, на которую ссылается внешний ключ. Для решения своей задачи она последовательно вызывает две дополнительные функции.
Функция admtf_ForeignKey_ReferenceTableComplexFeatures – комплексный список характеристик таблицы, на которую ссылается внешний ключ
В качестве параметров функция принимает название внешнего ключа (a_ForeignKey) и название схемы, в пределах которой создан внешний ключ (a_SchemaName).
У функции есть еще одни необязательный параметр — порядковый номер индекса таблицы (a_ForeignKeyNo). Этот параметр нужен для того, чтобы в значениях категории замещать порядковым номером символ '%' в «fk%rtbl» и «fk%ratt» соответственно.
Функция последовательно вызывает две дополнительные функции.
Первая admtf_ForeignKey_ReferenceTableFeatures возвращает непосредственно характеристики таблицы, на которую ссылается внешний ключ, и представляет собой упрощенную версию функции admtf_Table_Features.
Вторая admtf_ForeignKey_ReferenceTableAttributes – характеристики атрибутов внешней таблицы, соответствующие атрибутам внешнего ключа. Она почти полностью повторяет код функции admtf_ForeignKey_Attributes. Только в некоторых местах вместо идентификатора con.conrelid используется con.confrelid, и вместо массива con.conkey используется con.confkey.
исходный код оператора на рисунке
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;
Структура функции, возвращающей список характеристик индекса таблицы
Рис. 6. Функции, которые вызывает admtf_Index_ComplexFeatures.
Таблица 24. Назначение функций.
Текстовая версия таблицы на рисунке
| № | Название | Назначение |
|---|---|---|
| 1 | admtf_Index_Features | Функция возвращает характеристики индекса таблицы. |
| 2 | admtf_Index_Attributes | Функция возвращает список атрибутов таблицы, включенных в индекс, и их характеристик. |
| 3 | admtf_Index_ComplexFeatures | Функция возвращает характеристики индекса таблицы, а также список атрибутов, включенных в индекс. |
Функция admtf_Index_ComplexFeatures – комплексный список характеристик индекса таблицы
Функции admtf_Index_ComplexFeatures возвращает список следующих характеристик индекса таблицы.
Таблица 25. Результат выполнения функции admtf_Index_ComplexFeatures ('public','xie9street',7).
Текстовая версия таблицы на рисунке
| Категория | № | Название | Порядок | Комментарий | тип | Базовый тип | ? not NULL |
|---|---|---|---|---|---|---|---|
| idx07 | 7 | xie9street | Индекс по названию улицы населенного пункта в убывающем порядке | ||||
| idx07att | 1 | wcrccode | ASC | Код страны | wcrccode | smallint | t |
| idx07att | 2 | localityid | ASC | ИД населенного пункта | localityid | integer | t |
| idx07att | 3 | streetname | DESC | Наименование улицы населенного пункта | VARCHAR(150) | t |
В качестве параметров функция принимает название индекса (a_ Index) и название схемы, в пределах которой создан индекс (a_SchemaName).
Исходный код можно посмотреть и скачать здесь.
У функции есть еще одни необязательный параметр — порядковый номер индекса таблицы (a_IndexNo). Этот параметр нужен для того, чтобы в значения категории добавлять порядковый номер индекса таблицы. В частности, в приведенном примере функция выполнялась со значением этого параметра равным 7. Поэтому запись с характеристиками индекса помечена значением «idx07», а записи с характеристиками атрибутов — «idx07att». Если при вызове функции опустить этот параметр, то значения категорий в записях были бы «idx» и «idxatt» соответственно.
Подробнее смотри в разделе «О каких расширенных характеристиках идет речь?». По этой же причине значение категории формируется внутри функции admtf_Index_ComplexFeatures, а не в коде вызывающей ее функции.
Код функции представляет собой последовательный вызов двух табличных функций.
Первая функция (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);
Вторая функция (admtf_ Index_Attributes) возвращает характеристики атрибутов, включенных в состав индекса. Обратите внимание, что порядок записей об атрибутах определяется порядком их описания в индексе (inx.No), а не порядком физического следования в таблице (attr.attnum).
исходный код оператора на рисунке
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;
Особенности соединения записей, используемых системных каталогов, подробно рассмотрены в разделе «Функция admtf_Table_Indexes список индексов таблицы базы данных и их характеристик.
Создание функции admtf_Table_ComplexFeatures
Функция admtf_Table_ComplexFeatures возвращает комплексный список характеристик таблицы базы данных, который включает в себя характеристики, возвращаемые описанными в статье функциями. В качестве параметров функция принимает название исходной таблицы (a_TableName) и название схемы, в пределах которой создана таблица (a_SchemaName).
Исходный код можно посмотреть и скачать здесь.
Таблица 26. Результат выполнения функции admtf_Table_ComplexFeatures('public', 'street').
Текстовая версия таблицы на рисунке
| Категория | № | Название | Комментарий | тип | Базовый тип | ? not NULL |
|---|---|---|---|---|---|---|
| tbl | 0 | street | Список улиц в населенных пунктах | |||
| att | 1 | wcrccode | Код страны | wcrccode | smallint | t |
| att | 2 | localityid | ИД населенного пункта | localityid | integer | t |
| att | 3 | streetid | ИД улицы населенного пункта | streetid | smallint | t |
| att | 4 | streettypeacrm | Акроним типа улицы | streettypeacrm | character(8) | f |
| att | 5 | streetname | Наименование улицы | streettypeacrm | varchar(150) | t |
| pk | 0 | xpkstreet | Первичный ключ таблицы street | |||
| pkatt | 1 | wcrccode | Код страны | wcrccode | smallint | t |
| fk01 | 1 | fk_street_locality | Внешний ключ таблицы | |||
| fk02 | 2 | fk_street_streettype | Внешний ключ таблицы | |||
| idx01 | 1 | xie1street | Индекс по типу и названию улицы населенного пункта | |||
| idx02 | 2 | xie2street | Индекс по названию улицы населенного пункта | |||
| idx03 | 3 | xie3street | Индекс по названиям улиц всех населенных пунктов | |||
| idx04 | 4 | xpkstreet | Индекс уникальный (первичный ключ) таблицы street |
В процессе своего выполнения функция последовательно вызывает 9 дополнительных функций, список которых приведен в разделе «Структура головной функции».
Совместное выполнение головной и дополнительных функций приводит в результате к созданию таблицы с расширенными характеристиками таблицы.
Где использовались функции?
Описанные в статье функции были созданы в процессе подготовки заявки на государственную регистрацию базы данных. Порядок подачи заявки и требования ее оформлению изложены в документе «Правила оформления заявки на государственную регистрацию программы для электронных вычислительных машин или базы данных», утвержденном приказом Минэкономразвития России от 5 апреля 2016 года N 211 (далее Правила).
Обязательной частью заявки является документ «Материалы, идентифицирующие базу данных». Правила трактуют содержание этого документа следующим образом.
«Материалы, идентифицирующие базу данных, должны отражать объективную форму представления совокупности содержащихся в ней самостоятельных материалов в виде примеров реального наполнения и принципы их систематизации (структуру базы данных), позволяющие осуществить нахождение и обработку этих материалов с помощью ЭВМ.»
Другими словами, документ должен содержать описание структуры базы данных и примеры ее реального наполнения.
Как видно из рисунка, при подготовке заявки на регистрацию базы данных использовались не только функции, описанные в этой статье. Дополнительно были созданы 3- 4 функции для преобразования описаний таблиц базы данных в формат PlantUML.Точнее, эти функции создают код в формате плагина для системы управления проектами TRAC, поэтому если вам захочется проверить созданный этими функциями код, то не забудьте убрать две строчки сверху перед @startuml и все строчки снизу после @enduml.
Созданный при помощи функций код PlantUML скрипта
{{{
#!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. Часть первая;
Функции для документирования баз данных PostgreSQL. Часть вторая;
Функции для документирования баз данных PostgreSQL. Часть третья.
ПРИЛОЖЕНИЕ 1. Скрипты
Создание функции admtf_PrimaryKey_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
Создание функции admtf_PrimaryKey_Features
код функции
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));
Создание функции admtf_PrimaryKey_Attributes
код функции
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));
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));
Создание функции admtf_ForeignKey_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
Создание функции admtf_ForeignKey_Features
код функции
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);
Создание функции admtf_ForeignKey_Attributes
код функции
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));
Создание функции admtf_ForeignKey_ReferenceTableComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
Создание функции admtf_ForeignKey_ReferenceTableFeatures
код функции
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);
Создание функции admtf_ForeignKey_ReferenceTableAttributes
код функции
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);
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);
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);
Создание функции admtf_Index_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь.
код функции
Создание функции admtf_Index_Features
код функции
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));
Создание функции admtf_Index_Attributes
код функции
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));
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);
Создание функции admtf_Table_ComplexFeatures
Комментарии к исходному коду функции можно посмотреть здесь, а также в первой части статьи в разделах «Структура головной функции» и «О каких расширенных характеристиках идет речь?».
код функции
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);
Смотрите также
Функции для документирования баз данных PostgreSQL. Часть первая;
Функции для документирования баз данных PostgreSQL. Часть вторая;
Функции для документирования баз данных PostgreSQL. Часть третья.
Автор: Сергей Гладков
