During the work with PostgreSQL, more than a hundred functions have been accumulated for working with system directories: pg_class, pg_attribute, pg_constraints, etc.
What to do with them? They are used relatively rarely. Include in any project? Krasnoyarsk customer will not pay for such “nonsense”. And yet, what if they are useful to someone other than the author. And I decided to lay them out like read books in an open closet for those who want it.
Someone wants to use them in their work. And someone will be interested in a different experience from working with system catalogs.
But in order not to turn the publication into a boring enumeration, it is not known why the functions created, decided to dwell on those that can be combined with a common goal. Therefore, the functions that are used to issue an extended list of characteristics of an arbitrary database table are selected.
The advanced list of characteristics of the database table is returned by the function
admtf_Table_ComplexFeatures , which in this article will be called the head function. Thus, the article will be limited to consideration of the functions that are called in the process of performing the head function.
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 Two ;
Functions for documenting PostgreSQL databases. Part Three
Functions for documenting PostgreSQL databases. The end (part four) .
What are the advanced features in question?
In order to get an idea of what is meant by the extended characteristics of the database table, we begin by considering the following list of characteristics. The list contains the characteristics of the Street database table, returned by the function
admtf_Table_ComplexFeatures ( 'public' , 'street' ) .
The table below contains an abbreviated list of the characteristics of the Street table. A full set of characteristics of this table is given in the Additional Materials of Appendix 2.
Table 1. Extended specifications of the Street table.
Text version of the table in the figure| Category | No | Title | Comment | type of | Base type | ? not NULL |
|---|
| tbl | 0 | street | List of streets in settlements | | | |
| att | one | wcrccode | Country code | wcrccode | smallint | t |
| att | 2 | localityid | ID of the settlement | localityid | integer | t |
| att | 3 | streetid | ID street of the village | streetid | smallint | t |
| att | four | streettypeacrm | Street type acronym | streettypeacrm | character (8) | f |
| att | five | streetname | Street name | streettypeacrm | varchar (150) | t |
| pk | 0 | xpkstreet | Primary key table street | | | |
| pkatt | one | wcrccode | Country code | wcrccode | smallint | t |
| fk01 | one | fk_street_locality | Foreign key table | | | |
| fk02 | 2 | fk_street_streettype | Foreign key table | | | |
| idx01 | one | xie1street | Index on the type and name of the street settlements | | | |
| idx02 | 2 | xie2street | Index on the name of the street settlements | | | |
| idx03 | 3 | xie3street | Index of street names of all settlements | | | |
| idx04 | four | xpkstreet | Index unique (primary key) of the street table | | | |
Looking at the dislike of this set of letters and numbers, you can see that we are talking about the usual characteristics of a database table:
- Table name;
- List of table attributes and their types;
- The primary key and the list of foreign keys of the table together with the attributes of the table that comprise them;
- List indexes table.
The uniqueness of each record from the list of characteristics is provided by the values of the “category” fields and the sequence number (“No.”) of the characteristics.
Table 2. Categories of characteristics of tables.
Text version of the table in the figure| Acronym | Purpose |
|---|
| tb | Table specifications |
| att | Table attribute characteristics |
| seq | Sequence characteristics |
| pk | Primary Key Characteristics |
| pkAtt | Characteristics of the primary key attribute |
| fk99 | Foreign key characteristics |
| fk99att | Foreign Key Attribute Characteristics |
| fk99rtbl | Characteristics of the table referenced by the foreign key |
| fk99ratt | Attribute characteristics of the table referenced by the foreign key |
| Idx99 | Index Features |
| Idx99att | Index Attribute Characteristics |
| Inhtbl99 | Characteristics of the generated table |
Category value is necessary in order to distinguish different groups of characteristics from each other. A sequence number, in order to distinguish the characteristics within the group.
Several foreign keys (FOREIGN KEY) and indexes can be declared in the database table. Therefore, the category value for these characteristics and their descendants contains a sequence number. For example, an entry with the key “Category” = idx02att and “No.” = 1 indicates the first attribute of the 2nd index.
In the above list of categories, the location of the sequence number is indicated by '99'.
Remark 1
source code operator in the figureSELECT * FROM admtf_Table_ComplexFeatures('pg_catalog','pg_class');
The article provides examples of the characteristics of the tables, which are briefly described in the auxiliary diagram created specifically to demonstrate the capabilities of the functions. But the reader, having created this or that function in his database, can use the names of his diagrams and tables as parameters. Moreover, as a parameter, for example, the pg_class directory can be used, although in this case a limited number of characteristics are displayed .
The end of the remark.
Head function structure
Fig. 1. Functions that the head function calls.Table 3. Assignment of functions.
Text version of the table in the figure| No | Title | Purpose |
|---|
| one | admtf_Table_Features | The function returns a list of database table characteristics. |
| 2 | admtf_Table_Attributes | The function returns a list of database table attributes and their characteristics. |
| 3 | admtf_Table_Constraintes | The function returns a list of database table constraints and their characteristics. |
| four | admtf_Table_Indexes | The function returns a list of database table indexes and their characteristics. |
| five | admtf_Table_InheritanceChildrens | The function returns a list of tables generated (IHERITS) from the original database table. |
| 6 | admtf_Table_Sequences | The function returns a list of sequences (SEQUENCE) on which the table depends. |
| 7 | admtf_PrimaryKey_ComplexFeatures | The function returns a complete (extended) list of the primary key characteristics (PRIMARY KEY) of the database table. |
| eight | admtf_ForeignKey_ComplexFeatures | The function returns a complete (extended) list of foreign key characteristics (FOREIGN KEY) of the database table. |
| 9 | admtf_Index_ComplexFeatures | The function returns a complete (extended) list of database table index characteristics. |
| ten | admtf_Table_ComplexFeatures | The function returns a complete (extended) list of characteristics of the database table. |
Remark 2.
The function descriptions will be located in the order listed above. The reason is that the article will have to be divided into several parts. And the functions located in this order can be used regardless of the fact that some of them will be described only in the following parts of the publication.
The end of the remark.
Function admtf_Table_Features list of database table characteristics
The function admtf_Table_Features returns a list of the characteristics of the actual 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 function extracts the main data from the pg_class directory entry , which contains, in addition to records about tables, records about sequences, views, materialized views, and composite types. Therefore, the condition relkind = 'r' is used to select tables.
SELECT tbl.relname,dsc.description,tbl.relnatts::INTEGER,tbl.relchecks::INTEGER, tbl.relhaspkey,tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::INTEGER FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid LEFT OUTER JOIN pg_description dsc ON tbl.oid=dsc.objoid AND dsc.objsubid=0 WHERE LOWER( nspc.nspname)=LOWER(a_SchemaName) AND tbl.relkind='r' AND LOWER(tbl.relname) =LOWER(a_TableName);
Additionally, the function accesses the data of the pg_namespace and pg_description directories . The first one contains the names of database schemas, and the second one contains comments to all database objects.
It is important to pay attention to the condition objsubid = 0 . It defines the comment to the table, since the value of the objoid field is the same for both the table and its attributes. The comment to the attribute of the table is contained in the record in which objsubid matches the number of this attribute.
Table 4. Result of execution of the function admtf_Table_Features ('public', 'Street').| Title | Comment | Number of attributes | Number of CHECK restrictions | ? is there a primary key | ? indexes declared | ? are there any descendants | Number of records in the table |
|---|
| street | List of streets in settlements | 22 | 0 | t | t | f | 20150 |
Note 3
Note the number of attributes of the street table. It is significantly different from the number of attributes specified in the auxiliary scheme.
Table 5. Additional attributes of the Street table.
| attname | atttypid | attnum | Note |
|---|
| cmin | 29 | -four | System attribute |
| xmin | 28 | -3 | System attribute |
| ctid | 27 | -one | System attribute |
| wcrccode | 795369 | one | Valid attribute |
| localityid | 795352 | 2 | Valid attribute |
| streetid | 795364 | 3 | Valid attribute |
| streettypeacrm | 1919168 | four | Valid attribute |
| streetname | 1043 | five | Valid attribute |
| ........ pg.dropped.6 ........ | 0 | 6 | Remote attribute |
| ........ pg.dropped.7 ........ | 0 | 7 | Remote attribute |
The fact is that PostgreSQL, in addition to the basic attributes, additionally takes into account several system attributes, and even remote attributes.
End note
The function admtf_Table_Attributes a list of database table attributes and their characteristics
The function admtf_Table_Attributes returns a list of attributes 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 main data function extracts from the directory entry pg_attribute and pg_type . The first contains entries with data about the attributes of tables, views, materialized views, composite types, and even functions. The second is about the characteristics of attribute types.
Perhaps some explanation is required of the way in which the function defines the user and base types.
A table attribute is declared with a user-defined type if in the corresponding directory entry pg_type the field typbasetype is greater than 0. Otherwise, the attribute is of the base type. Therefore, the pg_type directory participates twice in the FROM clause. In the first directory entry, the presence of a user-defined type is determined; if not defined ( typbasetype = 0 ), then the value of the base type is formed from this entry. Otherwise, the base type is determined from the entry for which btyp.OID = typ.typbasetype.
Directly the string with the base type is formed using the function of the system catalog FORMAT_TYPE (type_oid, typemod) . The first parameter of which is the base type OID entries. The second parameter is the value of the modifier for types that contain size. For example, VARCHAR (100) or NUMERIC (4,2), DECIMAL (4,2). The value of the typemod parameter is taken from typ.typtypmod if the attribute has a custom type, otherwise from attr.atttypmod , i.e. directly from the attribute record.
source code operator in the figure SELECT attr.attnum, attr.attname::VARCHAR(100), CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256), attr.attnotnull, dsc.description FROM pg_attribute attr INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE LOWER( nspc.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname) =LOWER(a_TableName) AND tbl.relkind='r' AND attr.attnum>0 AND attr.atttypID>0 ORDER BY tbl.relname,attr.attnum;
Additionally, the function accesses the data of the pg_class , pg_namespace and pg_description directories . The first and second directories are used to search for attributes by schema and database table names.
The third directory is used to retrieve a comment on a table attribute.
The comment to the attribute of the table is in the record where the dsc.objoid contains the OID of the source table, and dsc.objsubid is the ordinal number of the attribute in the table, i.e. attr.attnum .
In order for the function not to return system and deleted attributes, the clause WHERE is set to attr.attnum> 0 AND attr.atttypID> 0 .
Table 6. Result of the execution of the function admtf_Table_Attributes ('public', 'Street').| No | Title | Custom type | Base type | ? not NULL | Comment |
|---|
| one | wcrccode | wcrccode | smallint | t | Country code |
| 2 | localityid | localityid | integer | t | ID of the settlement |
| 3 | streetid | streetid | smallint | t | ID street of the village |
| four | streettypeacrm | streettypeacrm | character (8) | f | Street type acronym |
| five | streetname | | varchar (150) | t | Street name of the settlement |
Function version using the regclass alias for the oid type
Object identifiers (OIDs) in PostgreSQL have the same type OID, which is currently implemented as an unsigned four-byte integer. But due to the presence of aliases of this type, an integer can be represented as an object name. And vice versa - convert the object name to an integer of type OID.
As an example, take a look at the following SELECT statement . It unusually retrieves the names of the attribute table and the names of its types — instead of referring to the corresponding catalog fields with the names of these characteristics, use:
- attrelid :: regclass (attrelid :: regclass: NAME) ,
- atttypid :: regtype (atttypid :: regtype: NAME)
- typbasetype :: regtype (typbasetype :: regtype: NAME) .
SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype, typ.typbasetype::regtype,attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=('public'||'.'||'Street')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum;
Below is the result of this query.
In the list of output values of the SELECT statement before conversion using aliases of the OID type, all values except the attribute name are numeric, but the result is the names of the table and attribute types. The types of output values can be seen in the second row of the table header.
In addition, in the WHERE clause of the statement is the condition attr.attrelid = ('public' || '.' || 'Street') :: regclass , on the left side of which is a numeric value, and in the right part - a string value, which is converted to a numeric using the alias regclass .
source code operator in the figure SELECT attr.attnum, attr.attname::VARCHAR(100), CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256), attr.attnotnull, dsc.description FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE attr.attrelid=( a_SchemaName ||'.'|| a_TableName)::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum;
Using the regclass alias, you can remove the connection from two directories from the main statement. But such an improvement had almost no effect on the performance of the function - in both versions, the function is executed in 11 ms. Perhaps due to the fact that there are few attributes in the test table.
Remark 4A serious disadvantage of the condition in the form of attr.attrelid = (a_SchemaName || '.' || a_TableName) :: regclass is manifested in the case when there is a schema and / or table with an unusual name in the database. For example, “My Scheme” and / or “My Table” . Such values need to be transferred, enclosed in double quotes, or use the QUOTE_IDENT function, otherwise the function will end with a system error.
source code operator in the figure SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype, attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=(' '||'.'||' ')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype, attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=('" "'||'.'||'" "')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum;
Therefore, I prefer to use conditions in the form
LOWER (nspc.nspname) = LOWER (a_SchemaName) AND LOWER (tbl.relname) = LOWER (a_TableName) , which does not lead to system errors.
End noteAPPENDIX 1. Scripts
Create function admtf_Table_Features
Comments on the source code of the function can be found here.function source code BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Features (a_SchemaName NAME,a_TableName NAME); CREATE OR REPLACE FUNCTION admtf_Table_Features (a_SchemaName NAME default 'public', a_TableName NAME default NULL ) RETURNS TABLE (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 CONSTANT CHAR:='r'; v_TableOID OID; v_TableName NAME; v_TableDescription TEXT; v_TableNumberOfRowCalc INTEGER;
Create function admtf_Table_Attributes
Comments on the source code of the function can be found here.function source code BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME); CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName NAME default 'public', a_TableName NAME default NULL ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER;
Creating the function admtf_Table_Attributes using the regclass alias
Comments on the source code of the function can be found here.function source code BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME); CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName NAME default 'public', a_TableName NAME default NULL ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER;
APPENDIX 2. Additional materials
Auxiliary database schema
- COUNTRY - Classifier of countries of the world - OKSM (All-Russian Classifier of Countries of the World);
- HOUSEADDR - List of house numbers on the streets of settlements;
- LCLTYTYPE - Directory of types of settlements;
- LOCALITY - List of locations;
- STREET - List of streets in the settlements;
- STREETTYPE - Directory of street types;
- TERRITORY - List of territories (republics, territories, regions, districts, etc.);
- TERRITORYTYPE - Directory types of territories.
Extended specifications of the Street table
.1. Street ().
| Category | No | Title | Comment | type of | | ? not NULL |
|---|
| tbl | 0 | street | | | | |
| att | one | wcrccode | Country code | wcrccode | smallint | t |
| att | 2 | localityid | ID of the settlement | localityid | integer | t |
| att | 3 | streetid | | streetid | smallint | t |
| att | four | streettypeacrm | | streettypeacrm | character(8) | f |
| att | five | streetname | | streettypeacrm | varchar(150) | t |
| pk | 0 | xpkstreet | Primary key table street | | | |
| pkatt | one | wcrccode | Country code | wcrccode | smallint | t |
| pkatt | 2 | localityid | ID of the settlement | localityid | integer | t |
| pkatt | 3 | streetid | | streetid | smallint | t |
| fk01 | one | fk_street_locality | | | | |
| fk01att | one | wcrccode | Country code | wcrccode | smallint | t |
| fk01att | 2 | localityid | ID of the settlement | localityid | integer | t |
| fk01rtbl | 0 | locality | | | | |
| fk01ratt | one | wcrccode | Country code | wcrccode | smallint | t |
| fk01ratt | 2 | localityid | ID of the settlement | localityid | integer | t |
| fk02 | 2 | fk_street_streettype | | | | |
| fk02att | one | streettypeacrm | | streettypeacrm | character(8) | f |
| fk02rtbl | 0 | streettype | | | | |
| fk02ratt | one | streettypeacrm | | streettypeacrm | character(8) | t |
| idx01 | one | xie1street | | | | |
| idx01att | one | wcrccode | Country code | wcrccode | smallint | t |
| idx01att | 2 | localityid | ID of the settlement | localityid | integer | t |
| idx01att | 3 | streettypeacrm | | streettypeacrm | character(8) | f |
| idx01att | four | streetname | | | varchar(150) | t |
| idx02 | 2 | xie2street | | | | |
| idx02att | one | wcrccode | Country code | wcrccode | smallin | t |
| idx02att | 2 | localityid | ID of the settlement | localityid | integer | t |
| idx02att | 3 | streetname | | | varchar(150) | t |
| idx03 | 3 | xie3street | | | | |
| idx03att | one | streetname | | | varchar(150) | t |
| idx04 | four | xpkstreet | ( ) street | | | |
| idx04att | one | wcrccode | Country code | wcrccode | smallint | t |
| idx04att | 2 | localityid | ID of the settlement | localityid | integer | t |
| idx04att | 3 | streetid | | streetid | smallint | t |
see also
Functions for documenting PostgreSQL databases. Part Two ;Functions for documenting PostgreSQL databases. Part ThreeFunctions for documenting PostgreSQL databases. The end (part four) .