Functions for documenting PostgreSQL databases. Part two
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 .
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 ).
Table 7. Result of execution of the function admtf_Table_Constraintes ('public', 'Street').
Table 8. Reproduction of the source string using generate_subscripts .
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 ).
Table 9. Result of execution of the function admtf_Table_Indexes ('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.
Comments on the source code of the function can be found here.
Comments on the source code of the function can be found here.
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Three .
Functions for documenting PostgreSQL databases. The end (part four) .
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 figure
SELECT tbl.OID,con.conname,con.contype,con.conkey,reftbl.OID,
reftbl.relname,con.confkey,con.consrc
FROM pg_constraint con
INNERJOIN pg_namespace nsp ON con.connamespace=nsp.oid
LEFTOUTERJOIN pg_class tbl ON con.conrelid=tbl.oid
LEFTOUTERJOIN pg_class reftbl ON con.confrelid=reftbl.oid
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
ORDERBY 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 .
The characteristics of the restrictions are returned by the function in the form of table and attribute names. 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 feature is stored as a text value in a field consrc , directory pg_constraint .
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 !~* '[a-z]'::text) | ||
| ck_street_streettypeacrm | c | streettypeacrm | ((streettypeacrm)::bpchar !~* '[a-z]'::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) asNoFROM pg_constraint c
WHERE c.conname='fk_street_locality'ORDERBYNo;
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, ', 'ORDERBY con.No) AS r_ConstraintKeyNames,
reftbl.relname AS RefTableName,
STRING_AGG(rattr.attname,', 'ORDERBY 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) asNoFROM pg_constraint c) con
INNERJOIN pg_class tbl ON con.conrelid=tbl.oid
INNERJOIN pg_attribute attrON attr.attrelid=tbl.oid
AND attr.attnum=con.conkey[con.No]
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
LEFTOUTERJOIN pg_class reftbl ON con.confrelid=reftbl.oid
LEFTOUTERJOIN pg_attribute rattr ON rattr.attrelid=reftbl.oid
AND rattr.attnum=con.confkey[con.No]
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
GROUPBY con.conname,con.contype,reftbl.relname,con.consrc
ORDERBY 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
INNERJOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNERJOIN pg_namespace inxnsp ON inxcls.relnamespace=inxnsp.oid
INNERJOIN pg_am inxam ON inxcls.relam=inxam.oid
INNERJOIN pg_class tbl ON inx.indrelid=tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableOID)
ORDERBY 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' ENDELSE''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
| Название индекса | Метод | ? Уникальный | ? первичный ключ | Атрибуты, входящие в индекс |
|---|---|---|---|---|
| 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 WHENtrueTHENCASE inx.indoption[inx.No] & 1WHEN1THEN' DESC'ELSE' ASC'ENDELSE''END,
c_Delimiter ORDERBY inx.No)
FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,i.indisprimary,
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_am inxam ON inxcls.relam=inxam.oid
INNERJOIN pg_class tbl ON inx.indrelid=tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNERJOIN pg_attribute attrON attr.attrelid=tbl.OID
AND attr.attnum=inx.indkey[inx.No]
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
GROUPBY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
ORDERBY 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
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************//* Функция возвращает список ограничений таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Constraintes
(a_SchemaName namedefault'public', /* название схемы базы данных */
a_TableName namedefaultNULL/* Название таблицы */
)
RETURNSTABLE (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; /* OID таблицы*/
v_ConstraintOID INTEGER; /* OID ограничения*/
v_ConstraintKeyNos SMALLINT[]; /* */
v_ConstraintName name; /* Название ограничения */
v_ConstraintType name; /* Буквенное обозначение типа ограничения */
v_isUnique BOOLEAN; /* Признак уникальности ограничения*/
v_isPrimary BOOLEAN; /* Признак того что индекс представляет Primary KEY таблицы*/
v_AttributeNum INTEGER; /* Порядковый номер аттрибута*/
v_AttributeName name; /* Наименование аттрибута*/
v_ConstraintKeyNames TEXT; /* Строка со списком аттрибутов ограничения*/
v_RefTableOID INTEGER; /* OID таблицы, на которую ссылается ограничение */
v_RefTableName name;/* Название таблицы, на которую ссылается ограничение */
v_RefTableKeyNos SMALLINT[]; /* */
v_RefTableKeyNames TEXT; /* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
v_ConstraintSource TEXT; /* Строка с описанием уловия CHECK*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--****************************************************************************************************** BEGINFOR v_ConstraintRec INSELECT con.oid AS ConstraintOID ,tbl.OID AS TableOID,
con.conname AS ConstraintName,
con.contype AS ConstraintType,
con.conkey AS ConstraintKeyNos,
reftbl.OID AS RefTableOID,
reftbl.relname AS RefTableName,
con.confkey AS RefTableKeyNos,
con.consrc AS ConstraintSource
FROM pg_constraint con
INNERJOIN pg_class tbl ON con.conrelid=tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
LEFTOUTERJOIN pg_class reftbl ON con.confrelid=reftbl.oid
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
ORDERBY con.contype DESC,con.conname
LOOP
v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
v_TableOID:=v_ConstraintRec.TableOID;
v_ConstraintName:=v_ConstraintRec.ConstraintName;
v_ConstraintType:=v_ConstraintRec.ConstraintType;
v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
v_RefTableOID:=v_ConstraintRec.RefTableOID;
v_RefTableName:=v_ConstraintRec.RefTableName;
v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
v_ConstraintKeyNames:='';
FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
LOOP
SELECTINTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attrWHERE attr.attrelid=v_TableOID
AND attr.attnum=v_AttributeNum;
v_ConstraintKeyNames:=v_ConstraintKeyNames||
CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
ENDLOOP;
v_RefTableKeyNames:='';
IF v_RefTableKeyNos IS NOT NULL THEN
FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
LOOP
SELECTINTO v_AttributeName
attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attrWHERE attr.attrelid=v_RefTableOID AND attr.attnum=v_AttributeNum;
v_RefTableKeyNames:=v_RefTableKeyNames||
CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
ENDLOOP;
ENDIF;
RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
v_RefTableName,v_RefTableKeyNames,
v_ConstraintSource;
ENDLOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Constraintes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************//* Функция возвращает список ограничений таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Constraintes
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_TableName VARCHAR(256) defaultNULL/* Название таблицы */
)
RETURNSTABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE--******************************************************************************************************BEGINRETURNQUERYSELECT tc.r_ConstraintName::VARCHAR(256),
tc.r_ConstraintType::VARCHAR(256),tc.r_ConstraintKeyNames::TEXT,
tc.r_RefTableName::VARCHAR(256),tc.r_RefTableKeyNames::TEXT,
tc.r_ConstraintSource::TEXTFROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);
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
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************//* Функция возвращает список ограничений таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Constraintes
(a_SchemaName namedefault'public', /* название схемы базы данных */
a_TableName namedefaultNULL/* Название таблицы */
)
RETURNSTABLE (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; /* OID таблицы*/
v_ConstraintOID INTEGER; /* OID ограничения*/
v_ConstraintKeyNos SMALLINT[]; /* */
v_ConstraintName name; /* Название ограничения */
v_ConstraintType name; /* Буквенное обозначение типа ограничения */
v_isUnique BOOLEAN; /* Признак уникальности ограничения*/
v_isPrimary BOOLEAN;/* Признак того что индекс представляет Primary KEY таблицы*/
v_AttributeNum INTEGER; /* Порядковый номер аттрибута*/
v_AttributeName name; /* Наименование аттрибута*/
v_ConstraintKeyNames TEXT; /* Строка со списком аттрибутов ограничения*/
v_RefTableOID INTEGER; /* OID таблицы, на которую ссылается ограничение */
v_RefTableName name;/* Название таблицы, на которую ссылается ограничение */
v_RefTableKeyNos SMALLINT[]; /* */
v_RefTableKeyNames TEXT;/* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
v_ConstraintSource TEXT; /* Строка с описанием уловия CHECK*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--****************************************************************************************************** BEGINFOR v_ConstraintRec INSELECT con.oid AS ConstraintOID ,
tbl.OID AS TableOID,
con.conname AS ConstraintName,
con.contype AS ConstraintType,
con.conkey AS ConstraintKeyNos,
reftbl.OID AS RefTableOID,
reftbl.relname AS RefTableName,
con.confkey AS RefTableKeyNos,
con.consrc AS ConstraintSource
FROM pg_constraint con
INNERJOIN pg_class tbl ON con.conrelid=tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
LEFTOUTERJOIN pg_class reftbl ON con.confrelid=reftbl.oid
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
ANDLOWER(tbl.relname)=LOWER(a_TableName)
ORDERBY con.contype DESC,con.conname
LOOP
v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
v_TableOID:=v_ConstraintRec.TableOID;
v_ConstraintName:=v_ConstraintRec.ConstraintName;
v_ConstraintType:=v_ConstraintRec.ConstraintType;
v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
v_RefTableOID:=v_ConstraintRec.RefTableOID;
v_RefTableName:=v_ConstraintRec.RefTableName;
v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
v_ConstraintKeyNames:='';
FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
LOOP
SELECTINTO v_AttributeName
attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attrWHERE attr.attrelid=v_TableOID
AND attr.attnum=v_AttributeNum;
v_ConstraintKeyNames:=v_ConstraintKeyNames||
CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
ENDLOOP;
v_RefTableKeyNames:='';
IF v_RefTableKeyNos IS NOT NULL THEN
FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
LOOP
SELECTINTO v_AttributeName
attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attrWHERE attr.attrelid=v_RefTableOID
AND attr.attnum=v_AttributeNum;
v_RefTableKeyNames:=v_RefTableKeyNames||
CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
||v_AttributeName;
ENDLOOP;
ENDIF;
RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
v_RefTableName,v_RefTableKeyNames,
v_ConstraintSource;
ENDLOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Constraintes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************//* Функция возвращает список ограничений таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Constraintes
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_TableName VARCHAR(256) defaultNULL/* Название таблицы */
)
RETURNSTABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
$BODY$
DECLARE--******************************************************************************************************BEGINRETURNQUERYSELECT tc.r_ConstraintName::VARCHAR(256),
tc.r_ConstraintType::VARCHAR(256),
tc.r_ConstraintKeyNames::TEXT,
tc.r_RefTableName::VARCHAR(256),
tc.r_RefTableKeyNames::TEXT,
tc.r_ConstraintSource::TEXTFROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS'Возвращает возвращает ограничений таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);
Create function admtf_Table_Indexes
Comments on the source code of the function can be found here.
function source code
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************//* Функция возвращает список индексов таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Indexes
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_TableName NAMEdefaultNULL/* Название таблицы */
)
RETURNSTABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
$BODY$
DECLARE
c_IndexKind CONSTANTCHAR:='i';
v_IndexRec RECORD; /* Данные об индексе*/
v_Scale INTEGER; /* Масштаб колонки */
v_TableOID INTEGER; /* OID таблицы*/
v_IndexOID INTEGER; /* OID индекса*/
v_IndexKeyNos SMALLINT[]; /* */
v_IndexName NAME; /* Название индекса */
v_IndexAMName NAME; /* Наименование типа индекса (метода доступа) */
v_isUnique BOOLEAN; /* Признак уникальности индекса*/
v_isPrimary BOOLEAN; /* Признак того что индекс представляет Primary KEY таблицы*/
v_AttributeNum INTEGER; /* Порядковый номер атрибута*/
v_AttributeName NAME; /* Наименование атрибута*/
v_IndexKeyNames TEXT; /* Строка со списком атрибутов индекса*/
c_Delimiter CONSTANT VARCHAR(2):=',';
--****************************************************************************************************** BEGINFOR v_IndexRec INSELECT inxcls.oid AS IndexOID,tbl.oid AS TableOID,
inxcls.relname AS IndexName,inxam.amname AS IndexAMName,
inx.indisunique AS isUnique,inx.indisprimary isPrimary,
inx.indkey::SMALLINT[] AS IndexKeyNos
FROM pg_index inx
INNERJOIN pg_class inxcls ON inx.indexrelid=inxcls.oid
INNERJOIN pg_am inxam ON inxcls.relam=inxam.oid
INNERJOIN pg_class tbl ON inx.indrelid=tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
AND inxcls.relkind=c_IndexKind
AND tbl.relname=LOWER(a_TableName)
ORDERBY inxam.amname, inxcls.relname LOOP
v_IndexOID:=v_IndexRec.IndexOID;
v_TableOID:=v_IndexRec.TableOID;
v_IndexName:=v_IndexRec.IndexName;
v_IndexAMName:=v_IndexRec.IndexAMName;
v_isUnique:=v_IndexRec.isUnique;
v_isPrimary:=v_IndexRec.isPrimary;
v_IndexKeyNos:=v_IndexRec.IndexKeyNos;
v_IndexKeyNames:='';
FOREACH v_AttributeNum IN ARRAY v_IndexKeyNos
LOOP
SELECTINTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
FROM pg_attribute attrWHERE attr.attrelid=v_TableOID AND attr.attnum=v_AttributeNum;
v_IndexKeyNames:=v_IndexKeyNames||
CASE WHEN v_IndexKeyNames='' THEN ''
ELSE c_Delimiter||' ' END ||
v_AttributeName;
ENDLOOP;
RETURN QUERY SELECT v_IndexName,v_IndexAMName,v_isUnique,
v_isPrimary,v_IndexKeyNames;
ENDLOOP;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Indexes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************//* Функция возвращает список индексов таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Indexes
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных */
a_TableName VARCHAR(256) defaultNULL/* Название таблицы */
)
RETURNSTABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
$BODY$
DECLARE--****************************************************************************************************** BEGINRETURNQUERYSELECT ti.r_IndexName::VARCHAR(256),
ti.r_IndexType::VARCHAR(256),
ti.r_isUnique::BOOLEAN,
ti.r_isPrimary::BOOLEAN,
ti.r_IndexKeyNames::TEXTFROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));
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
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME);
/********************************************************************************************************//* Функция возвращает список индексов таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Indexes
(a_SchemaName NAMEdefault'public', /* название схемы базы данных */
a_TableName NAMEdefaultNULL/* Название таблицы */
)
RETURNSTABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
$BODY$
DECLARE
c_IndexKind CONSTANTCHAR:='i';
c_Delimiter CONSTANT VARCHAR(2):=', ';
--****************************************************************************************************** BEGINRETURNQUERYSELECT inxcls.relname AS r_IndexName,
inxam.amname AS r_IndexType,
inx.indisunique AS r_isUnique,
inx.indisprimary r_isPrimary,
STRING_AGG(attr.attname||CASE inxam.amcanorder WHENtrueTHENCASE inx.indoption[inx.No] & 1WHEN1THEN' DESC'ELSE' ASC'ENDELSE''END,
c_Delimiter ORDERBY inx.No)
FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,
i.indisprimary,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_am inxam ON inxcls.relam=inxam.oid
INNERJOIN pg_class tbl ON inx.indrelid=tbl.oid
INNERJOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid
INNERJOIN pg_attribute attrON attr.attrelid=tbl.OID
AND attr.attnum=inx.indkey[inx.No]
WHERELOWER(nsp.nspname)=LOWER(a_SchemaName)
AND inxcls.relkind=c_IndexKind
AND tbl.relname=LOWER(a_TableName)
GROUPBY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
ORDERBY inxcls.relname;
RETURN;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
BEGINTRANSACTION;
DROPFUNCTIONIFEXISTS admtf_Table_Indexes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
/********************************************************************************************************//* Функция возвращает список индексов таблицы *//********************************************************************************************************/CREATEORREPLACEFUNCTION admtf_Table_Indexes
(a_SchemaName VARCHAR(256) default'public', /* название схемы базы данных*/
a_TableName VARCHAR(256) defaultNULL/* Название таблицы */
)
RETURNSTABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
$BODY$
DECLARE--****************************************************************************************************** BEGINRETURNQUERYSELECT ti.r_IndexName::VARCHAR(256),
ti.r_IndexType::VARCHAR(256),
ti.r_isUnique::BOOLEAN,
ti.r_isPrimary::BOOLEAN,
ti.r_IndexKeyNames::TEXTFROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
END
$BODY$
LANGUAGE plpgsql;
COMMENTONFUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS'Возвращает список индексов таблицы ';
--ROLLBACK TRANSACTION;COMMITTRANSACTION;
SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));
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) .