Functions for documenting PostgreSQL databases. Part three
This part of the article discusses functions that return the characteristics of sequences, inherited tables , and the special characteristics of table attributes .
see also
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Two ;
Functions for documenting PostgreSQL databases. The end (part four) .
The first half of the article contains comments on the implementation of functions. In the second, the source code of the functions. For those readers who are interested only in the source code, we suggest that you go straight to the Appendix .
The structure of the function that returns a list of characteristics of table sequences
Fig. 2. Functions on which the function admtf_Table_Sequences depends
')
Table 11. Assignment of functions.
| No | Title | Purpose |
|---|---|---|
| one | admtf_Sequence_Features | The function returns a list of table sequence characteristics. |
| 2 | admtf_Table_Sequences | The function returns a list of database table sequences and their characteristics. |
Function admtf_Sequence_Features - list of database sequence characteristics
The function admtf_Sequence_Features returns a list of sequence characteristics (SEQUENCE) of a database. Source code can be viewed and downloaded here .
The function admtf_Sequence_Features returns a list of sequence characteristics ( SEQUENCE ) of a database.
.As parameters, the function takes the name of the sequence ( a_SequenceName ) and the name of the circuit within which the sequence is created ( a_SchemaName ).
The need for the admtf_Sequence_Features function arose from the fact that the main characteristics of a sequence are actually stored in a table whose name matches the sequence name, and the data is extracted from it using the SELECT statement . In this case, the name of the sequence, the name of the scheme and commentary to the sequence are stored in the pg_class , pg_namespace and pg_description directories .
SELECT * FROM kr_road_network_vertices_pgr_id_seq; Remark 6
In version 10, PostgreSQL divided the characteristics of a sequence and the characteristics of its states. For this purpose, the pg_sequence directory with sequence characteristics is entered, containing the initial value ( start_value ), increment ( increment_by ) and maximum value ( max_value ) of the sequence. The last value returned by the sequence ( last_value ) was left in the “table” with the name of the sequence.
The end of the remark.
The representation of each sequence as an analogue of the table, I think, is dictated by the need to store the last used value of the sequence ( last_value ), which is a characteristic of the state of the sequence, but not the sequence as such.
The sequence entry in the pg_class directory differs from the table entry by the value of the relation type (relkind = 'S' ).
In order to extract the characteristics of an arbitrary sequence, you have to use dynamic SQL.
EXECUTE 'SELECT last_value,start_value,increment_by,max_value FROM '|| LOWER(a_SchemaName)||'.'||LOWER(a_SequenceName) INTO v_SequenceLastValue,v_SequenceStartValue, v_SequenceIncrementBy,v_SequenceMaxValue ; Table 12. Result of execution of the function admtf_Sequence_Features ('public', 'kr_road_network_vertices_pgr_id_seq').
| Title | Comment | Current | Start | Increment | the end |
|---|---|---|---|---|---|
| kr_road_network _vertices_pgr_id _seq | Sequence | 138023 | one | one | 9223372036854775807 |
The function admtf_Table_Sequences a list of sequences of a database table and their characteristics
The function admtf_Table_Sequences returns a list of sequences ( SEQUENCE ) of a database table, generating the values of its fields, and the characteristics of these sequences. The source code can be viewed and downloaded here , and here is the version of the function in which the cursor is not used .
As parameters, the function takes the name of the source table ( a_TableName ) and the name of the schema within which the table is created (
a_SchemaName
).
SELECT pseq.relname AS SequenceName,snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,', ' ||da.attname) AS SequenceDescription, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid AND da.attnum= d.refobjsubid WHERE 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 a separate sequence is a collection of entries in pg_class , describing it as a physical relation, and a conditional table with the name of the sequence containing data on the specific characteristics of the sequence
Information about the relationship between the sequence and the source table is stored in the pg_depend system directory.
Table 13. Attributes of the pg_depend directory required to implement the function.
| Title | Description |
|---|---|
| objid | Sequence OID in pg_class directory |
| objsubid | This field is zero. |
| refobjid | The OID of the table, in which fields the sequence is used. |
| refobjsubid | The attribute number of the table, the values of which are populated using the sequence |
Additionally, the function accesses the data in the pg_namespace and pg_description directories in order to extract the schemas and comments of both the sequence and the source table.
To determine the attribute of a table whose values are filled using a sequence, the function refers to the pg_attribute directory by the condition: attrelid = refobjid AND attnum = refobjsubid . (In this condition, the names of the pg_depend attributes of the directory are indicated to the right of the equal sign).
The special characteristics of the table sequences are retrieved in a loop by calling the function admtf_Sequence_Features . The cycle is used because more than one sequence can be assigned to fill in the table fields.
Table 14. Result of execution of the function admtf_Table_Sequences ('public', 'kr_road_network_vertices_pgr').
| Title | Comment | Start | Increment | the end | Field |
|---|---|---|---|---|---|
| kr_road_network _vertices_pgr_id _seq | A sequence that generates id field values | one | one | 9223372036854775807 | id |
Version without cursor
In a PostgreSQL environment that has a version less than 10, it’s most likely impossible to implement the function admtf_Table_Sequences without using a cursor.
But the happy owners of version 10 may well do without a cursor, because they have the pg_sequence directory at their disposal. In this case, all characteristics of the sequence can be retrieved with a single SELECT statement .
In the above implementation of the function, using the window function RANK () OVER (PARTITION BY pseq.relname) , the sequence number of the sequence used to populate the original table is calculated.
SELECT RANK() OVER (PARTITION BY pseq.relname) AS SequenceNo, pseq.relname AS SequenceName,snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,', ' ||da.attname) AS SequenceDescription, seq.seqstart AS SequenceStartValue,seq.seqincrement AS SequenceIncrementBy, seq.seqmax AS SequenceMaxValue, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_sequence seq ON seq.seqrelid= pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid AND da.attnum= d.refobjsubid WHERE 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; Remark 7
.This version of the function does not return the last value generated by the sequence ( last_value ).
The end of the remark.
Function admtf_Table_InheritanceChildrens - a list of characteristics of inherited tables
The admtf_Table_InheritanceChildrens function returns a list of the inherited table characteristics ( INHERITS ) of a database table. Source code can be viewed and downloaded here .
As parameters, the function takes the name of the source table ( a_TableName ) and the name of the schema within which the table is created ( a_SchemaName ).
The description of a separate inherited table is in an entry in pg_class . But to search for inherited tables by the name of the source table, you have to use the system catalog pg_depend .
Table 15. Attributes of the pg_depend directory required to implement the function.
| Title | Description |
|---|---|
| objid | OID of the inherited table in the pg_class directory |
| refobjid | OID source table |
SELECT rtbl.relname,rnspc.nspname,rdsc.description,rtbl.relnatts::INTEGER, rtbl.relchecks::INTEGER, rtbl.relhaspkey,rtbl.relhasindex,rtbl.relhassubclass, rtbl.reltuples::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 INNER JOIN pg_depend dp ON tbl.oid=dp.refobjid INNER JOIN pg_class rtbl ON rtbl.OID=dp.objid INNER JOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oid LEFT OUTER JOIN pg_Description rdsc ON rtbl.oid=rdsc.objoid AND rdsc.objsubid=0 WHERE 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; Additionally, the function accesses the data in the pg_namespace and pg_description directories in order to extract the schemas and comments for both the inherited and the source table.
Table 16. Result of execution of the function admtf_Table_InheritanceChildrens ('public', 'np_house').
| Title | Comment | Attributes | ? primary key | ? indexes | ? descendants | Number of records |
|---|---|---|---|---|---|---|
| np_house 04201 000000 | Houses in settlements (Achinsky district) | 15 | f | f | f | 5651 |
| np_house 4208 000000 | Houses in settlements (Bogotolsky district) | 15 | f | f | f | 4314 |
The number of records in the child table is selected from the reltuple attribute of the pg_class directory. Although this value often coincides exactly with the actual number of records in the table, it is still an estimated value. This means there may be a desire to get the exact value as a result. For example, as shown in the figure.
EXECUTE 'SELECT COUNT(*) FROM '||LOWER(a_SchemaName)||'.'||LOWER(a_TableName) INTO v_TableNumberOfRowCalc; But, first, in order to execute this statement in the text, the function admtf_Table_InheritanceChildrens will have to use a cursor.
Secondly, I would like the function to allow the output of both the estimated and the exact number of table entries.
Therefore, the function has one more optional parameter - the mode of obtaining the number of records in the table ( a_Mode ), which takes the values “estimated” ( estimate ) or “exactly”.
Additionally, the function admfn_Table_RowCount , which returns the exact number of table entries, is created, and in the list of returned SELECT values, the reltuple attribute is replaced with the following construction.
CASE WHEN a_Mode = 'exactly' THEN admfn_Table_RowCount(rnspc.nspname,rtbl.relname) ELSE reltuples END As a result, the function returns the estimated value of the “number of table entries” indicator, if the a_Mode parameter does not specify the requirement to return an exact value.
The structure of the function that returns a list of table attribute characteristics
Fig. 3. Functions that admtf_Attribute_Features Calls
| No | Title | Purpose |
|---|---|---|
| one | admtf_Attribute_PKFeatures | The function returns the indication of the presence of an attribute in the primary key (PRIMARY KEY), as well as some of its characteristics as part of this key. |
| 2 | admtf_Attribute_FKFeatures | The function returns an indication of the presence of an attribute in the foreign key (FOREIGN KEY), as well as some of its characteristics as part of this key. |
| 3 | admtf_Attribute_Features | The function returns a list of table attribute characteristics. |
Function admtf_Attribute_PKFeatures - - is there an attribute in the primary key
The function admtf_Attribute_PKFeatures returns a sign of the presence of a table attribute in the primary key (PRIMARY KEY) of the table, and, if present, what is its sequence number in this key, since primary key can be composite.
Source code can be viewed and downloaded here .
As parameters, the function takes the OID of the source table ( a_TableOID ) and the sequence number of the required attribute in it ( a_AttributeNo ).
The function extracts the necessary data from the pg_constraint directory entry containing the constraints (CONSTRAINT) of the source table, including the primary key constraint. The OID of the table you are looking for is stored in the conrelid field, the description of the primary key is stored in the record in which the contype field contains the value '' p '
. SELECT INTO v_PKAttributeList,rs_isAttributePK conkey,ARRAY[a_AttributeNo]<@conkey FROM pg_constraint c WHERE c.contype='p' AND c.conrelid=a_TableOID; The conkey field of the entry thus found contains an array of attribute sequence numbers that make up the primary key. Therefore, in order to check the presence of the source attribute in the primary key, it suffices to calculate the logical expression ARRAY [a_AttributeNo] <@ conkey .
If the attribute is present in the primary key, then its sequence number is calculated further in the loop.
Function admtf_Attribute_FKFeatures - is there an attribute in the foreign key
The function admtf_Attribute_FKFeatures returns an indication of the presence of a table attribute in one or more foreign keys (FOREIGN KEY) of the table, and, if present, its serial numbers in these keys, since foreign key can be composite.
Source code can be viewed and downloaded here .
As parameters, the function takes the OID of the source table ( a_TableOID ) and the sequence number of the required attribute in it ( a_AttributeNo ).
The function extracts the necessary data from the pg_constraint directory entry containing the constraints (CONSTRAINT) of the source table, including, but not limited to, foreign key constraints. The OID of the table you are looking for is stored in the conrelid field, the primary key description is stored in the record in which the contype field contains the value '' f '
. SELECT * FROM pg_constraint c WHERE c.contype='f ' AND c.conrelid=a_TableOID AND ARRAY[a_AttributeNo]<@conkey ORDER BY c.oid; The conkey field of the entry thus found contains an array of attribute sequence numbers that make up the foreign key. Therefore, in order to check the presence of the source attribute in the foreign key, it suffices to calculate the logical expression ARRAY [a_AttributeNo] <@ conkey .
If the attribute is present in the foreign key, then later in the loop an array of its ordinal numbers is formed in the foreign keys containing it. Additionally, two more arrays are formed from the names of the tables and their attributes that are referenced by the original attribute in the foreign keys containing it.
Table names are extracted from the pg_class directory entry by identifier (OID) extracted from the confrelid field of the foreign key entry.
To get the name of an attribute of an external table, use an array of sequence numbers from the field
confkey
(it differs from the above array by the letter “ f ” in the name). From this array is extracted the ordinal number of the attribute of the external table, which corresponds to the external attribute. By this ordinal number of the attribute of the external table and its OID, located in the pg_attribute directory, there is an entry describing the attribute and its name is extracted.Function admtf_Attribute_Features - a list of characteristics of the attribute table
The function admtf_Attribute_Features returns a list of the following characteristics of a table attribute. Source code can be viewed and downloaded here .
| No | Title | Type of | Purpose |
|---|---|---|---|
| one | AttributeName | name | The name of the source attribute. |
| 2 | UserTypeName | VARCHAR (256) | Custom Attribute Type |
| 3 | Typename | VARCHAR (256) | Base type of source attribute |
| four | isNotNULL | Boolean | ? Null value valid |
| five | isAttributePK | Boolean | ? participation in PK |
| 6 | ColumnPKNo | SMALLINT | Attribute ordinal number in PK |
| 7 | Description | TEXT | Original attribute comment |
| eight | isAttributeFK | Boolean | ? participation in FK |
| 9 | FKeyName | name [] | An array of names of the constraints of the table in which the foreign key is defined |
| ten | Columnfkno | SMALLINT [] | An array of attribute sequence numbers in the table's foreign keys |
| eleven | FKTableName | name [] | Array of tables referenced by foreign keys |
| 12 | FKTableColumnName | name [] | Array of attribute names in external tables corresponding to the source attribute |
As parameters, the function takes the OID of the source table ( a_TableOID ) and the sequence number of the required attribute in it ( a_AttributeNo ).
The values of the AttributeName and isNotNULL fields are extracted from the pg_attribute directory entry corresponding to the values of the input parameters.
SELECT attr.attname, attr.attnotnull FROM pg_attribute attr WHERE attr.attrelid =a_TableOID AND attr.attnum=a_AttributeNo; SELECT rs_isAttributePK,rs_ColumnPKNo FROM admtf_Attribute_PKFeatures (a_TableOID,a_AttributeNo); SELECT rs_isAttributeFK,rs_FKeyName,rs_ColumnFKNo, rs_FKTableName,rs_FKTableColumnName FROM admtf_Attribute_FKFeatures (a_TableOID,a_AttributeNo); The values of the isAttributePK and ColumnPKNo fields are returned by the function admtf_Attribute_PKFeatures .
The values of the fields isAttributeFK , FKeyName , ColumnFKNo , FKTableName , FKTableColumnName are returned by the function admtf_Attribute_FKFeatures .
Calling the function admtf_Attribute_Features ((SELECT OID FROM pg_class WHERE relname = 'street'), 2 :: SMALLINT) will result in the following result.
Table 18. The result of the function execution admtf_Attribute_Features
| AttributeName | UserTypeName | Typename | isNotNULL | isAttributePK | ColumnPKNo |
|---|---|---|---|---|---|
| localityid | localityid | integer | integer | integer | integer |
| Description | isAttributeFK | FKeyName | Columnfkno | FKTableName | FKTableColumnName |
|---|---|---|---|---|---|
| ID of the settlement | t | {fk_street_locality} | {2} | {locality} | {localityid} |
APPENDIX 1. Scripts
Create function admtf_Sequence_Features
Comments on the source code of the function can be found here.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Sequence_Features (a_SchemaName NAME,a_SequenceName NAME); /****************************************************************************/ /* , */ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Sequence_Features (a_SchemaName NAME default 'public', /* */ a_SequenceName NAME default NULL /* */ ) 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$ DECLARE c_SequenceKind CONSTANT CHAR:='S'; v_SequenceOID OID; /* */ v_SequenceName NAME; /* */ v_SequenceDescription TEXT; /* */ v_SequenceStartValue BIGINT; /* */ v_SequenceIncrementBy BIGINT; /* */ v_SequenceMaxValue BIGINT; /* */ v_SequenceLastValue BIGINT; /* */ v_SequenceNumberOfRowCalc INTEGER; /* */ --************************************************************************ BEGIN SELECT INTO rs_SequenceName,rs_SequenceDescription,rs_NumberOfAttribute tbl.relname, COALESCE(dsc.description,'') AS r_SequenceDescription, tbl.relnatts::INTEGER,tbl.relchecks::INTEGER,tbl.relhaspkey, tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::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 nspc.nspname=LOWER(a_SchemaName) AND tbl.relkind=c_SequenceKind AND tbl.relname =LOWER(a_SequenceName); IF FOUND THEN EXECUTE 'SELECT last_value,start_value,increment_by,max_value FROM '||LOWER(a_SchemaName)||'.'||LOWER(a_SequenceName) INTO v_SequenceLastValue,v_SequenceStartValue, v_SequenceIncrementBy,v_SequenceMaxValue ; RETURN QUERY SELECT rs_SequenceName,rs_SequenceDescription, rs_NumberOfAttribute,v_SequenceLastValue, v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue; END IF; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Sequence_Features(a_SchemaName NAME,a_SequenceName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Sequence_Features (a_SchemaName VARCHAR(256),a_SequenceName VARCHAR(256)); /****************************************************************************/ /* , */ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Sequence_Features (a_SchemaName VARCHAR(256) default 'public', /* */ a_SequenceName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (rs_SequenceName VARCHAR(256),rs_SequenceDescription TEXT, rs_NumberOfAttribute INTEGER,rs_SequenceLastValue BIGINT, rs_SequenceStartValue BIGINT,rs_SequenceIncrementBy BIGINT, rs_SequenceMaxValue BIGINT) AS $BODY$ DECLARE c_SequenceKind CONSTANT CHAR:='S'; --******************************************************** BEGIN RETURN 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::BIGINT FROM 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 ' , '; --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); Create function admtf_Table_Sequences
Comments on the source code of the function can be found here.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME); /*********************************************************************/ /* , */ /*********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName NAME default 'public', /* */ a_TableName NAME default NULL /* */ ) 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$ DECLARE v_TableOID INTEGER;/* OID */ v_Sequence RECORD;/* */ v_SequenceOID INTEGER;/* OID */ v_SequenceName NAME; /* */ v_SequenceSchemaName NAME; /* */ v_SequenceDescription TEXT; /* */ v_SequenceStartValue BIGINT; /* */ v_SequenceIncrementBy BIGINT; /* */ v_SequenceMaxValue BIGINT; /* */ v_DependcyType NAME; /* */ /* */ v_AttributeName NAME; /* */ v_SequenceNumber SMALLINT; /* */ c_Delimiter CONSTANT VARCHAR(2):=','; --********************************************************************* BEGIN v_SequenceNumber:=0; FOR v_Sequence IN SELECT pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,', '||da.attname) AS SequenceDescription, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid AND d.refobjsubid=da.attnum WHERE tbl.relkind = 'r' AND pseq.relkind = 'S' AND LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) ORDER BY pseq.relname LOOP v_SequenceNumber:=v_SequenceNumber+1; v_SequenceName:=v_Sequence.SequenceName; v_SequenceSchemaName:=v_Sequence.SequenceSchemaName; v_DependcyType:=v_Sequence.DependcyType; v_AttributeName:=v_Sequence.AttributeName; v_SequenceDescription:=v_Sequence.SequenceDescription; SELECT INTO v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue rs_SequenceStartValue,rs_SequenceIncrementBy, rs_SequenceMaxValue FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName); RETURN QUERY SELECT v_SequenceNumber,v_SequenceName, v_SequenceSchemaName,v_SequenceDescription, v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue,v_DependcyType, a_TableName,a_SchemaName,v_AttributeName; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)); /**********************************************************************/ /* , */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName VARCHAR(256) default 'public', /* */ a_TableName VARCHAR(256) default NULL /* */ ) 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$ DECLARE c_Delimiter CONSTANT VARCHAR(2):=','; --****************************************************** BEGIN RETURN 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 ' , '; --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); admtf_Table_Sequences (PostgreSQL 10)
.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName NAME, a_TableName NAME); /*********************************************************************/ /* , */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName NAME default 'public', /* */ a_TableName NAME default NULL /* */ ) 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$ DECLARE v_TableOID INTEGER; /* OID */ v_Sequence RECORD; /* */ v_SequenceOID INTEGER; /* OID */ v_SequenceName NAME; /* */ v_SequenceSchemaName NAME; /* */ v_SequenceDescription TEXT; /* */ v_SequenceStartValue BIGINT; /* */ v_SequenceIncrementBy BIGINT; /* */ v_SequenceMaxValue BIGINT; /* */ v_DependcyType NAME; /* */ v_AttributeName NAME; /* */ v_SequenceNumber SMALLINT; /* */ c_Delimiter CONSTANT VARCHAR(2):=','; --****************************************************************** BEGIN v_SequenceNumber:=0; FOR v_Sequence IN SELECT pseq.relname AS SequenceName, snsp.nspname AS SequenceSchemaName, COALESCE(dsc.description,', '||da.attname) AS SequenceDescription, d.depType AS DependcyType,da.attname AS AttributeName FROM pg_depend d INNER JOIN pg_class pseq ON d.objid = pseq.oid INNER JOIN pg_namespace snsp ON pseq.relnamespace=snsp.oid LEFT OUTER JOIN pg_Description dsc ON pseq.oid=dsc.objoid AND dsc.objsubid=0 INNER JOIN pg_class tbl ON d.refobjid = tbl.oid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid INNER JOIN pg_attribute da ON da.attrelid= d.refobjid ND d.refobjsubid=da.attnum WHERE tbl.relkind = 'r' AND pseq.relkind = 'S' AND LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) ORDER BY pseq.relname LOOP v_SequenceNumber:=v_SequenceNumber+1; v_SequenceName:=v_Sequence.SequenceName; v_SequenceSchemaName:=v_Sequence.SequenceSchemaName; v_DependcyType:=v_Sequence.DependcyType; v_AttributeName:=v_Sequence.AttributeName; v_SequenceDescription:=v_Sequence.SequenceDescription; SELECT INTO v_SequenceStartValue,v_SequenceIncrementBy,v_SequenceMaxValue rs_SequenceStartValue,rs_SequenceIncrementBy,rs_SequenceMaxValue FROM admtf_Sequence_Features(v_SequenceSchemaName,v_SequenceName); RETURN QUERY SELECT v_SequenceNumber,v_SequenceName, v_SequenceSchemaName,v_SequenceDescription, v_SequenceStartValue,v_SequenceIncrementBy, v_SequenceMaxValue,v_DependcyType, a_TableName,a_SchemaName,v_AttributeName; END LOOP; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Sequences(a_SchemaName NAME, a_TableName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Sequences (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)); /**********************************************************************/ /* , */ /**********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Sequences (a_SchemaName VARCHAR(256) default 'public', /* */ a_TableName VARCHAR(256) default NULL /* */ ) 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$ DECLARE c_Delimiter CONSTANT VARCHAR(2):=','; --******************************************************* BEGIN RETURN 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 ' , '; --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); admfn_Table_RowCount
.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admfn_Table_RowCount (a_SchemaName NAME,a_TableName NAME); /******************************************************/ /* */ /******************************************************/ CREATE OR REPLACE FUNCTION admfn_Table_RowCount (a_SchemaName NAME default 'public',/* */ a_TableName NAME default NULL /* */ ) RETURNS BIGINT AS $BODY$ DECLARE v_TableNumberOfRowCalc BIGINT; /* */ v_Found BOOLEAN; --*********************************************************** BEGIN IF a_SchemaName ~ E'^[a-z_0-9]+$' AND a_TableName ~ E'^[a-z_0-9]+$' THEN EXECUTE 'SELECT count(*) FROM ' ||a_SchemaName ||'.'|| a_TableName INTO v_TableNumberOfRowCalc; ELSE SELECT INTO v_Found true FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid WHERE tbl.relkind='r' AND tbl.relname=a_TableName AND nspc.nspname=a_SchemaName; IF FOUND THEN EXECUTE 'SELECT count(*) FROM ' || CASE WHEN a_SchemaName ~ E'^[a-z_0-9]+$' THEN a_SchemaName ELSE quote_ident(a_SchemaName) END ||'.'|| CASE WHEN a_TableName ~ E'^[a-z_0-9]+$' THEN a_TableName ELSE quote_ident(a_TableName) END INTO v_TableNumberOfRowCalc; ELSE SELECT INTO v_Found true FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid WHERE tbl.relkind='r' AND LOWER(tbl.relname)= LOWER(a_TableName) AND nspc.nspname=LOWER(a_SchemaName); IF FOUND THEN EXECUTE 'SELECT count(*) FROM ' || a_SchemaName ||'.'||a_TableName INTO v_TableNumberOfRowCalc; 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 ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION;BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admfn_Table_RowCount (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************/ /* */ /********************************************************************/ CREATE OR REPLACE FUNCTION admfn_Table_RowCount (a_SchemaName VARCHAR(256) default 'public',/* */ a_TableName VARCHAR(256) default NULL /* */ ) RETURNS BIGINT AS $BODY$ DECLARE v_TableNumberOfRowCalc BIGINT; /* */ --********************************************************* BEGIN RETURN 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 ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt admfn_Table_RowCount('public'::NAME,'Street'::NAME); SELECt admfn_Table_RowCount('public'::VARCHAR(256),'Street'::VARCHAR(256)); admtf_Table_InheritanceChildrens
.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_InheritanceChildrens (a_SchemaName NAME,a_TableName NAME,a_Mode VARCHAR(10)); /************************************************************/ /* */ /************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens (a_SchemaName NAME default 'public', /* */ a_TableName NAME default NULL, /* */ a_Mode VARCHAR(10) default 'estimate' /* */ ) 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'; c_ExactlyMode CONSTANT VARCHAR(10):='exactly'; c_EstimateMode CONSTANT VARCHAR(10):='estimate'; v_TableOID OID; /* */ v_SchemaName NAME; /* */ v_TableName NAME; /* */ v_TableDescription TEXT; /* */ v_TableNumberOfRowCalc INTEGER; /* */ v_InheritanceRECORD RECORD; /* */ v_InheritanceOID OID; /* */ BEGIN RETURN QUERY SELECT rtbl.relname,rdsc.description,rtbl.relnatts::INTEGER, rtbl.relchecks::INTEGER,rtbl.relhaspkey,rtbl.relhasindex, rtbl.relhassubclass, CASE WHEN a_Mode=c_ExactlyMode THEN admfn_Table_RowCount(rnspc.nspname,rtbl.relname)::INTEGER ELSE rtbl.reltuples::INTEGER END 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 INNER JOIN pg_depend dp ON tbl.oid=dp.refobjid INNER JOIN pg_class rtbl ON rtbl.OID=dp.objid INNER JOIN pg_namespace rnspc ON rtbl.relnamespace = rnspc.oid LEFT OUTER JOIN pg_Description rdsc ON rtbl.oid=rdsc.objoid AND rdsc.objsubid=0 WHERE nspc.nspname=LOWER(a_SchemaName) AND tbl.relkind=c_TableKind AND rtbl.relkind=c_TableKind AND 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 ' '; --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)); /************************************************************************/ /* */ /************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_InheritanceChildrens (a_SchemaName VARCHAR(256) default 'public',/* */ a_TableName VARCHAR(256) default NULL,/* */ a_Mode VARCHAR(10) default 'estimate' /* */ ) 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$ DECLARE c_TableKind CONSTANT CHAR:='r'; BEGIN RETURN 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::INTEGER FROM 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 ' '; --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)); admtf_Attribute_PKFeatures
.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Attribute_PKFeatures (a_TableOID OID,a_AttributeNo SMALLINT); /***************************************************************************/ /* . */ /* , */ /* */ /***************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Attribute_PKFeatures (a_TableOID OID, /* */ a_AttributeNo SMALLINT /* */ ) RETURNS TABLE (rs_isAttributePK BOOLEAN,rs_PKeyName name,rs_ColumnPKNo SMALLINT) AS $BODY$ DECLARE C_PKAttributeList_NDims CONSTANT INTEGER:=1; /* */ v_PKAttributeList SMALLINT[]; /* */ v_PKAttributeIndx INTEGER; /* */ v_PKAttributeLBound INTEGER; /* */ v_PKAttributeUBound INTEGER; /* */ --********************************************************************** BEGIN rs_isAttributePK:=false; rs_ColumnPKNo:=NULL; SELECT INTO rs_PKeyName,v_PKAttributeList,rs_isAttributePK conname,conkey,ARRAY[a_AttributeNo]<@conkey FROM pg_constraint c WHERE c.contype='p' and c.conrelid=a_TableOID; IF FOUND AND rs_isAttributePK THEN -- v_PKAttributeLBound:=array_lower(v_PKAttributeList,C_PKAttributeList_NDims); v_PKAttributeUBound:=array_upper(v_PKAttributeList,C_PKAttributeList_NDims); v_PKAttributeIndx:=v_PKAttributeLBound; WHILE v_PKAttributeIndx <= v_PKAttributeUBound AND a_AttributeNo<>v_PKAttributeList[v_PKAttributeIndx] LOOP v_PKAttributeIndx:=v_PKAttributeIndx+1; END LOOP; IF v_PKAttributeIndx<=v_PKAttributeUBound THEN rs_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 ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Attribute_PKFeatures((SELECT OID FROM pg_class WHERE relname='street'),3::SMALLINT); admtf_Attribute_FKFeatures
.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Attribute_FKFeatures (a_TableOID OID,a_AttributeNo SMALLINT); /****************************************************************************/ /* . */ /* , */ /* . */ /****************************************************************************/ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Attribute_FKFeatures (a_TableOID OID, /* */ a_AttributeNo SMALLINT /* */ ) 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; /* */ v_FKAttributeList SMALLINT[]; /* */ v_RefAttributeList SMALLINT[]; /* , */ /* */ v_FKAttributeIndx INTEGER; /* */ v_RefAttributeListIndx INTEGER; /* , */ /* */ v_FKAttributeLBound INTEGER; /* */ v_FKAttributeUBound INTEGER; /* */ v_FKConstraintIndx INTEGER; /* */ v_FKeyName name; /* , */ /* */ v_FKTableName name; /* , */ v_FKTableColumnName name; /* , */ /* */ v_RefAttributeNo SMALLINT; /* , */ /* */ v_Constraint pg_constraint%ROWTYPE; /* */ /* (CONSTRANT) */ --****************************************************************************************************** BEGIN rs_isAttributeFK:=false; rs_ColumnFKNo:=NULL; v_FKConstraintIndx:=0; FOR v_Constraint IN SELECT * FROM pg_constraint c WHERE c.contype='f' and c.conrelid=a_TableOID AND ARRAY[a_AttributeNo]<@conkey ORDER BY c.oid LOOP v_FKConstraintIndx:=v_FKConstraintIndx+1; rs_isAttributeFK:=true; v_FKeyName:=v_Constraint.conname; v_FKAttributeList:=v_Constraint.conkey; v_RefAttributeList:=v_Constraint.confkey; v_FKAttributeLBound:=array_lower(v_FKAttributeList,C_FKAttributeList_NDims); v_FKAttributeUBound:=array_upper(v_FKAttributeList,C_FKAttributeList_NDims); v_FKAttributeIndx:=v_FKAttributeLBound; WHILE v_FKAttributeIndx <= v_FKAttributeUBound AND a_AttributeNo<>v_FKAttributeList[v_FKAttributeIndx] LOOP v_FKAttributeIndx:=v_FKAttributeIndx+1; END LOOP; rs_FKeyName[v_FKConstraintIndx]:=v_FKeyName; rs_ColumnFKNo[v_FKConstraintIndx]:=v_FKAttributeIndx; SELECT INTO v_FKTableName ftbl.relname FROM pg_class ftbl WHERE ftbl.oid=v_Constraint.confrelid; rs_FKTableName[v_FKConstraintIndx]:=v_FKTableName; v_RefAttributeNo:=v_RefAttributeList[v_FKAttributeIndx]; v_FKTableColumnName:=NULL; SELECT INTO v_FKTableColumnName attname FROM pg_attribute a WHERE 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 ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Attribute_FKFeatures((SELECT OID FROM pg_class WHERE relname='street'),4::SMALLINT); admtf_Attribute_Features
.
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Attribute_Features (a_TableOID OID,a_AttributeNo SMALLINT); /****************************************************************************/ /* */ /****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Attribute_Features (a_TableOID OID, /* */ a_AttributeNo SMALLINT/* */ ) 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$ DECLARE v_Return_Error Integer := 0; /* */ --********************************************************************* BEGIN SELECT INTO rsa_AttributeName,rsa_UserTypeName,rsa_TypeName, rsa_isNotNULL,rsa_Description attr.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_Description FROM pg_attribute attr LEFT OUTER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE attr.attrelid =a_TableOID AND attr.attnum=a_AttributeNo; SELECT INTO rsa_isAttributePK,rsa_ColumnPKNo rs_isAttributePK,rs_ColumnPKNo FROM 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_FKTableColumnName FROM admtf_Attribute_FKFeatures(a_TableOID,a_AttributeNo); RETURN QUERY SELECT rsa_AttributeName,rsa_UserTypeName,rsa_TypeName,rsa_isNotNULL, rsa_isAttributePK,rsa_ColumnPKNo,rsa_Description,rsa_isAttributeFK, rsa_FKeyName,rsa_ColumnFKNo,rsa_FKTableName,rsa_FKTableColumnName; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Attribute_Features(a_TableOID OID,a_AttributeNo SMALLINT) IS ' '; --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. .
Functions for documenting PostgreSQL databases. The end (part four) .
Source: https://habr.com/ru/post/418597/
All Articles