This is the second part of the article, which describes user functions for working with system directories: pg_class, pg_attribute, pg_constraints, etc.
This part of the article discusses functions that
return characteristics of constraints and indices .
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 .
see also
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Three
Functions for documenting PostgreSQL databases. The end (part four) .
admtf_Table_Constraintes list of database table constraints and their characteristics
The admtf_Table_Constraintes function returns a list of constraints (CONSTRAINT) of a database table and their characteristics. 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 ).
The description of a single constraint is a collection of a pg_class entry that describes it as a physical relation and a pg_constraint entry containing data about the specific characteristics of the constraint.
source code operator in the figureSELECT tbl.OID,con.conname,con.contype,con.conkey,reftbl.OID, reftbl.relname,con.confkey,con.consrc FROM pg_constraint con INNER JOIN pg_namespace nsp ON con.connamespace=nsp.oid LEFT OUTER JOIN pg_class tbl ON con.conrelid=tbl.oid LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableOID) ORDER BY con.contype DESC,con.conname;
The main data (name and type of restriction) is extracted from the pg_constraint directory entry . The characteristics of each restriction, which are presented in the form of OID tables ( conrelid , confrelid ) or arrays of atomic numbers of attributes ( conkey , confkey ) involved in the restriction, are extracted from the same catalog.
Characteristics of the restrictions function returns in the form of table names and attributes. In this case, the table names are extracted from the pg_class catalog entry by the identifier (OID), and the attribute names from the pg_attribute catalog entries by the table identifier and the attribute sequence number. Since the sequence numbers are stored in the main directory in the form of an array (list), then lists of attribute names are generated inside the function using a loop.
The function returns one special characteristic - the rule for checking the values of fields in the records of the table (CHECK constraint). This characteristic is stored as a text value in the consrc field of the pg_constraint directory.
Table 7. Result of execution of the function admtf_Table_Constraintes ('public', 'Street').
Text version of the table in the figure| Title | Type of | Attributes of the source table | External table name | Attributes of an external table | Validation rule |
|---|
| xpkstreet | p | wcrccode, localityid, streetid | | | |
| fk_street_locality | f | wcrccode, localityid | locality | wcrccode, localityid | |
| fk_street_streettype | f | streettypeacrm | streettype | streettypeacrm | |
| ck_street_streetname | c | streetname | | | ((streetname) :: text! ~ * '[az]' :: text) |
| ck_street_streettypeacrm | c | streettypeacrm | | | ((streettypeacrm) :: bpchar! ~ * '[az]' :: text) |
Version without cursor
I foresee questions and comments on the use of the cursor in the main version of the function.
I will not answer –to the taste and color of comrades there. But I will give the version of the function without a cursor. The version of the implementation of the function without using the cursor can be viewed and downloaded here .
The main difficulty is to organize the connection (JOIN) of tables by the values located in the attribute of the array type of one of them. Such arrays in this case are conkey and confkey .
SELECT c.conname,c.contype,c.conkey::SMALLINT[], GENERATE_SUBSCRIPTS(c.conkey, 1) as No FROM pg_constraint c WHERE c.conname='fk_street_locality' ORDER BY No;
To solve this, PostgrSQL contains functions that return a table of the values of pointers to array elements. In our case, the generate_subscripts function will be used. Not only does it generate a set of pointers to the position of the array passed to it as a parameter, it also turns one record containing the array into several by the number of elements in the array. Each record of such a table contains one unique value - the position of the array.
Table 8. Reproduction of the source string using generate_subscripts .| Name of the restriction | Type of | Attribute number array | Pointer to array position |
|---|
| fk_street_locality | f | {1,2} | one |
| fk_street_locality | f | {1,2} | 2 |
source code operator in the figure SELECT con.conname AS ConstraintName,con.contype::VARCHAR(2) AS ConstraintType, STRING_AGG(attr.attname, ', 'ORDER BY con.No) AS r_ConstraintKeyNames, reftbl.relname AS RefTableName, STRING_AGG(rattr.attname,', 'ORDER BY con.No) AS r_RefTableKeyNames, con.consrc AS ConstraintSource FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.contype, c.conkey::SMALLINT[],c.consrc, c.confkey::SMALLINT[], generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con INNER JOIN pg_class tbl ON con.conrelid=tbl.oid INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid AND attr.attnum=con.conkey[con.No] INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid LEFT OUTER JOIN pg_attribute rattr ON rattr.attrelid=reftbl.oid AND rattr.attnum=con.confkey[con.No] WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) GROUP BY con.conname,con.contype,reftbl.relname,con.consrc ORDER BY con.contype DESC,con.conname;
Such a table can be connected with the pg_attribute attribute catalog , extracting attribute names from it by the condition attr.attrelid = tbl.oid AND attr.attnum = con.conkey [con.No] .
It now remains to remove the extra entries by grouping the entries, and from the attribute names to create a string.
Creating a string is performed using the aggregation function STRING_AGG , in which it is necessary to specify the sort option (ORDER BY), otherwise the order of the attributes may not correspond to the order in which the attributes are declared in the index.
The execution time of both versions of the functions coincided with me. It took 20 ms to output data in the results table.
The function admtf_Table_Indexes list indexes of a database table and their characteristics
The function admtf_Table_Indexes returns a list of indexes (INDEX) of a database table and their characteristics. 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 ).
source code operator in the figure SELECT tbl.oid,inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary, inx.indkey::SMALLINT[],inx.indoption::SMALLINT[],inxam.amcanorder FROM pg_index inx INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid INNER JOIN pg_namespace inxnsp ON inxcls.relnamespace=inxnsp.oid INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableOID) ORDER BY inxam.amname, inxcls.relname;
A single index description is a collection of a pg_class entry that describes it as a physical relation and a pg_index record containing data about the specific characteristics of the index. Additionally, information about index access methods is stored in the pg_am system directory.
CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 WHEN 1 THEN ' DESC' ELSE ' ASC' END ELSE '' END;
The index uniqueness ( indisunique ) is extracted from the pg_index catalog entry , the index is built in accordance with the description of the primary key ( indisprimary ), and also the arrays of the index numbers of the table attributes, the values of which are used to construct the index ( indkey ) and attributes in the index ( indoption ).
From the catalog entry describing the access method of the pg_am index, a feature is retrieved of the suitability of the data included in the index for sorting ( amcanorder ) and the name or type of index access method ( amname ).
In other words, the amcanorder attribute indicates whether it is possible to establish the sorting order of the values included in the attribute index. If amcanorder = true , then the sort order may be specified, otherwise not. From the same figure, the meaning of the indoption array values is seen - if the right bit of the binary form contains 1B, then the value of the corresponding attribute is sorted in descending order, otherwise - in ascending order.
Lists of attribute names included in the index, as well as signs of the ordering of attribute values, are formed inside the function using a loop.
Table 9. Result of execution of the function admtf_Table_Indexes ('public', 'Street').
Text version of the table in the figure| Index name | Method | ? Unique | ? primary key | Attributes included in the index |
|---|
| xie1street | btree | f | f | wcrccode ASC, localityid ASC, streettypeacrm ASC, streetname ASC |
| xie2stree | btree | f | f | wcrccode ASC, localityid ASC, streetname ASC |
| xie3street | btree | f | f | streetname ASC |
| xie9street | btree | f | f | wcrccode ASC, localityid ASC, streetname DESC |
| xpkstreet | btree | t | t | wcrccode ASC, localityid ASC, streetid ASC |
| xts1street | gin | f | f | streettsvector |
| xts2street | gin | f | f | streettsvector |
Version without cursor
The approach to creating a version of a function without a cursor completely coincides with that already described in the previous section:
- replicating records using generate_subscripts;
- subsequent grouping of records;
- create a list of index attributes using the STRING_AGG function with the ORDER BY option.
source code operator in the figure SELECT inxcls.relname AS r_IndexName ,inxam.amname AS r_IndexType, inx.indisunique AS r_isUnique,inx.indisprimary AS r_isPrimary, STRING_AGG(attr.attname|| CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 WHEN 1 THEN ' DESC' ELSE ' ASC' END ELSE '' END, c_Delimiter ORDER BY inx.No) FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,i.indisprimary, i.indkey::SMALLINT[],i.indoption::SMALLINT[], generate_subscripts(i.indkey, 1) as No FROM pg_index i) inx INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute attr ON attr.attrelid=tbl.OID AND attr.attnum=inx.indkey[inx.No] WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary ORDER BY inxcls.relname;
The execution time of both versions of the functions coincided with me to display the data in the results table in 20 ms.
Therefore, I will no longer produce versions of functions, since Those who wish can change them to your liking or contact me. I will send a modified version for free .
See also the first , third and fourth parts of the article.
APPENDIX 1. Scripts
Create function admtf_Table_Constraintes
Comments on the source code of the function can be found here.function source code BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME); CREATE OR REPLACE FUNCTION admtf_Table_Constraintes (a_SchemaName name default 'public', a_TableName name default NULL ) RETURNS TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS $BODY$ DECLARE v_Scale INTEGER; v_ConstraintRec RECORD; v_TableOID INTEGER; v_ConstraintOID INTEGER; v_ConstraintKeyNos SMALLINT[]; v_ConstraintName name; v_ConstraintType name; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName name; v_ConstraintKeyNames TEXT; v_RefTableOID INTEGER; v_RefTableName name; v_RefTableKeyNos SMALLINT[]; v_RefTableKeyNames TEXT; v_ConstraintSource TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Creating a version of the function admtf_Table_Constraintes without a cursor
Comments on the source code of the function can be found here.function source code BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME); CREATE OR REPLACE FUNCTION admtf_Table_Constraintes (a_SchemaName name default 'public', a_TableName name default NULL ) RETURNS TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS $BODY$ DECLARE v_Scale INTEGER; v_ConstraintRec RECORD; v_TableOID INTEGER; v_ConstraintOID INTEGER; v_ConstraintKeyNos SMALLINT[]; v_ConstraintName name; v_ConstraintType name; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName name; v_ConstraintKeyNames TEXT; v_RefTableOID INTEGER; v_RefTableName name; v_RefTableKeyNos SMALLINT[]; v_RefTableKeyNames TEXT; v_ConstraintSource TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Create function admtf_Table_Indexes
Comments on the source code of the function can be found here.function source code BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME); CREATE OR REPLACE FUNCTION admtf_Table_Indexes (a_SchemaName NAME default 'public', a_TableName NAME default NULL ) RETURNS TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS $BODY$ DECLARE c_IndexKind CONSTANT CHAR:='i'; v_IndexRec RECORD; v_Scale INTEGER; v_TableOID INTEGER; v_IndexOID INTEGER; v_IndexKeyNos SMALLINT[]; v_IndexName NAME; v_IndexAMName NAME; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName NAME; v_IndexKeyNames TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Creating a version of the function admtf_Table_Indexes without a cursor
Comments on the source code of the function can be found here.function source code BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME); CREATE OR REPLACE FUNCTION admtf_Table_Indexes (a_SchemaName NAME default 'public', a_TableName NAME default NULL ) RETURNS TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS $BODY$ DECLARE c_IndexKind CONSTANT CHAR:='i'; c_Delimiter CONSTANT VARCHAR(2):=', ';
see also
Functions for documenting PostgreSQL databases. Part One ;Functions for documenting PostgreSQL databases. .Functions for documenting PostgreSQL databases. The end (part four) .