Functions for documenting PostgreSQL databases. Ending
This is the fourth and last part of the article, which describes user functions for working with system directories: pg_class, pg_attribute, pg_constraints, etc. The first , second and third parts of the article are published earlier.
Fig. 4. Functions that admtf_PrimaryKey_ComplexFeatures calls.
Table 20. Assignment of functions.
Table 21. Result of execution of the function admtf_PrimaryKey_ComplexFeatures ('public', 'xpkstreet').
Features of the connection records used by the system catalogs are discussed in detail in the section “The function admtf_Table_Constraintes list of database table constraints and their characteristics” .
Fig. 5. Functions that admtf_ForeignKey_ComplexFeatures calls.
Table 22. Assignment of functions.
<strongTable 23. The result of the function admtf_ForeignKey_ComplexFeatures ('public', 'fk_street_locality', 3).
Features of the connection records used by the system catalogs are discussed in detail in the section “The function admtf_Table_Constraintes - a list of limitations of the database table and their characteristics” .
Fig. 6. Functions that admtf_Index_ComplexFeatures calls.
Table 24. Assignment of functions.
Table 25. Result of execution of the function admtf_Index_ComplexFeatures ('public', 'xie9street', 7).
Table 26. Result of execution of the function admtf_Table_ComplexFeatures ('public', 'street').
Comments on the source code of the function can be found here.
Comments on the source code of the function can be found here.
I anticipate that I must apologize in advance to those readers who are interested only in the device configuration of the PostgrSQL system directories, as well as in the techniques for extracting data from them. The functions described in this part of the article do not apply to the system directories that have not yet been reviewed, and the methods for extracting data are no different from those described in the previous parts. Such readers can finish viewing the article right here.
To all those who decide to continue reading, I inform you that this part of the article discusses functions that return the extended characteristics of primary and foreign keys , as well as indexes of tables. And of course, there is a brief description and code for the function admtf_Table_ComplexFeatures , which was stated as the purpose of the publication in the first part of the article .
The first half of the article contains comments on the implementation of functions. The second is the source code of the functions. For those readers who are interested only in the source code, we suggest that you go straight to the Appendix .
The structure of the function that returns the list of characteristics of the primary key of the table
Fig. 4. Functions that admtf_PrimaryKey_ComplexFeatures calls.
Table 20. Assignment of functions.
Text version of the table in the figure
| No | Title | Purpose |
|---|---|---|
| one | admtf_PrimaryKey_Features | The function returns the characteristics of the primary key (PRIMARY KEY) of the table. |
| 2 | admtf_PrimaryKey_Attributes | The function returns a list of primary key attributes (PRIMARY KEY) and their characteristics. |
| 3 | admtf_PrimaryKey_ComplexFeatures | The function returns the characteristics of the primary key (PRIMARY KEY) of the table, as well as a list of the key attributes that make up the key. |
The function admtf_PrimaryKey_ComplexFeatures - a comprehensive list of the characteristics of the primary key of the table
Table 21. Result of execution of the function admtf_PrimaryKey_ComplexFeatures ('public', 'xpkstreet').
Text version of the table in the figure
| Category | No | Title | Comment | type of | Base type | ? not NULL |
|---|---|---|---|---|---|---|
| pkl | 0 | xpkstreet | Primary key table street | |||
| pkatt | 1 | wcrccode | Код страны | wcrccode | smallint | t |
| pkatt | 2 | localityid | ИД населенного пункта | localityid | integer | t |
| pkatt | 3 | streetid | ИД улицы населенного пункта | streetid | smallint | t |
The function takes the name of the primary key ( a_PrimaryKeyName ) and the name of the schema within which the table is created ( a_SchemaName ) as required parameters . The function code is a sequential invocation of two table functions.
Source code can be viewed and downloaded here.
The first function ( admtf_PrimaryKey_Features ) prepares and executes a SELECT that returns the characteristics of the primary key.
source code operator in the figure
SELECT con.conname,
COALESCE(dsc.description,'Первичный ключ таблицы '|| tbl.relname)
FROM pg_constraint con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_class tbl ON con.conrelid=tbl.oid
LEFTOUTERJOIN pg_Description dsc ON con.oid=dsc.objoid
AND dsc.objsubid=0WHERE con.contype ='p'AND nspc.nspname=LOWER(a_SchemaName)
AND con.conname =LOWER(a_PrimaryKeyName);
The second function ( admtf_PrimaryKey_Attributes ) returns the characteristics of the attributes that make up the primary key.
source code operator in the figure
SELECT (rank() OVER (PARTITIONBY con.conrelid ORDERBY con.No)) ::SMALLINT,
attr.attnum,attr.attname::NAME,
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN 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) asNoFROM pg_constraint c) con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_attribute attrON attr.attrelid=con.conrelid
AND attr.attnum=con.conkey[con.No]
LEFTOUTERJOIN pg_type typ ON attr.atttypid=typ.oid
LEFTOUTERJOIN pg_type btyp ON typ.typbasetype=btyp.oid
WHERE con.contype ='p'AND nspc.nspname=LOWER(a_SchemaName)
AND con.conname =LOWER(a_PrimaryKeyName)
ORDERBY con.No;
Here you should pay attention to the order of displaying records about the attributes of the primary key. They are displayed in the order of description in the primary key ( con.No ), and not in the order of their description in the table ( attr.attnum ).
Features of the connection records used by the system catalogs are discussed in detail in the section “The function admtf_Table_Constraintes list of database table constraints and their characteristics” .
The structure of the function that returns a list of characteristics of the foreign key table
Fig. 5. Functions that admtf_ForeignKey_ComplexFeatures calls.
Table 22. Assignment of functions.
Text version of the table in the figure
| № | Название | Назначение |
|---|---|---|
| 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) таблицы, а также список атрибутов, включенных в индекс. |
The function admtf_ForeignKey_ComplexFeatures is a comprehensive list of foreign key characteristics of a table.
The admtf_ForeignKey_ComplexFeatures function returns a list of the following foreign key characteristics of a table.
<strongTable 23. The result of the function admtf_ForeignKey_ComplexFeatures ('public', 'fk_street_locality', 3).
Text version of the table in the figure
| Категория | № | Название | Комментарий | тип | Базовый тип | ? 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 |
As parameters, the function takes the name of the foreign key ( a_ ForeignKey ) and the name of the scheme within which the foreign key is created ( a_SchemaName ).
Source code can be viewed and downloaded here.
The function has one more optional parameter — the index number of the table ( a_ForeignKeyNo ). This parameter is needed to add the sequence number of the foreign key of the table to the category values. In particular, in the above example, the function was executed with the value of this parameter equal to 3 . Therefore, an entry with foreign key characteristics is marked with the value “ fk03 ”, entries with attribute characteristics are “ fk03att ”, an entry about the external table is “ fk03rtbl ”, and an entry about the attributes of the external table is “ fk03ratt ”. If this parameter were omitted when calling the function, the category values in the entries would be “ fk ”, “ fkatt ”, “fkrtbl and fkratt , respectively. For the same reason, the category value is formed inside the function admtf_ForeignKey_ComplexFeatures , and not in the code of the function calling it.
For details, see “What are the advanced features in question?”
.Function code is a sequential call of three table functions.
The first function ( admtf_ForeignKey_Features ) prepares and executes a SELECT that returns the characteristics of the foreign key.
source code operator in the figure
SELECT con.conname, COALESCE(dsc.description,'Внешний ключ таблицы '|| tbl.relname)
FROM pg_constraint con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_class tbl ON con.conrelid=tbl.oid
LEFTOUTERJOIN pg_Description dsc ON con.oid=dsc.objoid AND dsc.objsubid=0WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype='f'AND con.conname =LOWER(a_ForeignKeyName);
The second function ( admtf_ForeignKey_Attributes ) returns the characteristics of foreign key attributes.
Here you should pay attention to the order of displaying records about foreign key attributes. They are displayed in the order of description in the foreign key ( con.No ), and not in the order of their description in the table ( attr.attnum ).
Features of the connection records used by the system catalogs are discussed in detail in the section “The function admtf_Table_Constraintes - a list of limitations of the database table and their characteristics” .
source code operator in the figure
SELECT (rank() OVER (PARTITIONBY con.conrelid ORDERBY con.No))::SMALLINTAS r_ForeingKeyNo,
attr.attnum AS r_AttributeNumber,attr.attname::NAMEAS r_AttributeName,
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN typ.typname::NAMEELSE''ENDAS r_UserTypeName,
FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAMEAS 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) asNoFROM pg_constraint c) con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_attribute attrON attr.attrelid=con.conrelid AND attr.attnum=con.conkey[con.No]
INNERJOIN pg_type typ ON attr.atttypid=typ.oid
LEFTOUTERJOIN pg_type btyp ON typ.typbasetype=btyp.oid
LEFTOUTERJOIN 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)
ORDERBY con.No;
The third function ( admtf_ForeignKey_ReferenceTableComplexFeatures ) returns the characteristics of the table referenced by the foreign key. To solve its problem, it sequentially calls two additional functions.
The function admtf_ForeignKey_ReferenceTableComplexFeatures is a comprehensive list of table characteristics referenced by a foreign key.
As parameters, the function takes the name of the foreign key ( a_ForeignKey ) and the name of the schema within which the foreign key ( a_SchemaName ) is created.
The function has one more optional parameter — the index number of the table ( a_ForeignKeyNo ). This parameter is needed in order to replace with the sequence number the character '%' in the “ fk% rtbl ” and “ fk% ratt ”, respectively.
The function sequentially calls two additional functions.
The first admtf_ForeignKey_ReferenceTableFeatures returns directly the characteristics of the table referenced by the foreign key, and is a simplified version of the function admtf_Table_Features .
The second admtf_ForeignKey_ReferenceTableAttributes is the characteristics of the attributes of the external table that correspond to the attributes of the foreign key. It almost repeats the function code admtf_ForeignKey_Attributes . Only in some places instead of an identifier con.conrelid used con.confrelid , and instead of an array con.conkey used con.confkey .
source code operator in the figure
SELECT (rank() OVER (PARTITIONBY con.confrelid ORDERBY con.No))::SMALLINT,
attr.attnum AS r_AttributeNumber,attr.attname::NAMEAS r_AttributeName,
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN typ.typname::NAMEELSE''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) asNoFROM pg_constraint c) con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_attribute attrON attr.attrelid=con.confrelid
AND attr.attnum=con.confkey[con.No]
INNERJOIN pg_type typ ON attr.atttypid=typ.oid
LEFTOUTERJOIN pg_type btyp ON typ.typbasetype=btyp.oid
LEFTOUTERJOIN 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)
ORDERBY con.No;
The structure of the function that returns a list of table index characteristics
Fig. 6. Functions that admtf_Index_ComplexFeatures calls.
Table 24. Assignment of functions.
Text version of the table in the figure
| № | Название | Назначение |
|---|---|---|
| 1 | admtf_Index_Features | Функция возвращает характеристики индекса таблицы. |
| 2 | admtf_Index_Attributes | Функция возвращает список атрибутов таблицы, включенных в индекс, и их характеристик. |
| 3 | admtf_Index_ComplexFeatures | Функция возвращает характеристики индекса таблицы, а также список атрибутов, включенных в индекс. |
The function admtf_Index_ComplexFeatures - a comprehensive list of table index characteristics
The admtf_Index_ComplexFeatures function returns a list of the following table index characteristics.
Table 25. Result of execution of the function admtf_Index_ComplexFeatures ('public', 'xie9street', 7).
Text version of the table in the figure
| Категория | № | Название | Порядок | Комментарий | тип | Базовый тип | ? 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 |
As parameters, the function takes the name of the index ( a_ Index ) and the name of the scheme within which the index is created ( a_SchemaName ).
Source code can be viewed and downloaded here.
The function has one more optional parameter - the index index index number ( a_IndexNo ). This parameter is needed in order to add the index number of the table to the category values. In particular, in the above example, the function was executed with the value of this parameter equal to 7 . Therefore, the record with the characteristics of the index is marked with the value “ idx07 ”, and the record with the characteristics of the attributes - “ idx07att ”. If this parameter were omitted when calling the function, the category values in the entries would be “ idx ” and “ idxatt ”, respectively.
For details, see the section “What advanced features are we talking about?” . For the same reason, the category value is formed inside the function admtf_Index_ComplexFeatures , and not in the code of the function calling it.
The function code is a sequential invocation of two table functions.
The first function ( admtf_Index_Features ) prepares and executes a SELECT that returns the characteristics of the index.
source code operator in the figure
SELECT inxcls.relname,
CASEWHENCOALESCE(TRIM(dsc.description),'')=''THEN'Индекс' || CASEWHEN inx.indisunique THEN' уникальный'
|| CASEWHEN inx.indisprimary THEN'(первичный ключ)'ELSE''ENDELSE''END
||CASEWHEN inxam.amname='gist'THEN' пространственный'ELSE''END ||' таблицы '
||tbl.relname
ELSE dsc.description ENDFROM pg_index inx
INNERJOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNERJOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid
LEFTOUTERJOIN pg_Description dsc ON inxcls.oid=dsc.objoid
AND dsc.objsubid=0LEFTOUTERJOIN pg_am inxam ON inxcls.relam=inxam.oid
LEFTOUTERJOIN pg_class tbl ON inx.indrelid=tbl.oid
WHERE inxcls.relkind='i'AND nsp.nspname =LOWER(a_SchemaName)
AND inxcls.relname=LOWER(a_IndexName);
The second function ( admtf_ Index_Attributes ) returns the characteristics of the attributes included in the index. Please note that the order of the attribute records is determined by the order of their description in the index ( inx.No ), and not by the order of physical following in the table ( attr.attnum ).
source code operator in the figure
SELECT (inx.No+1)::SMALLINT,attr.attnum::SMALLINT, attr.attname::NAME,
CASEWHENNOT inxam.amcanorder THENNULLELSECASEWHEN inx.indoption[inx.No] & 1=1THEN'DESC'ELSE'ASC'ENDEND::VARCHAR(10),
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN typ.typname::NAMEELSE''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) asNoFROM pg_index i) inx
INNERJOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNERJOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid
LEFTOUTERJOIN pg_am inxam ON inxcls.relam=inxam.oid
LEFTOUTERJOIN pg_class tbl ON inx.indrelid=tbl.oid
INNERJOIN pg_attribute attrON attr.attrelid=tbl.oid AND attr.attnum=inx.indkey[inx.No]
LEFTOUTERJOIN pg_type typ ON attr.atttypid=typ.oid
LEFTOUTERJOIN pg_type btyp ON typ.typbasetype=btyp.oid
LEFTOUTERJOIN 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)
ORDERBY nsp.nspname,inxcls.relname,inx.No;
Features of the connection records used by the system catalogs are discussed in detail in the section “The function admtf_Table_Indexes list of database table indexes and their characteristics.
Creating the function admtf_Table_ComplexFeatures
The admtf_Table_ComplexFeatures function returns a comprehensive list of database table characteristics, which includes the characteristics returned by the functions described in the article. As parameters, the function takes the name of the source table ( a_TableName ) and the name of the schema within which the table is created ( a_SchemaName ).
Source code can be viewed and downloaded here.
Table 26. Result of execution of the function admtf_Table_ComplexFeatures ('public', 'street').
Text version of the table in the figure
| Категория | № | Название | Комментарий | тип | Базовый тип | ? 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 |
In the course of its execution, the function sequentially calls 9 additional functions, a list of which is given in the section “Structure of the head function” .
The joint implementation of the head and additional functions results in the creation of a table with advanced characteristics of the table .
Where are the functions used?
The functions described in the article were created in the process of preparing an application for state registration of the database. The application procedure and the requirements for its execution are set forth in the document “Rules for filing an application for state registration of a program for electronic computers or a database”, approved by Order No. 211 of the Ministry of Economic Development of Russia dated April 5, 2016 (hereinafter referred to as the Rules).
The mandatory part of the application is the document "Materials identifying the database." The rules interpret the contents of this document as follows.
“Materials identifying the database should reflect the objective form of representing the totality of the independent materials contained in it in the form of examples of real content and the principles of their systematization (database structure), which allow finding and processing these materials using a computer.”
In other words, the document should contain a description of the database structure and examples of its actual content.
As can be seen from the figure, when preparing the application for database registration, not only the functions described in this article were used. Additionally, 3-4 functions were created to convert the descriptions of database tables into the PlantUML format. More precisely, these functions create code in the format of a plug- in for the TRAC project management system , so if you want to check the code created by these functions, do not forget to remove two lines from the top before @startuml and all lines below after @enduml .
PlantUML script code created using functions
{{{
#!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
}}}
----PS Why aren't additional functions listed here for converting the description of database tables to the PlantUML plugin format for the TRAC project management system? First, they did not fit into the stated topic. Secondly, it seems that I tired the readers with the texts of functions. But if someone is interested in these functions, then write to me and I will send their texts.
See also
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Two ;
Functions for documenting PostgreSQL databases. Part Three .
APPENDIX 1. Scripts
Creating the function admtf_PrimaryKey_ComplexFeatures
Comments on the source code of the function can be found here.
function code
Создание функции admtf_PrimaryKey_Features
код функции
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_PrimaryKey_Features (a_SchemaName NAME,a_PrimaryKeyName NAME);
/******************************************************************************//* Функция возвращает список характеристик первичного ключа таблицы, принадлежащего *//* схеме *//******************************************************************************/CREATEORREPLACEFUNCTION admtf_PrimaryKey_Features
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_PrimaryKeyName NAMEdefaultNULL/* Название первичного ключа таблицы */
)
RETURNSTABLE (rs_PrimaryKeyName NAME,rs_PrimaryKeyDescription TEXT) AS
$BODY$
DECLARE c_PrimaryKeyKind CONSTANTCHAR:='p';
v_PrimaryKeyOID OID; /* ИД первичного ключа таблицы */
v_PrimaryKeyName NAME; /* Название первичного ключа таблицы */
v_PrimaryKeyDescription TEXT; /* Описание первичного ключа таблицы */
v_MasterTableName NAME; /* Название таблицы, которой принадлежит первичный ключ */--******************************************************************* BEGINSELECTINTO rs_PrimaryKeyName,rs_PrimaryKeyDescription
con.conname,COALESCE(dsc.description,'Первичный ключ таблицы '|| tbl.relname)
FROM pg_constraint con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_class tbl ON con.conrelid=tbl.oid
LEFTOUTERJOIN pg_Description dsc ON con.oid=dsc.objoid
AND dsc.objsubid=0WHERE 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;
COMMENTONFUNCTION admtf_PrimaryKey_Features(a_SchemaName NAME,a_PrimaryKeyName NAME) IS'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_PrimaryKey_Features (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
/******************************************************************************//* Функция возвращает список характеристик первичного ключа таблицы, принадлежащего *//* схеме *//******************************************************************************/CREATEORREPLACEFUNCTION admtf_PrimaryKey_Features
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_PrimaryKeyName VARCHAR(256) defaultNULL/* Название первичного ключа таблицы */
)
RETURNSTABLE (rs_PrimaryKeyName VARCHAR(256),rs_PrimaryKeyDescription TEXT) AS
$BODY$
DECLARE c_PrimaryKeyKind CONSTANTCHAR:='p';
--****************************************************************** BEGINRETURNQUERYSELECT pkf.rs_PrimaryKeyName::VARCHAR(256),
pkf.rs_PrimaryKeyDescription::TEXTFROM admtf_PrimaryKey_Features(a_SchemaName::NAME,a_PrimaryKeyName::NAME) pkf;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_PrimaryKey_Features(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECt * FROM admtf_PrimaryKey_Features('public'::NAME,'xpkstreet'::NAME);
SELECt * FROM admtf_PrimaryKey_Features('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256));
Создание функции admtf_PrimaryKey_Attributes
код функции
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_PrimaryKey_Attributes (a_SchemaName NAME,a_PrimaryKeyName NAME);
/********************************************************************//* Функция возвращает список атрибутов первичного ключа и их характеристик *//********************************************************************/CREATEORREPLACEFUNCTION admtf_PrimaryKey_Attributes
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_PrimaryKeyName NAMEdefaultNULL/* Название первичного ключа таблицы */
)
RETURNSTABLE (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 CONSTANTCHAR:='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; /* Порядковый номер атрибута первичного ключа*/--********************************************************************** BEGINRETURNQUERYSELECT (rank() OVER (PARTITIONBY con.conrelid ORDERBY
attr.attnum))::SMALLINTAS r_PrimaryKeyNo,
attr.attnum AS r_AttributeNumber,attr.attname::NAMEAS r_AttributeName,
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN typ.typname::NAMEELSE''::NAMEENDAS r_UserTypeName,
FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAMEAS 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) asNoFROM pg_constraint c) con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_attribute attrON attr.attrelid=con.conrelid
AND attr.attnum=con.conkey[con.No]
LEFTOUTERJOIN pg_type typ ON attr.atttypid=typ.oid
LEFTOUTERJOIN pg_type btyp ON typ.typbasetype=btyp.oid
LEFTOUTERJOIN pg_description dsc ON dsc.objoid=attr.attrelid
AND dsc.objsubid=attr.attnum
WHERE con.contype=c_PrimaryKeyKind
ANDLOWER(nspc.nspname)=LOWER(a_SchemaName)
ANDLOWER(con.conname)=LOWER(a_PrimaryKeyName)
ORDERBY attr.attnum;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_PrimaryKey_Attributes(a_SchemaName NAME,a_PrimaryKeyName NAME) IS'Функция возвращает список атрибутов первичного ключа и их характеристик ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_PrimaryKey_Attributes (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
/********************************************************************//* Функция возвращает список атрибутов первичного ключа и их характеристик *//********************************************************************/CREATEORREPLACEFUNCTION admtf_PrimaryKey_Attributes
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_PrimaryKeyName VARCHAR(256) defaultNULL/* Название первичного ключа таблицы */
)
RETURNSTABLE (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 CONSTANTCHAR:='p';
--******************************************************************* BEGINRETURNQUERYSELECT 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::TEXTFROM admtf_PrimaryKey_Attributes(a_SchemaName::NAME,a_PrimaryKeyName::NAME) pka;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_PrimaryKey_Attributes(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS'Функция возвращает список атрибутов первичного ключа и их характеристик';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECt * FROM admtf_PrimaryKey_Attributes('public'::NAME,'xpkstreet'::NAME);
SELECt * FROM admtf_PrimaryKey_Attributes('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256));
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_PrimaryKey_ComplexFeatures (a_SchemaName NAME,a_PrimaryKeyName NAME);
/*****************************************************************************//* Функция возвращает список характеристик первичного ключа таблицы, принадлежащей *//* схеме, а также список характеристик его атрибутов *//*****************************************************************************/CREATEORREPLACEFUNCTION admtf_PrimaryKey_ComplexFeatures
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_PrimaryKeyName NAMEdefaultNULL/* Название первичного ключа таблицы */
)
RETURNSTABLE (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 CONSTANTVARCHAR(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;
SELECTINTO 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::NAMEAS rpk_UserTypeName, NULL::NAMEAS rpk_TypeName,
NULL::BOOLEANAS 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);
ENDIF;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_PrimaryKey_ComplexFeatures(a_SchemaName NAME,a_PrimaryKeyName NAME) IS'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_PrimaryKey_ComplexFeatures (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256));
/******************************************************************************//* Функция возвращает список характеристик первичного ключа таблицы, принадлежащей *//* схеме, а также список характеристик его атрибутов *//******************************************************************************/CREATEORREPLACEFUNCTION admtf_PrimaryKey_ComplexFeatures
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_PrimaryKeyName VARCHAR(256) defaultNULL/* Название первичного ключа таблицы */
)
RETURNSTABLE (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 CONSTANTVARCHAR(10):='pk'; /* Категория характеристик *//* первичного ключа таблицы */--************************************************************************* BEGINRETURNQUERYSELECT 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::BOOLEANFROM admtf_PrimaryKey_ComplexFeatures(a_SchemaName::NAME,
a_PrimaryKeyName::NAME) pk;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_PrimaryKey_ComplexFeatures(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS'Возвращает список характеристик первичного ключа таблицы, принадлежащей схеме, а также список характеристик его атрибутов';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECt * FROM admtf_PrimaryKey_ComplexFeatures('public'::NAME,'xpkstreet'::NAME);
SELECt * FROM admtf_PrimaryKey_ComplexFeatures('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256));
Creating the function admtf_ForeignKey_ComplexFeatures
Comments on the source code of the function can be found here.
function code
Комментарии к исходному коду функции можно посмотреть здесь.
Создание функции admtf_ForeignKey_Features
код функции
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_Features (a_SchemaName NAME,a_ForeignKeyName NAME);
/**************************************************************//* Функция возвращает список характеристик внешнего ключа таблицы, *//* принадлежащего схеме *//**************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_Features
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_ForeignKeyName NAMEdefaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE (rs_ForeignKeyName NAME,rs_ForeignKeyDescription TEXT) AS
$BODY$
DECLARE c_ForeignKeyKind CONSTANTCHAR:='f';
v_ForeignKeyOID OID; /* ИД внешнего ключа таблицы */
v_ForeignKeyName NAME; /* Название внешнего ключа таблицы */
v_ForeignKeyDescription TEXT; /* Описание внешнего ключа таблицы */
v_MasterTableName NAME; /* Название таблицы, которой принадлежит первичный ключ */--************************************************************************ BEGINSELECTINTO rs_ForeignKeyName,rs_ForeignKeyDescription
con.conname,COALESCE(dsc.description,'Внешний ключ таблицы '|| tbl.relname)
FROM pg_constraint con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_class tbl ON con.conrelid=tbl.oid
LEFTOUTERJOIN pg_Description dsc ON con.oid=dsc.objoid
AND dsc.objsubid=0WHERE 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;
COMMENTONFUNCTION admtf_ForeignKey_Features(a_SchemaName NAME,a_ForeignKeyName NAME) IS'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_Features (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256));
/********************************************************************************************************//* Функция возвращает список характеристик внешнего ключа таблицы, принадлежащего схеме *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_Features
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_ForeignKeyName VARCHAR(256) defaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE (rs_ForeignKeyName VARCHAR(256),rs_ForeignKeyDescription TEXT) AS
$BODY$
DECLARE c_ForeignKeyKind CONSTANTCHAR:='f';
--******************************************************************* BEGINRETURNQUERYSELECT fkf.rs_ForeignKeyName::VARCHAR(256),
fkf.rs_ForeignKeyDescription::TEXTFROM admtf_ForeignKey_Features
(a_SchemaName::NAME,a_ForeignKeyName::NAME) fkf;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_ForeignKey_Features(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
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
код функции
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_Attributes (a_SchemaName NAME,a_ForeignKeyName NAME);
/**************************************************************//* Функция возвращает список характеристик внешнего ключа таблицы, *//* принадлежащего схеме *//**************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_Attributes
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_ForeignKeyName NAMEdefaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE (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 CONSTANTCHAR:='f';
--**************************************************************** BEGINRETURNQUERYSELECT (rank() OVER (PARTITIONBY con.conrelid ORDERBY con.No))::SMALLINTAS r_ForeingKeyNo,
attr.attnum AS r_AttributeNumber,attr.attname::NAMEAS r_AttributeName,
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN typ.typname::NAMEELSE''ENDAS r_UserTypeName,
FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAMEAS 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) asNoFROM pg_constraint c) con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_attribute attrON attr.attrelid=con.conrelid
AND attr.attnum=con.conkey[con.No]
INNERJOIN pg_type typ ON attr.atttypid=typ.oid
LEFTOUTERJOIN pg_type btyp ON typ.typbasetype=btyp.oid
LEFTOUTERJOIN 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)
ORDERBY con.No;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_ForeignKey_Attributes(a_SchemaName NAME,a_ForeignKeyName NAME) IS'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_Attributes (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256));
/**************************************************************//* Функция возвращает список характеристик внешнего ключа таблицы, *//* принадлежащего схеме *//**************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_Attributes
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_ForeignKeyName VARCHAR(256) defaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE (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 CONSTANTCHAR:='f';
--***************************************************************** BEGINRETURNQUERYSELECT 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::TEXTFROM admtf_ForeignKey_Attributes(a_SchemaName::NAME,a_ForeignKeyName::NAME) fka;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_ForeignKey_Attributes(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
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
код функции
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_ReferenceTableFeatures (a_SchemaName NAME,a_ForeignKeyName NAME);
/*******************************************************************//* Функция возвращает список характеристик таблицы, на которую ссылается *//* внешний ключ *//*******************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_ReferenceTableFeatures
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_ForeignKeyName NAMEdefaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE (rfkrt_ReferenceTableName NAME,rfkrt_ReferenceTableDescription TEXT) AS
$BODY$
DECLARE c_ForeignKeyKind CONSTANTCHAR:='f';
v_ReferenceTableOID OID; /* ИД таблицы, на которую ссылается внешний ключ */
v_ReferenceTableName NAME; /* Название таблицы, на которую ссылается внешний ключ*/
v_ReferenceTableDescription TEXT; /*Описание таблицы, на которую ссылается внешний ключ */
v_MasterTableName NAME; /* Название таблицы, которой принадлежит внешний ключ */--******************************************************************* BEGINSELECTINTO v_ReferenceTableName rtbl.relname
FROM pg_constraint con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN 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);
ENDIF;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName NAME,a_ForeignKeyName NAME) IS'Возвращает список характеристик таблицы, на которую ссылается внешний ключ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_ReferenceTableFeatures (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256));
/*******************************************************************//* Функция возвращает список характеристик таблицы, на которую ссылается *//* внешний ключ *//******************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_ReferenceTableFeatures
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_ForeignKeyName VARCHAR(256) defaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE (rfkrt_ReferenceTableName VARCHAR(256),rfkrt_ReferenceTableDescription TEXT) AS
$BODY$
DECLARE c_ForeignKeyKind CONSTANTCHAR:='f';
--********************************************************************* BEGINRETURNQUERYSELECT fkrt.rfkrt_ReferenceTableName::VARCHAR(256),
fkrt.rfkrt_ReferenceTableDescription::TEXTFROM admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName::NAME,
a_ForeignKeyName::NAME) fkrt;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS'Возвращает список характеристик таблицы, на которую ссылается внешний ключ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
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
код функции
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_ReferenceTableAttributes (a_SchemaName NAME,a_ForeignKeyName NAME);
/******************************************************************//* Функция возвращает список характеристик атрибутов таблицы, на которую *//* сылается внешний ключ *//******************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_ReferenceTableAttributes
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_ForeignKeyName NAMEdefaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE(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 CONSTANTCHAR:='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; /* Порядковый номер атрибута внешнего ключа*/--****************************************************************************************************** BEGINRETURNQUERYSELECT (rank() OVER (PARTITIONBY con.confrelid
ORDERBY con.No))::SMALLINTAS r_ReferenceTableKeyNo,
attr.attnum AS r_AttributeNumber,attr.attname::NAMEAS r_AttributeName,
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN typ.typname::NAMEELSE''ENDAS r_UserTypeName,
FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
COALESCE(NULLIF(typ.typtypmod,-1),
attr.atttypmod))::NAMEAS 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) asNoFROM pg_constraint c) con
INNERJOIN pg_namespace nspc ON con.connamespace = nspc.oid
INNERJOIN pg_attribute attrON attr.attrelid=con.confrelid
AND attr.attnum=con.confkey[con.No]
INNERJOIN pg_type typ ON attr.atttypid=typ.oid
LEFTOUTERJOIN pg_type btyp ON typ.typbasetype=btyp.oid
LEFTOUTERJOIN 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)
ORDERBY con.No;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_ForeignKey_ReferenceTableAttributes(a_SchemaName NAME,a_ForeignKeyName NAME) IS'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_ReferenceTableAttributes (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256));
/*********************************************************//* Функция возвращает список характеристик атрибутов таблицы, *//* на которую сылается внешний ключ *//********************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_ReferenceTableAttributes
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_ForeignKeyName VARCHAR(256) defaultNULL/* Название внешнего ключа таблицы */
)
RETURNSTABLE (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 CONSTANTCHAR:='f';
--**************************************************************** BEGINRETURNQUERYSELECT 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::TEXTFROM admtf_ForeignKey_ReferenceTableAttributes(a_SchemaName::NAME,
a_ForeignKeyName::NAME) fkra;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_ForeignKey_ReferenceTableAttributes(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS'Возвращает список характеристик внешнего ключа таблицы, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECt * FROM admtf_ForeignKey_ReferenceTableAttributes('public'::VARCHAR(256),'fk_street_locality'::VARCHAR(256));
SELECt * FROM admtf_ForeignKey_ReferenceTableAttributes('public'::NAME,'fk_street_locality'::NAME);
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_ReferenceTableComplexFeatures (a_SchemaName NAME,a_ForeignKeyName NAME,a_ForeignKeyNo SMALLINT);
/*************************************************************//* Функция возвращает список характеристик таблицы, которую *//* ссылается внешний ключ, а также список характеристик ее атрибутов *//*************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_ForeignKeyName NAMEdefaultNULL, /* Название внешнего ключа таблицы */
a_ForeignKeyNo SMALLINTdefaultNULL/* Порядковый номер внешнего ключа таблицы*/
)
RETURNSTABLE (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 CONSTANTVARCHAR(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;
SELECTINTO 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::NAMEAS fkrt_UserTypeName,
NULL::NAMEAS fkrt_TypeName, NULL::BOOLEANAS fkrt_isNotNULL ;
ENDIF;
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;
COMMENTONFUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures(a_SchemaName NAME,a_ForeignKeyName NAME,a_ForeignKeyNo SMALLINT) IS'Возвращает список характеристик таблицы, которую ссылается внешний ключ, а также список характеристик ее атрибутов';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_ForeignKey_ReferenceTableComplexFeatures (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256),a_ForeignKeyNo SMALLINT);
/*************************************************************//* Функция возвращает список характеристик таблицы, которую *//* ссылается внешний ключ, а также список характеристик ее атрибутов *//*************************************************************/CREATEORREPLACEFUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_ForeignKeyName VARCHAR(256) defaultNULL,/* Название внешнего ключа таблицы */
a_ForeignKeyNo SMALLINTdefaultNULL/* Порядковый номер внешнего ключа таблицы*/
)
RETURNSTABLE (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 CONSTANTVARCHAR(1):='%';/* Категория характеристик *//* внешнего ключа таблицы */--****************************************************************** BEGINRETURNQUERY
Also popular now:
-
Configuring SSH in Cisco
-
Eye tracking model work
-
The Tale of the Lost Millions
-
Simulate properties for Enumerations in the .NET Framework 3.5
-
IT Services Market Research 2008 and 2009 Forecasts
-
Spotify received go-ahead to host Apple Store
-
Vim. Change multiple files at once. Detailed example with comments
-
Canobuvos Issue 002
-
Laptop + video glasses = when?
-
“Erotica by September 1st!”