Home PostgreSQL Functions for documenting PostgreSQLdatabases.Part Three

Functions for documenting PostgreSQLdatabases.Part Three

by admin

This is part three of an article that describes custom functions for working with system directories : pg_class, pg_attribute, pg_constraints, etc.
Functions for documenting PostgreSQLdatabases.Part Three
This part of the article discusses functions that return characteristics of sequences inherited by tables as well as the special characteristics of the attributes of the tables

See also
Functions for documenting PostgreSQLdatabases.Part One ;
Functions for documenting PostgreSQL databases.Part Two ;
Functions for documenting PostgreSQL databases.Ending(part four)
The first half of the article contains comments on the implementation of the functions.The second half contains the source code of the functions.Readers who are only interested in the source code should go straight to Appendix

Structure of a function that returns a list of sequence characteristics of a table

Functions for documenting PostgreSQLdatabases.Part Three
Figure 2. Functions on which admtf_Table_Sequencesfunctions depend
Table 11. Function assignment.

Title Destination
1 admtf_Sequence_Features The function returns the list of table sequence characteristics
2 admtf_Table_Sequences The function returns the list of database table sequences and their characteristics

Function admtf_Sequence_Features – List of database sequence characteristics

The admtf_Sequence_Featuresfunction returns a list of sequence characteristics (SEQUENCE) of the database. You can see and download the source code here

Function admtf_Sequence_Features returns a list of sequence characteristics ( SEQUENCE ) of the database

The function takes the name of the sequence as parameters ( a_SequenceName ) and the name of the circuit within which the sequence is created ( a_SchemaName ).

Need for function admtf_Sequence_Features arose because the main characteristics of the sequence are actually stored in a table with the same name as the sequence, and the data are retrieved from it using the operator SELECT In doing so, the sequence name, schema name, and sequence comment are stored in the pg_class , pg_namespace and pg_description

SELECT* FROM kr_road_network_vertices_pgr_id_seq;

Note 6

In version 10 PostgreSQL separated the characteristics of a sequence and the characteristics of its states. To do this, the directory pg_sequence with the characteristics of the sequence, 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.

Endnotes.

The presentation of each sequence as a table analog, 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 of the sequence as such.

Sequence record in the catalog pg_class differs from a table entry by the value of the relation type (relkind= ‘S’ ).

In order to retrieve 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 admtf_Sequence_Features(‘public’, ‘kr_road_network_vertices_pgr_id_seq’).

Title Commentary Current Beginning Increment End
kr_road_network
_vertices_pgr_id
_seq
Post-sequence 138023 1 1 9223372036854775807

Function admtf_Table_Sequenceslists the database table sequences and their characteristics

Function admtf_Table_Sequences returns the list of sequences ( SEQUENCE ) of the database table that generates the values of its fields, and the characteristics of those sequences. The source code can be viewed and downloaded here , and here is the version of the function which does not use the cursor

The function takes as parameters the name of the original table ( a_TableName ) and the name of the scheme within which the table is created (

a_SchemaName

).
Functions for documenting PostgreSQLdatabases.Part Three
source code of the operator in the figure

SELECT pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description, 'Sequence generating field values '|/da.attname) AS SequenceDescription, d.depType AS DependcyType, da.attname AS attributeNameFROM pg_dependdINNER JOIN pg_class pseq ON d.objid= pseq.oidINNER JOIN pg_namespacesnsp ON pseq.relnamespace=snsp.oidLEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoidAND dsc.objsubid=0INNER JOIN pg_class tbl ON d.refobjid= tbl.oidINNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oidINNER JOIN pg_attributeda ON da.attrelid= d.refobjidAND da.attnum= d.refobjsubidWHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)AND LOWER(tbl.relname)=LOWER(a_TableOID)AND tbl.relkind = 'r' AND pseq.relkind = 'S'ORDER BY pseq.relname;

The description of an individual sequence is a cumulative entry in pg_class describing it as a physical relation, and a conditional table with the name of the sequence containing data about specific characteristics of the sequence

Sequence and source table relationship information is stored in the system directory pg_depend

Table 13. Catalog attributes pg_depend necessary to implement the function.

Title Description
objid Sequence OID in the pg_class directory
objsubid This field contains zero
refobjid OID of the table which fields are populated with the sequence
refobjsubid The number of the attribute of the table whose values are filled with the sequence

Additional function accesses directory data pg_namespace and pg_description , in order to extract the schemas and comments of both the sequence and the source table.

To determine the attribute of the table whose values are populated using the sequence, the function refers to the directory pg_attribute by condition : attrelid= refobjid AND attnum= refobjsubid (In this condition, the directory attribute names to the right of the equal sign are pg_depend ).

The special characteristics of the table sequences are retrieved in the loop by calling the function admtf_Sequence_Features The loop is used because more than one sequence can be assigned to fill table fields.

Table 14. Result of the function admtf_Table_Sequences (‘public’, ‘kr_road_network_vertices_pgr’).

Title Commentary Start arrival End Field
kr_road_network
_vertices_pgr_id
_seq
Sequence generating the id field values 1 1 9223372036854775807 id

Version without cursor

In the environment of PostgreSQL version less than 10, implement the function admtf_Table_Sequences without using the cursor is probably not possible.
But the happy owners of version 10 can do without the cursor, because they have the pg_sequence In this case, all the characteristics of the sequence can be extracted with one operator SELECT

In the given implementation of the function with the window function RANK() OVER (PARTITION BY pseq.relname) calculates the sequence number used to fill the original table.

Functions for documenting PostgreSQLdatabases.Part Three
source code ofthe operator in the figure

SELECTRANK() OVER (PARTITION BY pseq.relname) AS SequenceNo, pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description, 'Sequence generating field values '|/da.attname) AS SequenceDescription, seq.seqstart AS SequenceStartValue, seq.seqincrement AS SequenceIncrementBy, seq.seqmax AS SequenceMaxValue, d.depType AS DependcyType, da.attname AS AttributeNameFROM pg_dependdINNER JOIN pg_class pseq ON d.objid= pseq.oidINNER JOIN pg_sequence seq ON seq.seqrelid= pseq.oidINNER JOIN pg_namespacesnsp ON pseq.relnamespace=snsp.oidLEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoidAND dsc.objsubid=0INNER JOIN pg_class tbl ON d.refobjid= tbl.oidINNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oidINNER JOIN pg_attribute da ON da.attrelid= d.refobjidAND da.attnum= d.refobjsubidWHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)AND LOWER(tbl.relname)=LOWER(a_TableOID)AND tbl.relkind = 'r' AND pseq.relkind = 'S'ORDER BY pseq.relname;

Note 7

This version ofthe function does not return the last value generated by the sequence ( last_value ).

Endnotes.

Function admtf_Table_InheritanceChildrens- list ofcharacteristics ofinherited tables

Function admtf_Table_InheritanceChildrens returns a list ofcharacteristics ofthe inherited tables ( INHERITS ) database table. You can view and download the source code here

The function takes as parameters the name of the original table ( a_TableName ) and the name of the scheme within which the table was created ( a_SchemaName ).

The description of the individual inherited table is in the entry in pg_class But to search for inherited tables by the name of the original table, you have to use the system directory pg_depend

Table 15.Catalog attributes pg_depend necessary to implement the function.

Title Description
objid OID of inherited table in pg_class directory
refobjid Source table OID

Functions for documenting PostgreSQLdatabases.Part Three
source code of the operator in the figure

SELECT rtbl.relname, rnspc.nspname, rdsc.description, rtbl.relnatts::INTEGER, rtbl.relchecks::INTEGER, rtbl.relhaspkey, rtbl.relhasindex, rtbl.relhassubclass, rtbl.reltuples::INTEGERFROM pg_class tblINNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oidLEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoidAND dsc.objsubid=0INNER JOIN pg_depend dp ON tbl.oid=dp.refobjidINNER JOIN pg_class rtbl ON rtbl.OID=dp.objidINNER JOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oidLEFT OUTER JOIN pg_Description rdsc ON rtbl.oid=rdsc.objoidAND rdsc.objsubid=0WHERE LOWER(nspc.nspname)=LOWER(a_SchemaName)AND LOWER(tbl.relname)=LOWER(a_TableOID)AND tbl.relkind = 'r' AND rtbl.relkind = 'r'ORDER BY rtbl.relname;

Additional function accesses directory data pg_namespace and pg_description , in order to retrieve schemas and comments for both the inherited and the original table.

Table 16.Result of the function admtf_Table_InheritanceChildrens (‘public’, ‘np_house’).

Title Commentary Attributes ? primary key ? indices ? descendants Number of records
np_house 04201000000 Houses in settlements (Achinsk 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 spawned table is chosen from the reltuple attribute of the directory pg_class Although this value is often exactlythe same as the actual number of records in the table, it is still an estimated value. You may want the result to be an exact value. 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 fulfill this statement in the text, the function admtf_Table_InheritanceChildrens Will have to use the cursor.

Second, I would like the function to output both the estimated and the exact number of table entries.

So the function has another optional parameter – the mode of getting the number of table entries ( a_Mode ), which takes values "estimated" ( estimate ) or "exactly" ( exactly ).

In addition a function was created admfn_Table_RowCount which returns the exact number of table entries, and in the list of returned values SELECT reltuple attribute is replaced by the following construction.

Functions for documenting PostgreSQLdatabases.Part Three
source code of the operator in the figure

CASE WHEN a_Mode= 'exactly' THEN admfn_Table_RowCount(rnspc.nspname, rtbl.relname)ELSE reltuples END

As a result, the function returns the estimated value of the indicator "number of table entries", if the parameter a_Mode is not set to return an exact value.

Structure of a function that returns a list of attribute characteristics for a table

Functions for documenting PostgreSQLdatabases.Part Three
Figure 3. Functions called by admtf_Attribute_Features
Functions for documenting PostgreSQLdatabases.Part Three
text version of the table in the picture Table 17. Function Assignment.

Title Destination
1 admtf_Attribute_PKFeatures The function returns the attribute’s presence in the primary key (PRIMARY KEY) and some of its characteristics as part of that key.
2 admtf_Attribute_FKFeatures The function returns an indication of the presence of the attribute in the foreign key (FOREIGN KEY) and some of its characteristics as part of that key.
3 admtf_Attribute_Features The function returns a list of table attribute characteristics.

Function admtf_Attribute_PKFeatures – whether the attribute is present in the primary key

Function admtf_Attribute_PKFeatures returns an indication of the presence of a table attribute in the primary key (PRIMARY KEY) of the table and, if present, what its sequence number is in that key, since the primary key can be compound.
You can see and download the source code here

The function takes as parameters the OID of the original table ( a_TableOID ) and the ordinal number of the sought attribute in it ( a_AttributeNo ).

The function retrieves the desired data from the directory entry pg_constraint containing constraints (CONSTRAINT) of the original table, including the primary key constraint. The OID of the lookup table is stored in the field conrelid , the primary key description is stored in a record in which the contype field contains the value ”p’

SELECT INTO v_PKAttributeList, rs_isAttributePK conkey, ARRAY[a_AttributeNo]<@conkeyFROM pg_constraintcWHERE c.contype='p' AND c.conrelid=a_TableOID;

Field conkey field of the thus found record contains an array of sequence numbers of the attributes that make up the primary key. Therefore, in order to check the presence of the original attribute in the primary key, it is enough to calculate the logical expression ARRAY[a_AttributeNo]<@conkey

If the attribute is present in the primary key, then its sequence number is calculated in the loop.

Function admtf_Attribute_FKFeatures -If the attribute is present in the external key

Function admtf_Attribute_FKFeatures returns an indication of the presence of the table attribute in one or more foreign keys (FOREIGN KEY) of the table and, if present, what are its sequence numbers in those keys, since the foreign key can be compound.
You can view and download the source code here

The function takes as parameters the OID of the original table ( a_TableOID ) and the ordinal number of the sought attribute in it ( a_AttributeNo ).

The function retrieves the desired data from the directory entry pg_constraint containing the constraints (CONSTRAINT) of the original table, including the foreign key constraints. The OID of the sought table is stored in the field conrelid , the primary key description is stored in the record where the contype field contains the value ”f’

SELECT * FROM pg_constraint cWHERE c.contype='f' AND c.conrelid=a_TableOIDAND ARRAY[a_AttributeNo]<@conkeyORDER BY c.oid;

Field conkey field of the thus found record contains an array of sequence numbers of the attributes that make up the foreign key.Therefore, in order to check whether the original attribute is present in the foreign key, it is enough to compute the logical expression ARRAY[a_AttributeNo]<@conkey

If the attribute is present in the foreign key, then the loop generates an array of its sequence numbers in the foreign keys that contain it.Additionally, two more arrays are formed from the table names and their attributes referenced by the original attribute in the foreign keys containing it.

Table names are extracted from the directory entry pg_class by the identifier (OID) extracted from the confrelid field of the external key record.

To get the nameof an attribute of an external table, the array of sequence numbers from the field

confkey

(It differs from the above array by the letter " f " in the name).From this array, the sequence number of the external table attribute to which the external attribute corresponds is retrieved.By this sequence number of the external table attribute and its OID, is in the pg_attributedirectory there are records about the attribute’s description and its name is retrieved.

Function admtf_Attribute_Features – List of table attribute characteristics

Functions admtf_Attribute_Features Returns a list of the following table attribute characteristics. You can see and download the source code here

Functions for documenting PostgreSQLdatabases.Part Three
The text version of the table in the figure

Title Type Appointment
1 AttributeName name Name of the original attribute.
2 UserTypeName VARCHAR(256) Custom type of source attribute
3 TypeName VARCHAR(256) Basic type of source attribute
4 isNotNULL BOOLEAN ? Tolerance of NULL value
5 isAttributePK BOOLEAN ? participation in PK
6 ColumnPKNo SMALLINT Serial number of the attribute in the PK
7 Description TEXT Comment to the original attribute
8 isAttributeFK BOOLEAN ? participation in FK
9 FKeyName name[] Array of table constraint names where the foreign key is defined
10 ColumnFKNo SMALLINT[] Array of sequence numbers of attribute in table foreign keys
11 FKTableName name[] Array of tables referenced by external keys
12 FKTableColumnName name[] Array of names of attributes in external tables, corresponding to the original attribute

The function takes as parameters the OID of the original table ( a_TableOID ) and the ordinal number of the sought attribute in it ( a_AttributeNo ).
The values of the fields AttributeName and IsNotNull are extracted from the directory entry pg_attribute 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_ColumnPKNoFROM admtf_Attribute_PKFeatures(a_TableOID, a_AttributeNo);SELECT rs_isAttributeFK, rs_FKeyName, rs_ColumnFKNo, rs_FKTableName, rs_FKTableColumnNameFROM admtf_Attribute_FKFeatures(a_TableOID, a_AttributeNo);

Field values isAttributePK and ColumnPKNo are returned by the function admtf_Attribute_PKFeatures

Field values isAttributeFK , FKeyName , ColumnFKNo , FKTableName , FKTableColumnName are returned by the function admtf_Attribute_FKFeatures

Function call admtf_Attribute_Features((SELECT OID FROM pg_class WHERE relname=’street’), 2::SMALLINT) will lead to the following result.

Table 18. Result of the function admtf_Attribute_Features

AttributeName UserTypeName TypeName isNotNULL isAttributePK ColumnPKNo
localityid localityid integer integer integer integer

Description isAttributeFK FKeyName ColumnFKNo FKTableName FKTableColumnName
Locality ID t {fk_street_locality} {2} {locality} {localityid}

APPENDIX 1. Scripts

Creating the function admtf_Sequence_Features

Comments on the source code of the function can be found here.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Sequence_Features (a_SchemaName NAME, a_SequenceName NAME);/****************************************************************************/ /* The function returns a list of the characteristics of the sequence belonging to the schema */ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Sequence_Features(a_SchemaName NAME default 'public', /* database schema name */ a_SequenceNameNAME default NULL /* Sequence name */)RETURNS TABLE (rs_SequenceName NAME, rs_SequenceDescription TEXT, rs_NumberOfAttribute INTEGER, rs_SequenceLastValue BIGINT, rs_SequenceStartValue BIGINT, rs_SequenceIncrementBy BIGINT, rs_SequenceMaxValue BIGINT) AS$BODY$DECLAREc_SequenceKind CONSTANT CHAR:='S';v_SequenceOID OID; /* Sequence ID */v_SequenceName NAME; /* Sequence Name */v_SequenceDescription TEXT; /* Sequence Description */v_SequenceStartValue BIGINT; /* Initial value of the sequence */v_SequenceIncrementBy BIGINT; /* Increment of the sequence */v_SequenceMaxValue BIGINT; /* Maximum value of the sequence */v_SequenceLastValue BIGINT; /* Maximum value of the sequence */v_SequenceNumberOfRowCalc INTEGER; /* Number of records in the table */--************************************************************************BEGINSELECT INTO rs_SequenceName, rs_SequenceDescription, rs_NumberOfAttributetbl.relname, COALESCE(dsc.description, 'Sequence') AS r_SequenceDescription, tbl.relnatts::INTEGER, tbl.relchecks::INTEGER, tbl.relhaspkey, tbl.relhasindex, tbl.relhassubclass, tbl.reltuples::INTEGERFROM pg_class tblINNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oidLEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoidAND dsc.objsubid=0WHERE nspc.nspname=LOWER(a_SchemaName)AND tbl.relkind=c_SequenceKindAND tbl.relname =LOWER(a_SequenceName);IF FOUND THENEXECUTE 'SELECT last_value, start_value, increment_by, max_valueFROM '||LOWER(a_SchemaName)||'.'||LOWER(a_SequenceName) INTOv_SequenceLastValue, v_SequenceStartValue, v_SequenceIncrementBy, v_SequenceMaxValue ;RETURN QUERY SELECT rs_SequenceName, rs_SequenceDescription, rs_NumberOfAttribute, v_SequenceLastValue, v_SequenceStartValue, v_SequenceIncrementBy, v_SequenceMaxValue;END IF;RETURN;END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admtf_Sequence_Features(a_SchemaName NAME, a_SequenceName NAME) IS 'The function returns a list of sequence characteristics belonging to the schema';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Sequence_Features (a_SchemaName VARCHAR(256), a_SequenceName VARCHAR(256)); /****************************************************************************/ /* The function returns a list of the characteristics of the sequence belonging to the scheme *//****************************************************************************/CREATE OR REPLACE FUNCTION admtf_Sequence_Features(a_SchemaName VARCHAR(256) default 'public', /* the name of the database schema */ a_SequenceName VARCHAR(256) default NULL, / * Name of the sequence */ ) RETURNS TABLE (rs_SequenceName VARCHAR(256), rs_SequenceDescription TEXT rs_NumberOfAttribute INTEGER, BIGINT rs_SequenceLastValue, rs_SequenceStartValue BIGINT, BIGINT rs_SequenceIncrementBy, rs_SequenceMaxValue BIGINT) AS $BODY$ DECLAREc_SequenceKind CONSTANT CHAR:='S';--********************************************************BEGINRETURN QUERY SELECT 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;COMMENT ON FUNCTION admtf_Sequence_Features(a_SchemaName VARCHAR(256), a_SequenceName VARCHAR(256)) IS 'The function returns a list of characteristics of the sequence belonging to the schema';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;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);

Creating the function admtf_Table_Sequences

Comments on the source code of the function can be found here.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME);/*********************************************************************//* The function returns a list of sequences depends on table */ /*********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences(a_SchemaName NAME default 'public', /* database schema name */a_TableName NAME default NULL /* Table name */)RETURNS TABLE (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$DECLAREv_TableOID INTEGER;/* Table OID*/v_Sequence RECORD;/* Sequence record*/v_SequenceOID INTEGER;/* OID restrictions*/v_SequenceName NAME;/* Sequence name */v_SequenceSchemaName NAME;/* Sequence scheme name */v_SequenceDescription TEXT; /* Sequence description */v_SequenceStartValue BIGINT; /* Sequence start value */v_SequenceIncrementBy BIGINT; /* Sequence increment */v_SequenceMaxValue BIGINT; /* Maximum sequence value */v_DependencyType NAME; /* Alphabetic notation of dependency type *//* tables from the sequence */v_AttributeName NAME; /* Attribute Name */v_SequenceNumber SMALLINT; /* Sequence number */c_Delimiter CONSTANT VARCHAR(2):=', ';--*********************************************************************BEGINv_SequenceNumber:=0;FOR v_Sequence IN SELECT pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description, 'Sequence generatingfield values '||da.attname) AS SequenceDescription, d.depType AS DependcyType, da.attname AS AttributeNameFROM pg_depend dINNER JOIN pg_class pseq ON d.objid = pseq.oidINNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oidLEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoidAND dsc.objsubid=0INNER JOIN pg_class tbl ON d.refobjid = tbl.oidINNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oidINNER JOIN pg_attribute da ON da.attrelid= d.refobjidAND d.refobjsubid=da.attnumWHERE tbl.relkind = 'r' AND pseq.relkind = 'S'AND LOWER(nsp.nspname)=LOWER(a_SchemaName)AND LOWER(tbl.relname)=LOWER(a_TableName)ORDER BY pseq.relnameLOOPv_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;SELECT INTO v_SequenceStartValue, v_SequenceIncrementBy, v_SequenceMaxValuers_SequenceStartValue, rs_SequenceIncrementBy, rs_SequenceMaxValueFROM 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;END LOOP;RETURN;END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS 'Returns a list of sequences that the table depends on';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)); /**********************************************************************/ /* The function returns a list of sequences depends on table */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256) default 'public', /* name of the database schema */a_TableName VARCHAR(256) default NULL, / * table Name */ ) RETURNS TABLE (r_SequenceNumber SMALLINT, r_SequenceName VARCHAR(256), r_SequenceSchemaName VARCHAR(256), r_SequenceDescription TEXT r_SequenceStartValue BIGINT, BIGINT r_SequenceIncrementBy, r_SequenceMaxValue BIGINT r_DependType VARCHAR(256), r_RefTableName VARCHAR(256), r_RefTableSchemaName VARCHAR(256), r_RefAttributeName VARCHAR(256)) AS $BODY$ DECLARE c_Delimiter CONSTANT VARCHAR(2):=', ';--******************************************************BEGINRETURN QUERY SELECT 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;COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Returns list of sequences that the table depends on';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;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 admtf_Table_Sequences function without a cursor (PostgreSQL 10)

Comments on the source code of the function can be found here.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName NAME a_TableName NAME); /*********************************************************************/ /* The function returns a list of sequences depends on the table *//**********************************************************************/CREATE OR REPLACE FUNCTION admtf_Table_Sequences(a_SchemaName default NAME is 'public', /* the name of the database schema */ a_TableName NAME default NULL, / * table Name */ ) RETURNS TABLE (r_SequenceNumber SMALLINT, r_SequenceName NAME r_SequenceSchemaName NAME r_SequenceDescription TEXT r_SequenceStartValue BIGINT, BIGINT r_SequenceIncrementBy, r_SequenceMaxValue BIGINT r_DependType NAME r_RefTableName NAME r_RefTableSchemaName NAME r_RefAttributeName NAME) AS $BODY$DECLAREv_TableOID INTEGER; /* Table OID*/v_Sequence RECORD; /* Sequence record*/v_SequenceOID INTEGER; /* OID restrictions*/v_SequenceName NAME; /* Sequence name */v_SequenceSchemaName NAME; /* Sequence scheme name */v_SequenceDescription TEXT; /* Sequence description */v_SequenceStartValue BIGINT; /* Sequence start value */v_SequenceIncrementBy BIGINT; /* Sequence increment */v_SequenceMaxValue BIGINT; /* Maximum sequence value */v_DependcyType NAME; /* Alphabetic designation of the table's sequence dependency type */v_AttributeName NAME; /* Attribute Name */v_SequenceNumber SMALLINT; /* Sequence number */c_Delimiter CONSTANT VARCHAR(2):=', ';--******************************************************************BEGINv_SequenceNumber:=0;FOR v_Sequence IN SELECT pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description, 'Sequence generatingfield values '||da.attname) AS SequenceDescription, d.depType AS DependcyType, da.attname AS AttributeNameFROM pg_depend dINNER JOIN pg_class pseq ON d.objid = pseq.oidINNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oidLEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoidAND dsc.objsubid=0INNER JOIN pg_class tbl ON d.refobjid = tbl.oidINNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oidINNER JOIN pg_attribute da ON da.attrelid= d.refobjidND d.refobjsubid=da.attnumWHERE tbl.relkind = 'r' AND pseq.relkind = 'S'AND LOWER(nsp.nspname)=LOWER(a_SchemaName)AND LOWER(tbl.relname)=LOWER(a_TableName)ORDER BY pseq.relnameLOOPv_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;SELECT INTO v_SequenceStartValue, v_SequenceIncrementBy, v_SequenceMaxValuers_SequenceStartValue, rs_SequenceIncrementBy, rs_SequenceMaxValueFROM 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;END LOOP;RETURN;END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS 'Returns a list of sequences that the table depends on';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));/**********************************************************************//* The function returns a list of sequences depends on table */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256) default 'public', /* database schema name */ a_TableNameVARCHAR(256) default NULL /* Table name */)RETURNS TABLE (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$DECLAREc_Delimiter CONSTANT VARCHAR(2):=', ';--*******************************************************BEGINRETURN QUERY SELECT 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;COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Returns a list of sequences that the table depends on';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;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 admfn_Table_RowCount function

Comments on the source code of the function can be found here.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admfn_Table_RowCount (a_SchemaName NAME a_TableName NAME); /******************************************************/ /* The function returns the number of rows in the table */ /******************************************************/ CREATE OR REPLACE FUNCTION admfn_Table_RowCount(a_SchemaName NAME default 'public', /* database schema name */a_TableName NAME default NULL /* Table name */)RETURNS BIGINT AS$BODY$DECLARE v_TableNumberOfRowCalc BIGINT; /* Quantity */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_TableNameINTO v_TableNumberOfRowCalc;ELSESELECT INTO v_Found trueFROM pg_class tblINNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oidWHERE tbl.relkind='r' AND tbl.relname=a_TableNameAND nspc.nspname=a_SchemaName;IF FOUND THENEXECUTE 'SELECT count(*) FROM ' ||CASE WHEN a_SchemaName ~ E'^[a-z_0-9]+$' THENa_SchemaNameELSE quote_ident(a_SchemaName) END||'.'||CASE WHEN a_TableName ~ E'^[a-z_0-9]+$' THENa_TableNameELSE quote_ident(a_TableName) ENDINTO v_TableNumberOfRowCalc;ELSESELECT INTO v_Found trueFROM pg_class tblINNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oidWHERE tbl.relkind='r' AND LOWER(tbl.relname)= LOWER(a_TableName)AND nspc.nspname=LOWER(a_SchemaName);IF FOUND THENEXECUTE 'SELECT count(*) FROM ' || a_SchemaName ||'.'||a_TableNameINTO v_TableNumberOfRowCalc;END IF;END IF;END IF;RETURN v_TableNumberOfRowCalc;END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admfn_Table_RowCount(a_SchemaName NAME, a_TableName NAME) IS 'Returns the number of rows in the table';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admfn_Table_RowCount (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)); /********************************************************************/ /* The function returns the number of rows in the table *//********************************************************************/CREATE OR REPLACE FUNCTION admfn_Table_RowCount(a_SchemaName VARCHAR(256) default 'public', /* database schema name */ a_TableNameVARCHAR(256) default NULL /* Table name */)RETURNS BIGINT AS$BODY$DECLARE v_TableNumberOfRowCalc BIGINT; /* Quantity */--*********************************************************BEGINRETURN admfn_Table_RowCount(a_SchemaName::NAME, a_TableName::NAME);END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admfn_Table_RowCount(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Returns the number of rows in the table';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;SELECt admfn_Table_RowCount('public'::NAME, 'Street'::NAME);SELECt admfn_Table_RowCount('public'::VARCHAR(256), 'Street'::VARCHAR(256));

Creating the admtf_Table_InheritanceChildrens function

Comments on the source code of the function can be found here.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Table_InheritanceChildrens (a_SchemaName NAME a_TableName NAME, a_Mode VARCHAR(10)); /************************************************************/ /* The function returns a list of characteristics inherited tables *//************************************************************/CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName NAME default 'public', /* database schema name */ a_TableNameNAME default NULL, /* Table name */a_Mode VARCHAR(10) default 'estimate'/*Calculation mode for the number of records in the table*/)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$DECLAREc_TableKind CONSTANT CHAR:='r';c_ExactlyMode CONSTANT VARCHAR(10):='exactly';c_EstimateMode CONSTANT VARCHAR(10):='estimate';v_TableOID OID; /* Table ID */v_SchemaName NAME; /* Table schema name */v_TableName NAME; /* Table name */v_TableDescription TEXT; /* Table description */v_TableNumberOfRowCalc INTEGER; /* Number of entries in the table */v_InheritanceRECORD RECORD; /* Inheritance table entries */v_InheritanceOID OID; /* Inheritance table ID */BEGINRETURN QUERY SELECT rtbl.relname, rdsc.description, rtbl.relnatts::INTEGER, rtbl.relchecks::INTEGER, rtbl.relhaspkey, rtbl.relhasindex, rtbl.relhassubclass, CASE WHEN a_Mode=c_ExactlyMode THENadmfn_Table_RowCount(rnspc.nspname, rtbl.relname)::INTEGERELSE rtbl.reltuples::INTEGER ENDFROM pg_class tblINNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oidLEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoidAND dsc.objsubid=0INNER JOIN pg_depend dp ON tbl.oid=dp.refobjidINNER JOIN pg_class rtbl ON rtbl.OID=dp.objidINNER JOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oidLEFT OUTER JOIN pg_Description rdsc ON rtbl.oid=rdsc.objoidAND rdsc.objsubid=0WHERE nspc.nspname=LOWER(a_SchemaName)AND tbl.relkind=c_TableKind AND rtbl.relkind=c_TableKindAND tbl.relname =LOWER(a_TableName)ORDER BY rtbl.relname;END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName NAME, a_TableName NAME, a_Mode VARCHAR(10)) IS 'Returns a list of characteristics of inherited tables';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Table_InheritanceChildrens (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256), a_TableName NAME, a_Mode VARCHAR(10)); /************************************************************************/ /* The function returns a list of characteristics inherited tables */ /************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName VARCHAR(256) default 'public', /* database schema name */a_TableName VARCHAR(256) default NULL, /* Table name */ a_ModeVARCHAR(10) default 'estimate'/*Calculation mode for the number of records in the table*/)RETURNS TABLE (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$DECLAREc_TableKind CONSTANT CHAR:='r';BEGINRETURN QUERY SELECT 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;COMMENT ON FUNCTION admtf_Table_InheritanceChildrens(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256), a_Mode VARCHAR(10)) IS 'Returns a list of characteristics of inherited tables';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;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.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Attribute_PKFeatures (a_TableOID OID a_AttributeNo SMALLINT); /***************************************************************************/ /* The function returns the sign of the presence of columns in the primary key. *//* If the column is present, returns and its ordinal number in the list of columns */ /* primary key */ /***************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Attribute_PKFeatures(a_TableOID OID, /* Table ID */ a_AttributeNoSMALLINT /* The ordinal number of the attribute in the table*/)RETURNS TABLE (rs_isAttributePK BOOLEAN, rs_PKeyName name, rs_ColumnPKNo SMALLINT) AS$BODY$DECLARE C_PKAttributeList_NDims CONSTANT INTEGER:=1;/* Dimension of the array of primary key attributes*/v_PKAttributeList SMALLINT[];/* List of atrebut numbers in the primary key*/v_PKAttributeIndx INTEGER;/* Current index of the primary key attribute array*/v_PKAttributeLBound INTEGER;/* Subscript of the primary key attribute array*/v_PKAttributeUBound INTEGER;/* Superscript of the primary key attribute array*/--********************************************************************** BEGINrs_isAttributePK:=false;rs_ColumnPKNo:=NULL;SELECT INTO rs_PKeyName, v_PKAttributeList, rs_isAttributePKconname, conkey, ARRAY[a_AttributeNo]<@conkeyFROM pg_constraint cWHERE c.contype='p' and c.conrelid=a_TableOID;IF FOUND AND rs_isAttributePK THEN -- the column is present in the primary keyv_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_PKAttributeUBoundAND a_AttributeNo<> v_PKAttributeList[v_PKAttributeIndx]LOOPv_PKAttributeIndx:=v_PKAttributeIndx+1;END LOOP;IF v_PKAttributeIndx<=v_PKAttributeUBound THENrs_ColumnPKNo:=v_PKAttributeIndx;END IF;END IF;RETURN QUERY SELECT rs_isAttributePK, rs_PKeyName, rs_ColumnPKNo;END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admtf_Attribute_PKFeatures(a_TableOID OID, a_AttributeNo SMALLINT) IS 'Returns the sign of the presence of the column in the primary key and the sequence number in the list of primary key columns';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;SELECT * FROM admtf_Attribute_PKFeatures((SELECT OID FROM pg_class WHERE relname='street'), 3::SMALLINT);

Create function admtf_Attribute_FKFeatures

Comments on the source code of the function can be found here.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Attribute_FKFeatures (a_TableOID OID, a_AttributeNo SMALLINT);/****************************************************************************//* The function returns a sign of the presence of a column in the external key. *//* If a column is present, its ordinal number in the list of *//* columns of the foreign key is also returned. *//****************************************************************************//****************************************************************************/CREATE OR REPLACE FUNCTION admtf_Attribute_FKFeatures(a_TableOID OID, /* Table ID */ a_AttributeNoSMALLINT /* The ordinal number of the attribute in the table*/)RETURNS TABLE (rs_isAttributeFK BOOLEAN, rs_FKeyName name[], rs_ColumnFKNo SMALLINT[], rs_FKTableName name[], rs_FKTableColumnName name[]) AS$BODY$DECLARE C_FKAttributeList_NDims CONSTANT INTEGER:=1;/* Dimension of the array of foreign key attributes*/v_FKAttributeList SMALLINT[];/* List of attribute numbers as part of the foreign key*/v_RefAttributeList SMALLINT[];/* List of attribute numbers in the table, *//* to which the foreign key refers to */v_FKAttributeIndx INTEGER;/* Current attribute array index of the foreign key*/v_RefAttributeListIndx INTEGER;/* Current attribute array index of the table, *//* to which the foreign key refers */v_FKAttributeLBound INTEGER;/* Bottom index of the foreign key attribute array *//v_FKAttributeUBound INTEGER;/* Top index of the foreign key attribute array*/v_FKConstraintIndx INTEGER;/* Current foreign key restriction index*/v_FKeyName name;/* Name of the table constraint, *//* where the foreign key is defined */v_FKTableName name;/* Name of the table to which the foreign key is referenced.v_FKTableColumnName name;/* Attribute name in the table, *//* The table the foreign key refers to.v_RefAttributeNo SMALLINT;/* The sequence number of the attribute in the table, *//* to which the foreign key refers to */v_Constraint pg_constraint%ROWTYPE;/* System Description Table entry *//* constraints (CONSTANT) */--******************************************************************************************************BEGINrs_isAttributeFK:=false;rs_ColumnFKNo:=NULL;v_FKConstraintIndx:=0;FOR v_Constraint IN SELECT * FROM pg_constraint cWHERE c.contype='f' and c.conrelid=a_TableOIDAND ARRAY[a_AttributeNo]<@conkeyORDER BY c.oidLOOPv_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_FKAttributeUBoundAND a_AttributeNo<> v_FKAttributeList[v_FKAttributeIndx]LOOPv_FKAttributeIndx:=v_FKAttributeIndx+1;END LOOP;rs_FKeyName[v_FKConstraintIndx]:=v_FKeyName;rs_ColumnFKNo[v_FKConstraintIndx]:=v_FKAttributeIndx;SELECT INTO v_FKTableName ftbl.relnameFROM pg_class ftbl WHERE ftbl.oid=v_Constraint.confrelid;rs_FKTableName[v_FKConstraintIndx]:=v_FKTableName;v_RefAttributeNo:=v_RefAttributeList[v_FKAttributeIndx];v_FKTableColumnName:=NULL;SELECT INTO v_FKTableColumnName attnameFROM pg_attribute aWHERE a.attrelid=v_Constraint.confrelid AND a.attnum=v_RefAttributeNo;rs_FKTableColumnName[v_FKConstraintIndx]:=v_FKTableColumnName;END LOOP;RETURN QUERY SELECT rs_isAttributeFK, rs_FKeyName, rs_ColumnFKNo, rs_FKTableName, rs_FKTableColumnName;END$BODY$LANGUAGE plpgsql;COMMENT ON FUNCTION admtf_Attribute_FKFeatures(a_TableOID OID, a_AttributeNo SMALLINT) IS 'Returns the sign of the column presence in the primary key and the sequence number in the external key column list';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;SELECT * FROM admtf_Attribute_FKFeatures((SELECT OID FROM pg_class WHERE relname='street'), 4::SMALLINT);

Creating the function admtf_Attribute_Features

Comments on the source code of the function can be found here.
function code

BEGIN TRANSACTION;DROP FUNCTION IF EXISTS admtf_Attribute_Features (a_TableOID OID a_AttributeNo SMALLINT); /****************************************************************************/ /* The function returns the column characteristics table *//****************************************************************************/CREATE OR REPLACE FUNCTION admtf_Attribute_Features(a_TableOID OID, /* Table ID */ a_AttributeNoSMALLINT/* The sequential number of the attribute in the table*/)RETURNS TABLE (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$DECLAREv_Return_Error Integer := 0; /* Return code*/--*********************************************************************BEGINSELECT INTO rsa_AttributeName, rsa_UserTypeName, rsa_TypeName, rsa_isNotNULL, rsa_Descriptionattr.attname, CASE WHEN COALESCE(typ.typbasetype, 0)> 0 THEN typ.typname::VARCHAR(100)ELSE ''END AS 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_DescriptionFROM pg_attribute attrLEFT OUTER JOIN pg_type typ ON attr.atttypid=typ.oidLEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oidLEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelidAND dsc.objsubid=attr.attnumWHERE attr.attrelid =a_TableOID AND attr.attnum=a_AttributeNo;SELECT INTO rsa_isAttributePK, rsa_ColumnPKNo rs_isAttributePK, rs_ColumnPKNoFROM admtf_Attribute_PKFeatures(a_TableOID, a_AttributeNo);SELECT INTO rsa_isAttributeFK, rsa_FKeyName, rsa_ColumnFKNo, rsa_FKTableName, rsa_FKTableColumnName rs_isAttributeFK, rs_FKeyName, rs_ColumnFKNo, rs_FKTableName, rs_FKTableColumnNameFROM 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;COMMENT ON FUNCTION admtf_Attribute_Features(a_TableOID OID, a_AttributeNo SMALLINT) IS 'Returns table column properties';--ROLLBACK TRANSACTION;COMMIT TRANSACTION;SELECT * FROM admtf_Attribute_Features ((SELECT OID FROM 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. Ending(part four)

You may also like