Functions for documenting PostgreSQL databases. Part three
This part of the article discusses functions that return the characteristics of sequences, inherited tables , and the special characteristics of table attributes .
See also
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Two ;
Functions for documenting PostgreSQL databases. The end (part four) .
The first half of the article contains comments on the implementation of functions. In the second, 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 a list of characteristics of table sequences
Fig. 2. Functions on which the function admtf_Table_Sequences depends.
Table 11. Function assignment.
| No | Title | Purpose |
|---|---|---|
| one | admtf_Sequence_Features | The function returns a list of table sequence characteristics. |
| 2 | admtf_Table_Sequences | The function returns a list of database table sequences and their characteristics. |
Function admtf_Sequence_Features - list of database sequence characteristics
The function admtf_Sequence_Features returns a list of sequence characteristics (SEQUENCE) of a database. Source code can be viewed and downloaded here .
The function admtf_Sequence_Features returns a list of sequence characteristics ( SEQUENCE ) of a database.
.As parameters, the function takes the name of the sequence ( a_SequenceName ) and the name of the circuit within which the sequence is created ( a_SchemaName ).
The need for the admtf_Sequence_Features function arose from the fact that the main characteristics of a sequence are actually stored in a table whose name matches the sequence name, and the data is extracted from it using the SELECT statement . In this case, the name of the sequence, the name of the schema and commentary on the sequence are stored in the pg_class , pg_namespace and pg_description directories .
SELECT * FROM kr_road_network_vertices_pgr_id_seq;
Remark 6
In version 10, PostgreSQL divided the characteristics of a sequence and the characteristics of its states. For this purpose, the pg_sequence directory with sequence characteristics is entered , containing the initial value ( start_value ), increment ( increment_by ) and maximum value ( max_value ) of the sequence. The last value returned by the sequence ( last_value ) was left in the “table” with the name of the sequence.
The end of the remark.
The representation of each sequence as an analogue of the table, I think, is dictated by the need to store the last used value of the sequence ( last_value ), which is a characteristic of the state of the sequence, but not the sequence as such.
The sequence entry in the pg_class directory differs from the table entry by the value of the relation type (relkind = 'S' ).
In order to extract the characteristics of an arbitrary sequence, you have to use dynamic SQL.
EXECUTE'SELECT last_value,start_value,increment_by,max_value FROM '||
LOWER(a_SchemaName)||'.'||LOWER(a_SequenceName)
INTO v_SequenceLastValue,v_SequenceStartValue,
v_SequenceIncrementBy,v_SequenceMaxValue ;
Table 12. Result of execution of the function admtf_Sequence_Features ('public', 'kr_road_network_vertices_pgr_id_seq').
| Title | Comment | Current | Start | Increment | the end |
|---|---|---|---|---|---|
| kr_road_network _vertices_pgr_id _seq | Sequence | 138023 | one | one | 9223372036854775807 |
The function admtf_Table_Sequences a list of sequences of a database table and their characteristics
The function admtf_Table_Sequences returns a list of sequences ( SEQUENCE ) of a database table, generating the values of its fields, and the characteristics of these sequences. The source code can be viewed and downloaded here , and here is the version of the function in which the cursor is not used .
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
).
SELECT pseq.relname AS SequenceName,snsp.nspname AS SequenceSchemaName,
COALESCE(dsc.description,'Последовательность, генерирующая значения поля '
||da.attname) AS SequenceDescription,
d.depType AS DependcyType,da.attname AS AttributeName
FROM pg_depend d
INNERJOIN pg_class pseq ON d.objid = pseq.oid
INNERJOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid
LEFTOUTERJOIN pg_Description dsc ON pseq.oid=dsc.objoid
AND dsc.objsubid=0INNERJOIN pg_class tbl ON d.refobjid = tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNERJOIN pg_attribute da ON da.attrelid= d.refobjid
AND da.attnum= d.refobjsubid
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
AND tbl.relkind = 'r'AND pseq.relkind = 'S'ORDERBY pseq.relname;
The description of a separate sequence is a collection of entries in pg_class , describing it as a physical relation, and a conditional table with the name of the sequence containing data on the specific characteristics of the sequence
Information about the relationship between the sequence and the source table is stored in the pg_depend system directory .
Table 13. Attributes of the pg_depend directory required to implement the function.
| Title | Description |
|---|---|
| objid | Sequence OID in pg_class directory |
| objsubid | This field is zero. |
| refobjid | The OID of the table, in which fields the sequence is used. |
| refobjsubid | The attribute number of the table, the values of which are populated using the sequence |
Additionally, the function accesses the data in the pg_namespace and pg_description directories in order to extract the schemas and comments of both the sequence and the source table.
To determine the attribute of a table whose values are filled using a sequence, the function refers to the pg_attribute directory by the condition: attrelid = refobjid AND attnum = refobjsubid . (In this condition, the names of pg_depend attributes of the directory are indicated to the right of the equal sign )
The special characteristics of the table sequences are retrieved in a loop by calling the function admtf_Sequence_Features . The cycle is used because more than one sequence can be assigned to fill in the table fields.
Table 14. Result of execution of the function admtf_Table_Sequences ('public', 'kr_road_network_vertices_pgr').
| Title | Comment | Start | Increment | the end | Field |
|---|---|---|---|---|---|
| kr_road_network _vertices_pgr_id _seq | A sequence that generates id field values | one | one | 9223372036854775807 | id |
Version without cursor
In a PostgreSQL environment with a version less than 10 , it’s most likely impossible to implement the function admtf_Table_Sequences without using a cursor.
But the happy owners of version 10 may well do without a cursor, because they have the pg_sequence directory at their disposal . In this case, all characteristics of the sequence can be retrieved with a single SELECT statement .
In the above implementation of the function, using the window function RANK () OVER (PARTITION BY pseq.relname) , the sequence number of the sequence used to populate the original table is calculated.
SELECTRANK() OVER (PARTITIONBY pseq.relname) AS SequenceNo,
pseq.relname AS SequenceName,snsp.nspname AS SequenceSchemaName,
COALESCE(dsc.description,'Последовательность, генерирующая значения поля '
||da.attname) AS SequenceDescription,
seq.seqstart AS SequenceStartValue,seq.seqincrement AS SequenceIncrementBy,
seq.seqmax AS SequenceMaxValue,
d.depType AS DependcyType,da.attname AS AttributeName
FROM pg_depend d
INNERJOIN pg_class pseq ON d.objid = pseq.oid
INNERJOIN pg_sequence seq ON seq.seqrelid= pseq.oid
INNERJOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid
LEFTOUTERJOIN pg_Description dsc ON pseq.oid=dsc.objoid
AND dsc.objsubid=0INNERJOIN pg_class tbl ON d.refobjid = tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNERJOIN pg_attribute da ON da.attrelid= d.refobjid
AND da.attnum= d.refobjsubid
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
AND tbl.relkind = 'r'AND pseq.relkind = 'S'ORDERBY pseq.relname;
Remark 7
.This version of the function does not return the last value generated by the sequence ( last_value ).
The end of the remark.
Function admtf_Table_InheritanceChildrens - a list of characteristics of inherited tables
The admtf_Table_InheritanceChildrens function returns a list of the inherited table characteristics ( INHERITS ) of a database table. Source code can be viewed and downloaded here .
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 ).
The description of a separate inherited table is in an entry in pg_class . But to search for inherited tables by the name of the source table, you have to use the system catalog pg_depend .
Table 15. Attributes of the pg_depend directory required to implement the function.
| Title | Description |
|---|---|
| objid | OID of the inherited table in the pg_class directory |
| refobjid | OID source table |
SELECT rtbl.relname,rnspc.nspname,rdsc.description,rtbl.relnatts::INTEGER,
rtbl.relchecks::INTEGER,
rtbl.relhaspkey,rtbl.relhasindex,rtbl.relhassubclass,
rtbl.reltuples::INTEGERFROM pg_class tbl
INNERJOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid
LEFTOUTERJOIN pg_Description dsc ON tbl.oid=dsc.objoid
AND dsc.objsubid=0INNERJOIN pg_depend dp ON tbl.oid=dp.refobjid
INNERJOIN pg_class rtbl ON rtbl.OID=dp.objid
INNERJOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oid
LEFTOUTERJOIN pg_Description rdsc ON rtbl.oid=rdsc.objoid
AND rdsc.objsubid=0WHERELOWER(nspc.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
AND tbl.relkind = 'r'AND rtbl.relkind = 'r'ORDERBY rtbl.relname;
Additionally, the function accesses the data in the pg_namespace and pg_description directories in order to extract the schemas and comments for both the inherited and the source table.
Table 16. Result of execution of the function admtf_Table_InheritanceChildrens ('public', 'np_house').
| Title | Comment | Attributes | ? primary key | ? indexes | ? descendants | Number of records |
|---|---|---|---|---|---|---|
| np_house 04201 000000 | Houses in settlements (Achinsky district) | 15 | f | f | f | 5651 |
| np_house 4208 000000 | Houses in settlements (Bogotolsky district) | 15 | f | f | f | 4314 |
The number of records in the child table is selected from a catalog attribute reltuple pg_class . Although this value often coincides exactly with the actual number of records in the table, it is still an estimated value. This means there may be a desire to get the exact value as a result. For example, as shown in the figure.
EXECUTE'SELECT COUNT(*) FROM '||LOWER(a_SchemaName)||'.'||LOWER(a_TableName)
INTO v_TableNumberOfRowCalc;
But, first, in order to execute this statement in the text, the function admtf_Table_InheritanceChildrens will have to use a cursor.
Secondly, I would like the function to allow the output of both the estimated and the exact number of table entries.
Therefore, the function has yet another optional - Mode obtain the number of table entries ( a_Mode ), which takes the values "estimated» ( estimate ) or "exactly» ( exactly ).
Additionally, the function admfn_Table_RowCount , which returns the exact number of table entries, is created, and in the list of returned SELECT values, the reltuple attribute is replaced with the following construction.
CASE WHEN a_Mode = 'exactly' THEN admfn_Table_RowCount(rnspc.nspname,rtbl.relname)
ELSE reltuples ENDAs a result, the function returns the estimated value of the “number of table entries” indicator, if the a_Mode parameter does not specify the requirement to return an exact value.
The structure of the function that returns a list of table attribute characteristics
Fig. 3. Functions that admtf_Attribute_Features Calls
| No | Title | Purpose |
|---|---|---|
| one | admtf_Attribute_PKFeatures | The function returns the indication of the presence of an attribute in the primary key (PRIMARY KEY), as well as some of its characteristics as part of this key. |
| 2 | admtf_Attribute_FKFeatures | Функция возвращает признак присутствия атрибута во внешнем ключе ключе (FOREIGN KEY), а также некоторые его характеристики в качестве части этого ключа. |
| 3 | admtf_Attribute_Features | Функция возвращает список характеристик атрибута таблицы. |
Function admtf_Attribute_PKFeatures - - is there an attribute in the primary key
The function admtf_Attribute_PKFeatures returns a sign of the presence of a table attribute in the primary key (PRIMARY KEY) of the table, and, if present, what is its sequence number in this key, since primary key can be composite.
Source code can be viewed and downloaded here .
As parameters, the function takes the OID of the source table ( a_TableOID ) and the sequence number of the required attribute in it ( a_AttributeNo ).
The function extracts the necessary data from the pg_constraint directory entry containing the constraints (CONSTRAINT) of the source table, including the primary key constraint. The OID of the table you are looking for is stored in the conrelid field , the description of the primary key is stored in the record in which the contype field contains the value '' p '
.SELECTINTO v_PKAttributeList,rs_isAttributePK conkey,ARRAY[a_AttributeNo]<@conkey
FROM pg_constraint c
WHERE c.contype='p'AND c.conrelid=a_TableOID;
The conkey field of the entry thus found contains an array of attribute sequence numbers that make up the primary key. Therefore, in order to check the presence of the source attribute in the primary key, it suffices to calculate the logical expression ARRAY [a_AttributeNo] <@ conkey .
If the attribute is present in the primary key, then its sequence number is calculated further in the loop.
Function admtf_Attribute_FKFeatures - is there an attribute in the foreign key
The function admtf_Attribute_FKFeatures returns an indication of the presence of a table attribute in one or more foreign keys (FOREIGN KEY) of the table, and, if present, its serial numbers in these keys, since foreign key can be composite.
Source code can be viewed and downloaded here .
As parameters, the function takes the OID of the source table ( a_TableOID ) and the sequence number of the required attribute in it ( a_AttributeNo ).
The function extracts the necessary data from the pg_constraint directory entry containing the constraints (CONSTRAINT) of the source table, including, but not limited to, foreign key constraints. The OID of the table you are looking for is stored in the conrelid field , the primary key description is stored in the record in which the contype field contains the value '' f '
.SELECT * FROM pg_constraint c
WHERE c.contype='f 'AND c.conrelid=a_TableOID ANDARRAY[a_AttributeNo]<@conkey
ORDERBY c.oid;
The conkey field of the entry thus found contains an array of attribute sequence numbers that make up the foreign key. Therefore, in order to check the presence of the source attribute in the foreign key, it suffices to calculate the logical expression ARRAY [a_AttributeNo] <@ conkey .
If the attribute is present in the foreign key, then later in the loop an array of its ordinal numbers is formed in the foreign keys containing it. Additionally, two more arrays are formed from the names of the tables and their attributes that are referenced by the original attribute in the foreign keys containing it.
Table names are extracted from the pg_class directory entry by identifier (OID) extracted from the confrelid field of the foreign key entry.
To get the name of the attribute of the external table, use the array of ordinal numbers from the field
confkey
(it differs from the above array by the letter “ f ” in the name). From this array is extracted the ordinal number of the attribute of the external table, which corresponds to the external attribute. By this ordinal number of the attribute of the external table and its OID, located in the pg_attribute directory, there is an entry describing the attribute and its name is extracted.Function admtf_Attribute_Features - a list of characteristics of the attribute table
The function admtf_Attribute_Features returns a list of the following characteristics of a table attribute. Source code can be viewed and downloaded here .
| № | Название | Тип | Назначение |
|---|---|---|---|
| 1 | AttributeName | name | Название исходного атрибута. |
| 2 | UserTypeName | VARCHAR(256) | Пользовательский тип исходного атрибута |
| 3 | TypeName | VARCHAR(256) | Базовый тип исходного атрибута |
| 4 | isNotNULL | BOOLEAN | ? Допустимость значения NULL |
| 5 | isAttributePK | BOOLEAN | ? участие в PK |
| 6 | ColumnPKNo | SMALLINT | Порядковый номер атрибута в PK |
| 7 | Description | TEXT | Комментарий к исходному атрибуту |
| 8 | isAttributeFK | BOOLEAN | ? участие в FK |
| 9 | FKeyName | name[] | Массив названий ограничений таблицы, в которых определен внешний ключ |
| 10 | ColumnFKNo | SMALLINT[] | Массив порядковых номеров атрибута во внешних ключах таблицы |
| 11 | FKTableName | name[] | Массив таблиц, на которые ссылаются внешние ключи |
| 12 | FKTableColumnName | name[] | Массив названий атрибутов в внешних таблицах, соответствующих исходному атрибуту |
As parameters, the function takes the OID of the source table ( a_TableOID ) and the sequence number of the required attribute in it ( a_AttributeNo ).
The values of the AttributeName and isNotNULL fields are extracted from the pg_attribute directory entry corresponding to the values of the input parameters.
SELECT attr.attname, attr.attnotnull FROM pg_attribute attrWHERE attr.attrelid =a_TableOID AND attr.attnum=a_AttributeNo;
SELECT rs_isAttributePK,rs_ColumnPKNo
FROM admtf_Attribute_PKFeatures (a_TableOID,a_AttributeNo);
SELECT rs_isAttributeFK,rs_FKeyName,rs_ColumnFKNo,
rs_FKTableName,rs_FKTableColumnName
FROM admtf_Attribute_FKFeatures (a_TableOID,a_AttributeNo);
The values of the isAttributePK and ColumnPKNo fields are returned by the function admtf_Attribute_PKFeatures .
The values of the fields isAttributeFK , FKeyName , ColumnFKNo , FKTableName , FKTableColumnName are returned by the function admtf_Attribute_FKFeatures .
Calling the function admtf_Attribute_Features ((SELECT OID FROM pg_class WHERE relname = 'street'), 2 :: SMALLINT) will result in the following result.
Table 18. The result of the function execution admtf_Attribute_Features
| AttributeName | UserTypeName | Typename | isNotNULL | isAttributePK | ColumnPKNo |
|---|---|---|---|---|---|
| localityid | localityid | integer | integer | integer | integer |
| Description | isAttributeFK | FKeyName | Columnfkno | FKTableName | FKTableColumnName |
|---|---|---|---|---|---|
| ID of the settlement | t | {fk_street_locality} | {2} | {locality} | {localityid} |
APPENDIX 1. Scripts
Create function admtf_Sequence_Features
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Sequence_Features (a_SchemaName NAME,a_SequenceName NAME);
/****************************************************************************//* Функция возвращает список характеристик последовательности, принадлежащей схеме *//****************************************************************************/CREATEORREPLACEFUNCTION admtf_Sequence_Features
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_SequenceName NAMEdefaultNULL/* Название последовательности */
)
RETURNSTABLE (rs_SequenceName NAME,rs_SequenceDescription TEXT,rs_NumberOfAttribute INTEGER,rs_SequenceLastValue BIGINT,
rs_SequenceStartValue BIGINT,rs_SequenceIncrementBy BIGINT,rs_SequenceMaxValue BIGINT) AS
$BODY$
DECLARE
c_SequenceKind CONSTANTCHAR:='S';
v_SequenceOID OID; /* ИД последовательности */
v_SequenceName NAME; /* Название последовательности */
v_SequenceDescription TEXT; /* Описание последовательности */
v_SequenceStartValue BIGINT; /* Начальное значение последовательности */
v_SequenceIncrementBy BIGINT; /* Приращение последовательности */
v_SequenceMaxValue BIGINT; /* Максимальное значение последовательности */
v_SequenceLastValue BIGINT; /* Максимальное значение последовательности */
v_SequenceNumberOfRowCalc INTEGER; /* Число записей в таблице */--************************************************************************ BEGINSELECTINTO rs_SequenceName,rs_SequenceDescription,rs_NumberOfAttribute
tbl.relname,
COALESCE(dsc.description,'Последовательность') AS r_SequenceDescription,
tbl.relnatts::INTEGER,tbl.relchecks::INTEGER,tbl.relhaspkey,
tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::INTEGERFROM pg_class tbl
INNERJOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid
LEFTOUTERJOIN pg_Description dsc ON tbl.oid=dsc.objoid
AND dsc.objsubid=0WHERE nspc.nspname=LOWER(a_SchemaName)
AND tbl.relkind=c_SequenceKind
AND tbl.relname =LOWER(a_SequenceName);
IF FOUND THEN
EXECUTE'SELECT last_value,start_value,increment_by,max_value
FROM '||LOWER(a_SchemaName)||'.'||LOWER(a_SequenceName) INTO
v_SequenceLastValue,v_SequenceStartValue,
v_SequenceIncrementBy,v_SequenceMaxValue ;
RETURN QUERY SELECT rs_SequenceName,rs_SequenceDescription,
rs_NumberOfAttribute,v_SequenceLastValue,
v_SequenceStartValue,v_SequenceIncrementBy,
v_SequenceMaxValue;
ENDIF;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Sequence_Features(a_SchemaName NAME,a_SequenceName NAME) IS'Функция возвращает список характеристик последовательности, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Sequence_Features (a_SchemaName VARCHAR(256),a_SequenceName VARCHAR(256));
/****************************************************************************//* Функция возвращает список характеристик последовательности, принадлежащей схеме *//****************************************************************************/CREATEORREPLACEFUNCTION admtf_Sequence_Features
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_SequenceName VARCHAR(256) defaultNULL/* Название последовательности */
)
RETURNSTABLE (rs_SequenceName VARCHAR(256),rs_SequenceDescription TEXT,
rs_NumberOfAttribute INTEGER,rs_SequenceLastValue BIGINT,
rs_SequenceStartValue BIGINT,rs_SequenceIncrementBy BIGINT,
rs_SequenceMaxValue BIGINT) AS
$BODY$
DECLARE
c_SequenceKind CONSTANTCHAR:='S';
--******************************************************** BEGINRETURNQUERYSELECT sf.rs_SequenceName::VARCHAR(256),
sf.rs_SequenceDescription::TEXT,
sf.rs_NumberOfAttribute::INTEGER,
sf.rs_SequenceLastValue::BIGINT,
sf.rs_SequenceStartValue::BIGINT,
sf.rs_SequenceIncrementBy::BIGINT,
sf.rs_SequenceMaxValue::BIGINTFROM admtf_Sequence_Features(a_SchemaName::NAME,a_SequenceName::NAME) sf;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Sequence_Features(a_SchemaName VARCHAR(256),a_SequenceName VARCHAR(256)) IS'Функция возвращает список характеристик последовательности, принадлежащей схеме';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Sequence_Features('public'::VARCHAR(255),'k_dorogi_dijkstra_seq_seq'::VARCHAR(255));
SELECT * FROM admtf_Sequence_Features('public'::NAME,'kr_road_network_vertices_pgr_id_seq'::NAME);
Create function admtf_Table_Sequences
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME);
/*********************************************************************//* Функция возвращает список последовательностей, от которых зависит таблица *//*********************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Sequences
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_TableName NAMEdefaultNULL/* Название таблицы */
)
RETURNSTABLE (r_SequenceNumber SMALLINT,r_SequenceName NAME,
r_SequenceSchemaName NAME,r_SequenceDescription TEXT,
r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
r_SequenceMaxValue BIGINT,r_DependType NAME,
r_RefTableName NAME,r_RefTableSchemaName NAME,
r_RefAttributeName NAME) AS
$BODY$
DECLARE
v_TableOID INTEGER;/* OID таблицы*/
v_Sequence RECORD;/* Запись о последовательности*/
v_SequenceOID INTEGER;/* OID ограничения*/
v_SequenceName NAME; /* Название последовательности */
v_SequenceSchemaName NAME; /* Название схемы последовательности */
v_SequenceDescription TEXT; /* Описание последовательности */
v_SequenceStartValue BIGINT; /* Начальное значение последовательности */
v_SequenceIncrementBy BIGINT; /* Приращение последовательности */
v_SequenceMaxValue BIGINT; /* Максимальное значение последовательности */
v_DependcyType NAME; /* Буквенное обозначение типа зависимости *//* таблицы от последовательности */
v_AttributeName NAME; /* Наименование аттрибута*/
v_SequenceNumber SMALLINT; /* Порядковый номер последовательности*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--********************************************************************* BEGIN
v_SequenceNumber:=0;
FOR v_Sequence IN SELECT pseq.relname AS SequenceName,
snsp.nspname AS SequenceSchemaName,
COALESCE(dsc.description,'Последовательность, генерирующая
значения поля '||da.attname) AS SequenceDescription,
d.depType AS DependcyType,da.attname AS AttributeName
FROM pg_depend d
INNERJOIN pg_class pseq ON d.objid = pseq.oid
INNERJOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid
LEFTOUTERJOIN pg_Description dsc ON pseq.oid=dsc.objoid
AND dsc.objsubid=0INNERJOIN pg_class tbl ON d.refobjid = tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNERJOIN pg_attribute da ON da.attrelid= d.refobjid
AND d.refobjsubid=da.attnum
WHERE tbl.relkind = 'r'AND pseq.relkind = 'S'ANDLOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
ORDERBY pseq.relname
LOOP
v_SequenceNumber:=v_SequenceNumber+1;
v_SequenceName:=v_Sequence.SequenceName;
v_SequenceSchemaName:=v_Sequence.SequenceSchemaName;
v_DependcyType:=v_Sequence.DependcyType;
v_AttributeName:=v_Sequence.AttributeName;
v_SequenceDescription:=v_Sequence.SequenceDescription;
SELECTINTO v_SequenceStartValue,v_SequenceIncrementBy,
v_SequenceMaxValue
rs_SequenceStartValue,rs_SequenceIncrementBy,
rs_SequenceMaxValue
FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName);
RETURN QUERY SELECT v_SequenceNumber,v_SequenceName,
v_SequenceSchemaName,v_SequenceDescription,
v_SequenceStartValue,v_SequenceIncrementBy,
v_SequenceMaxValue,v_DependcyType,
a_TableName,a_SchemaName,v_AttributeName;
ENDLOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/**********************************************************************//* Функция возвращает список последовательностей, от которых зависит таблица *//**********************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Sequences
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_TableName VARCHAR(256) defaultNULL/* Название таблицы */
)
RETURNSTABLE (r_SequenceNumber SMALLINT,r_SequenceName VARCHAR(256),
r_SequenceSchemaName VARCHAR(256),r_SequenceDescription TEXT,
r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
r_SequenceMaxValue BIGINT,r_DependType VARCHAR(256),
r_RefTableName VARCHAR(256),r_RefTableSchemaName VARCHAR(256),
r_RefAttributeName VARCHAR(256)) AS
$BODY$
DECLARE
c_Delimiter CONSTANTVARCHAR(2):=',';
--****************************************************** BEGINRETURNQUERYSELECT ts.r_SequenceNumber::SMALLINT,
ts.r_SequenceName::VARCHAR(256),
ts.r_SequenceSchemaName::VARCHAR(256) ,
ts.r_SequenceDescription::TEXT,
ts.r_SequenceStartValue::BIGINT,
ts.r_SequenceIncrementBy::BIGINT,
ts.r_SequenceMaxValue::BIGINT,
ts.r_DependType::VARCHAR(256),
ts.r_RefTableName::VARCHAR(256),
ts.r_RefTableSchemaName::VARCHAR(256),
ts.r_RefAttributeName::VARCHAR(256)
FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME) ts;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Table_Sequences('public'::VARCHAR(255),'kr_road_network_vertices_pgr'::VARCHAR(255));
SELECT * FROM admtf_Table_Sequences('public'::NAME,'kr_road_network_vertices_pgr'::NAME);
Creating the function admtf_Table_Sequences without a cursor (PostgreSQL 10)
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME);
/*********************************************************************//* Функция возвращает список последовательностей, от которых зависит таблица *//**********************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Sequences
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_TableName NAMEdefaultNULL/* Название таблицы */
)
RETURNSTABLE (r_SequenceNumber SMALLINT,r_SequenceName NAME,
r_SequenceSchemaName NAME,r_SequenceDescription TEXT,
r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
r_SequenceMaxValue BIGINT,r_DependType NAME,
r_RefTableName NAME,r_RefTableSchemaName NAME,
r_RefAttributeName NAME) AS
$BODY$
DECLARE
v_TableOID INTEGER; /* OID таблицы*/
v_Sequence RECORD; /* Запись о последовательности*/
v_SequenceOID INTEGER; /* OID ограничения*/
v_SequenceName NAME; /* Название последовательности */
v_SequenceSchemaName NAME; /* Название схемы последовательности */
v_SequenceDescription TEXT; /* Описание последовательности */
v_SequenceStartValue BIGINT; /* Начальное значение последовательности */
v_SequenceIncrementBy BIGINT; /* Приращение последовательности */
v_SequenceMaxValue BIGINT; /* Максимальное значение последовательности */
v_DependcyType NAME; /* Буквенное обозначение типа зависимости таблицы от последовательности */
v_AttributeName NAME; /* Наименование аттрибута*/
v_SequenceNumber SMALLINT; /* Порядковый номер последовательности*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--****************************************************************** BEGIN
v_SequenceNumber:=0;
FOR v_Sequence IN SELECT pseq.relname AS SequenceName,
snsp.nspname AS SequenceSchemaName,
COALESCE(dsc.description,'Последовательность, генерирующая
значения поля '||da.attname) AS SequenceDescription,
d.depType AS DependcyType,da.attname AS AttributeName
FROM pg_depend d
INNERJOIN pg_class pseq ON d.objid = pseq.oid
INNERJOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid
LEFTOUTERJOIN pg_Description dsc ON pseq.oid=dsc.objoid
AND dsc.objsubid=0INNERJOIN pg_class tbl ON d.refobjid = tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNERJOIN pg_attribute da ON da.attrelid= d.refobjid
ND d.refobjsubid=da.attnum
WHERE tbl.relkind = 'r'AND pseq.relkind = 'S'ANDLOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
ORDERBY pseq.relname
LOOP
v_SequenceNumber:=v_SequenceNumber+1;
v_SequenceName:=v_Sequence.SequenceName;
v_SequenceSchemaName:=v_Sequence.SequenceSchemaName;
v_DependcyType:=v_Sequence.DependcyType;
v_AttributeName:=v_Sequence.AttributeName;
v_SequenceDescription:=v_Sequence.SequenceDescription;
SELECTINTO v_SequenceStartValue,v_SequenceIncrementBy,v_SequenceMaxValue
rs_SequenceStartValue,rs_SequenceIncrementBy,rs_SequenceMaxValue
FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName);
RETURN QUERY SELECT v_SequenceNumber,v_SequenceName,
v_SequenceSchemaName,v_SequenceDescription,
v_SequenceStartValue,v_SequenceIncrementBy,
v_SequenceMaxValue,v_DependcyType,
a_TableName,a_SchemaName,v_AttributeName;
ENDLOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/**********************************************************************//* Функция возвращает список последовательностей, от которых зависит таблица *//**********************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Sequences
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_TableName VARCHAR(256) defaultNULL/* Название таблицы */
)
RETURNSTABLE (r_SequenceNumber SMALLINT,r_SequenceName VARCHAR(256),
r_SequenceSchemaName VARCHAR(256),r_SequenceDescription TEXT,
r_SequenceStartValue BIGINT,r_SequenceIncrementBy BIGINT,
r_SequenceMaxValue BIGINT,r_DependType VARCHAR(256),
r_RefTableName VARCHAR(256),r_RefTableSchemaName VARCHAR(256),
r_RefAttributeName VARCHAR(256)) AS
$BODY$
DECLARE
c_Delimiter CONSTANTVARCHAR(2):=',';
--******************************************************* BEGINRETURNQUERYSELECT ts.r_SequenceNumber::SMALLINT,
ts.r_SequenceName::VARCHAR(256),
ts.r_SequenceSchemaName::VARCHAR(256),
ts.r_SequenceDescription::TEXT,
ts.r_SequenceStartValue::BIGINT,
ts.r_SequenceIncrementBy::BIGINT,
ts.r_SequenceMaxValue::BIGINT,
ts.r_DependType::VARCHAR(256),
ts.r_RefTableName::VARCHAR(256),
ts.r_RefTableSchemaName::VARCHAR(256),
ts.r_RefAttributeName::VARCHAR(256)
FROM admtf_Table_Sequences(a_SchemaName::NAME,a_TableName::NAME) ts;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS'Возвращает список последовательностей, от которых зависит таблица';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Table_Sequences('public'::VARCHAR(255),
'kr_road_network_vertices_pgr'::VARCHAR(255));
SELECT * FROM admtf_Table_Sequences('public'::NAME,
'kr_road_network_vertices_pgr'::NAME);
Create function admfn_Table_RowCount
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admfn_Table_RowCount (a_SchemaName NAME,a_TableName NAME);
/******************************************************//* Функция возвращает число строк в таблице *//******************************************************/CREATEORREPLACEFUNCTION admfn_Table_RowCount
(a_SchemaName NAMEdefault'public',/* название схемы базы данных */
a_TableName NAMEdefaultNULL/* Название таблицы */
)
RETURNSBIGINTAS
$BODY$
DECLARE v_TableNumberOfRowCalc BIGINT; /* Количество */
v_Found BOOLEAN;
--*********************************************************** BEGINIF a_SchemaName ~ E'^[a-z_0-9]+$'AND a_TableName ~ E'^[a-z_0-9]+$'THENEXECUTE'SELECT count(*) FROM ' ||a_SchemaName ||'.'|| a_TableName
INTO v_TableNumberOfRowCalc;
ELSE
SELECTINTO v_Found trueFROM pg_class tbl
INNERJOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid
WHERE tbl.relkind='r'AND tbl.relname=a_TableName
AND nspc.nspname=a_SchemaName;
IF FOUND THEN
EXECUTE'SELECT count(*) FROM ' ||
CASEWHEN a_SchemaName ~ E'^[a-z_0-9]+$'THEN
a_SchemaName
ELSE quote_ident(a_SchemaName) END
||'.'||
CASEWHEN a_TableName ~ E'^[a-z_0-9]+$'THEN
a_TableName
ELSE quote_ident(a_TableName) ENDINTO v_TableNumberOfRowCalc;
ELSE
SELECTINTO v_Found trueFROM pg_class tbl
INNERJOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid
WHERE tbl.relkind='r'ANDLOWER(tbl.relname)= LOWER(a_TableName)
AND nspc.nspname=LOWER(a_SchemaName);
IF FOUND THEN
EXECUTE'SELECT count(*) FROM ' || a_SchemaName ||'.'||a_TableName
INTO v_TableNumberOfRowCalc;
ENDIF;
ENDIF;
ENDIF;
RETURN v_TableNumberOfRowCalc;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admfn_Table_RowCount(a_SchemaName NAME,a_TableName NAME) IS'Возвращает число строк в таблице';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admfn_Table_RowCount (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256));
/********************************************************************//* Функция возвращает число строк в таблице *//********************************************************************/CREATEORREPLACEFUNCTION admfn_Table_RowCount
(a_SchemaName VARCHAR(256) default'public',/* название схемы базы данных */
a_TableName VARCHAR(256) defaultNULL/* Название таблицы */
)
RETURNSBIGINTAS
$BODY$
DECLARE v_TableNumberOfRowCalc BIGINT; /* Количество */--********************************************************* BEGINRETURN admfn_Table_RowCount(a_SchemaName::NAME,a_TableName::NAME);
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admfn_Table_RowCount(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS'Возвращает число строк в таблице';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECt admfn_Table_RowCount('public'::NAME,'Street'::NAME);
SELECt admfn_Table_RowCount('public'::VARCHAR(256),'Street'::VARCHAR(256));
Create function admtf_Table_InheritanceChildrens
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_InheritanceChildrens (a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10));
/************************************************************//* Функция возвращает список характеристик унаследованных таблиц *//************************************************************/CREATEORREPLACEFUNCTION admtf_Table_InheritanceChildrens
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_TableName NAMEdefaultNULL, /* Название таблицы */
a_Mode VARCHAR(10) default'estimate'/*Режим вычисления количества записей в таблице*/
)
RETURNSTABLE (rs_TableName NAME,rs_TableDescription TEXT,
rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,
rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,
rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS
$BODY$
DECLARE
c_TableKind CONSTANTCHAR:='r';
c_ExactlyMode CONSTANT VARCHAR(10):='exactly';
c_EstimateMode CONSTANT VARCHAR(10):='estimate';
v_TableOID OID; /* ИД таблицы */
v_SchemaName NAME; /* Название схемы таблицы */
v_TableName NAME; /* Название таблицы */
v_TableDescription TEXT; /* Описание таблицы */
v_TableNumberOfRowCalc INTEGER; /* Число записей в таблице */
v_InheritanceRECORD RECORD; /* Запись унаследованной таблицы */
v_InheritanceOID OID; /* ИД унаследованной таблицы */BEGINRETURNQUERYSELECT rtbl.relname,rdsc.description,rtbl.relnatts::INTEGER,
rtbl.relchecks::INTEGER,rtbl.relhaspkey,rtbl.relhasindex,
rtbl.relhassubclass,
CASEWHEN a_Mode=c_ExactlyMode THEN
admfn_Table_RowCount(rnspc.nspname,rtbl.relname)::INTEGERELSE rtbl.reltuples::INTEGERENDFROM pg_class tbl
INNERJOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid
LEFTOUTERJOIN pg_Description dsc ON tbl.oid=dsc.objoid
AND dsc.objsubid=0INNERJOIN pg_depend dp ON tbl.oid=dp.refobjid
INNERJOIN pg_class rtbl ON rtbl.OID=dp.objid
INNERJOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oid
LEFTOUTERJOIN pg_Description rdsc ON rtbl.oid=rdsc.objoid
AND rdsc.objsubid=0WHERE nspc.nspname=LOWER(a_SchemaName)
AND tbl.relkind=c_TableKind AND rtbl.relkind=c_TableKind
AND tbl.relname =LOWER(a_TableName)
ORDERBY rtbl.relname;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_InheritanceChildrens(a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10)) IS'Возвращает список характеристик унаследованных таблиц';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_InheritanceChildrens (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_TableName NAME,a_Mode VARCHAR(10));
/************************************************************************//* Функция возвращает список характеристик унаследованных таблиц *//************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_InheritanceChildrens
(a_SchemaName VARCHAR(256) default'public',/* название схемы базы данных */
a_TableName VARCHAR(256) defaultNULL,/* Название таблицы */
a_Mode VARCHAR(10) default'estimate'/*Режим вычисления количества записей в таблице*/
)
RETURNSTABLE (rs_TableName VARCHAR(256),rs_TableDescription TEXT,
rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,
rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,
rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS
$BODY$
DECLARE
c_TableKind CONSTANTCHAR:='r';
BEGINRETURNQUERYSELECT tic.rs_TableName::VARCHAR(256),tic.rs_TableDescription::TEXT,
tic.rs_NumberOfAttribute::INTEGER,tic.rs_NumberOfChecks::INTEGER,
tic.rs_hasPKey::BOOLEAN,tic.rs_hasIndex::BOOLEAN,
tic.rs_hasSubClass::BOOLEAN,tic.rs_NumberOfRow::INTEGERFROM admtf_Table_InheritanceChildrens(a_SchemaName::NAME,
a_TableName::NAME,a_Mode::VARCHAR(10)) tic;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_InheritanceChildrens(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256),a_Mode VARCHAR(10)) IS'Возвращает список характеристик унаследованных таблиц';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Table_InheritanceChildrens('public'::NAME,'np_house'::NAME);
SELECT * FROM admtf_Table_InheritanceChildrens('public'::VARCHAR(256),'np_house'::VARCHAR(256));
Create function admtf_Attribute_PKFeatures
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Attribute_PKFeatures (a_TableOID OID,a_AttributeNo SMALLINT);
/***************************************************************************//* Функция возвращает признак присутствия колонки в первичном ключе. *//* Если колонка присутствует, то возвращается и ее порядковый номер в списке колонок *//* первичного ключа *//***************************************************************************/CREATEORREPLACEFUNCTION admtf_Attribute_PKFeatures
(a_TableOID OID, /* ИД таблицы */
a_AttributeNo SMALLINT/* Порядковый номер атрибута в таблице*/
)
RETURNSTABLE (rs_isAttributePK BOOLEAN,rs_PKeyName name,rs_ColumnPKNo SMALLINT) AS
$BODY$
DECLARE C_PKAttributeList_NDims CONSTANTINTEGER:=1;
/* Размерность массива атрибутов первичного ключа*/
v_PKAttributeList SMALLINT[];
/* Список номеров атребутов в составе первичного ключа*/
v_PKAttributeIndx INTEGER;
/* Текущий индекс масссива атрибутов первичного ключа*/
v_PKAttributeLBound INTEGER;
/* Нижний индекс масссива атрибутов первичного ключа*/
v_PKAttributeUBound INTEGER;
/* Верхний индекс масссива атрибутов первичного ключа*/--********************************************************************** BEGIN
rs_isAttributePK:=false;
rs_ColumnPKNo:=NULL;
SELECTINTO rs_PKeyName,v_PKAttributeList,rs_isAttributePK
conname,conkey,ARRAY[a_AttributeNo]<@conkey
FROM pg_constraint c
WHERE c.contype='p'and c.conrelid=a_TableOID;
IF FOUND AND rs_isAttributePK THEN -- колонка присутсвует в первичном ключе
v_PKAttributeLBound:=array_lower(v_PKAttributeList,C_PKAttributeList_NDims);
v_PKAttributeUBound:=array_upper(v_PKAttributeList,C_PKAttributeList_NDims);
v_PKAttributeIndx:=v_PKAttributeLBound;
WHILE v_PKAttributeIndx <= v_PKAttributeUBound
AND a_AttributeNo<>v_PKAttributeList[v_PKAttributeIndx]
LOOP
v_PKAttributeIndx:=v_PKAttributeIndx+1;
ENDLOOP;
IF v_PKAttributeIndx<=v_PKAttributeUBound THEN
rs_ColumnPKNo:=v_PKAttributeIndx;
ENDIF;
ENDIF;
RETURN QUERY SELECT rs_isAttributePK,rs_PKeyName,rs_ColumnPKNo;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Attribute_PKFeatures(a_TableOID OID,a_AttributeNo SMALLINT) IS'Возвращает признак присутствия колонки в первичном ключе и порядковый номер в списке колонок первичного ключа';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Attribute_PKFeatures((SELECTOIDFROM pg_class WHERE relname='street'),3::SMALLINT);
Create function admtf_Attribute_FKFeatures
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Attribute_FKFeatures (a_TableOID OID,a_AttributeNo SMALLINT);
/****************************************************************************//* Функция возвращает признак присутсвия колонки во внешнем ключе. *//* Если колонка присутствует, то возвращается и ее порядковый номер в списке *//* колонок внешнего ключа. *//****************************************************************************//****************************************************************************/CREATEORREPLACEFUNCTION admtf_Attribute_FKFeatures
(a_TableOID OID, /* ИД таблицы */
a_AttributeNo SMALLINT/* Порядковый номер атрибута в таблице*/
)
RETURNSTABLE (rs_isAttributeFK BOOLEAN,rs_FKeyName name[],rs_ColumnFKNo SMALLINT[],rs_FKTableName name[],rs_FKTableColumnName name[]) AS
$BODY$
DECLARE C_FKAttributeList_NDims CONSTANTINTEGER:=1;
/* Размерность массива атрибутов внешнего ключа*/
v_FKAttributeList SMALLINT[];
/* Список номеров атрибутов в составе внешнего ключа*/
v_RefAttributeList SMALLINT[];
/* Список номеров атрибутов в таблице, *//* на которую ссылается внешний ключ*/
v_FKAttributeIndx INTEGER;
/* Текущий индекс масссива атрибутов внешнего ключа*/
v_RefAttributeListIndx INTEGER;
/* Текущий индекс масссива атрибутов таблицы, *//* на которую ссылается внешний ключ*/
v_FKAttributeLBound INTEGER;
/* Нижний индекс масссива атрибутов внешнего ключа*/
v_FKAttributeUBound INTEGER;
/* Верхний индекс масссива атрибутов внешнего ключа*/
v_FKConstraintIndx INTEGER;
/* Текущий индекс ограничения внешнего ключа*/
v_FKeyName name;
/* Название ограничения таблицы, *//* в котором определен внешний ключ*/
v_FKTableName name;
/* Название таблицы, на которую ссылается внешний ключ*/
v_FKTableColumnName name;
/* Название атрибута в таблице, *//* на которую ссылается внешний ключ*/
v_RefAttributeNo SMALLINT;
/* Порядковый номер атрибута в таблице, *//* на которую ссылается внешний ключ*/
v_Constraint pg_constraint%ROWTYPE;
/* Запись Системной таблицы описания *//* ограничений (CONSTRANT) */--****************************************************************************************************** BEGIN
rs_isAttributeFK:=false;
rs_ColumnFKNo:=NULL;
v_FKConstraintIndx:=0;
FOR v_Constraint IN SELECT * FROM pg_constraint c
WHERE c.contype='f'and c.conrelid=a_TableOID
ANDARRAY[a_AttributeNo]<@conkey
ORDERBY c.oid
LOOP
v_FKConstraintIndx:=v_FKConstraintIndx+1;
rs_isAttributeFK:=true;
v_FKeyName:=v_Constraint.conname;
v_FKAttributeList:=v_Constraint.conkey;
v_RefAttributeList:=v_Constraint.confkey;
v_FKAttributeLBound:=array_lower(v_FKAttributeList,C_FKAttributeList_NDims);
v_FKAttributeUBound:=array_upper(v_FKAttributeList,C_FKAttributeList_NDims);
v_FKAttributeIndx:=v_FKAttributeLBound;
WHILE v_FKAttributeIndx <= v_FKAttributeUBound
AND a_AttributeNo<>v_FKAttributeList[v_FKAttributeIndx]
LOOP
v_FKAttributeIndx:=v_FKAttributeIndx+1;
ENDLOOP;
rs_FKeyName[v_FKConstraintIndx]:=v_FKeyName;
rs_ColumnFKNo[v_FKConstraintIndx]:=v_FKAttributeIndx;
SELECTINTO v_FKTableName ftbl.relname
FROM pg_class ftbl WHERE ftbl.oid=v_Constraint.confrelid;
rs_FKTableName[v_FKConstraintIndx]:=v_FKTableName;
v_RefAttributeNo:=v_RefAttributeList[v_FKAttributeIndx];
v_FKTableColumnName:=NULL;
SELECTINTO v_FKTableColumnName attname
FROM pg_attribute a
WHERE a.attrelid=v_Constraint.confrelid AND a.attnum=v_RefAttributeNo;
rs_FKTableColumnName[v_FKConstraintIndx]:=v_FKTableColumnName;
ENDLOOP;
RETURN QUERY SELECT rs_isAttributeFK,rs_FKeyName,rs_ColumnFKNo,
rs_FKTableName,rs_FKTableColumnName;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Attribute_FKFeatures(a_TableOID OID,a_AttributeNo SMALLINT) IS'Возвращает признак присутсвия колонки в первичном ключе и порядковый номер в списке колонок внешнего ключа';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Attribute_FKFeatures((SELECTOIDFROM pg_class WHERE relname='street'),4::SMALLINT);
Create function admtf_Attribute_Features
Comments on the source code of the function can be found here.
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Attribute_Features (a_TableOID OID,a_AttributeNo SMALLINT);
/****************************************************************************//* Функция возвращает характеристики колонки таблицы *//****************************************************************************/CREATEORREPLACEFUNCTION admtf_Attribute_Features
(a_TableOID OID, /* ИД таблицы */
a_AttributeNo SMALLINT/* Порядковый номер атрибута в таблице*/
)
RETURNSTABLE (rsa_AttributeName name,rsa_UserTypeName VARCHAR(256),rsa_TypeName VARCHAR(256),rsa_isNotNULL BOOLEAN,rsa_isAttributePK BOOLEAN,
rsa_ColumnPKNo SMALLINT,rsa_Description Text,rsa_isAttributeFK BOOLEAN,rsa_FKeyName name[],rsa_ColumnFKNo SMALLINT[],rsa_FKTableName name[],rsa_FKTableColumnName name[]) AS
$BODY$
DECLARE
v_Return_Error Integer := 0; /* Код возврата*/--********************************************************************* BEGINSELECTINTO rsa_AttributeName,rsa_UserTypeName,rsa_TypeName,
rsa_isNotNULL,rsa_Description
attr.attname,
CASEWHENCOALESCE(typ.typbasetype,0)>0THEN typ.typname::VARCHAR(100)
ELSE''ENDAS r_UserTypeName,
FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid),
COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256) AS r_TypeName,
attr.attnotnull AS r_isNotNULL,
dsc.description AS r_Description
FROM pg_attribute attrLEFTOUTERJOIN 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 attr.attrelid =a_TableOID AND attr.attnum=a_AttributeNo;
SELECTINTO rsa_isAttributePK,rsa_ColumnPKNo rs_isAttributePK,rs_ColumnPKNo
FROM admtf_Attribute_PKFeatures(a_TableOID,a_AttributeNo);
SELECTINTO rsa_isAttributeFK,rsa_FKeyName,rsa_ColumnFKNo,rsa_FKTableName,
rsa_FKTableColumnName rs_isAttributeFK,rs_FKeyName,
rs_ColumnFKNo,rs_FKTableName,rs_FKTableColumnName
FROM admtf_Attribute_FKFeatures(a_TableOID,a_AttributeNo);
RETURN QUERY SELECT rsa_AttributeName,rsa_UserTypeName,rsa_TypeName,rsa_isNotNULL,
rsa_isAttributePK,rsa_ColumnPKNo,rsa_Description,rsa_isAttributeFK,
rsa_FKeyName,rsa_ColumnFKNo,rsa_FKTableName,rsa_FKTableColumnName;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Attribute_Features(a_TableOID OID,a_AttributeNo SMALLINT) IS'Возвращает характеристики колонки таблицы';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Attribute_Features ((SELECTOIDFROM pg_class WHERE relname='street'),2::SMALLINT);
see also
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Two .
Functions for documenting PostgreSQL databases. The end (part four) .