Functions for documenting PostgreSQL databases. Ending
This is the fourth and last part of the article, which describes user functions for working with system directories: pg_class, pg_attribute, pg_constraints, etc. The first , second and third parts of the article are published earlier.
Fig. 4. Functions that admtf_PrimaryKey_ComplexFeatures calls.
Table 20. Assignment of functions.
')
Table 21. Result of execution of the function admtf_PrimaryKey_ComplexFeatures ('public', 'xpkstreet').
Features of the connection records used by system directories are discussed in detail in the section “The function admtf_Table_Constraintes list of database table constraints and their characteristics” .
Fig. 5. Functions that admtf_ForeignKey_ComplexFeatures calls.
Table 22. Assignment of functions.
<strongTable 23. The result of the function admtf_ForeignKey_ComplexFeatures ('public', 'fk_street_locality', 3).
Features of the connection records used by the system catalogs are discussed in detail in the section “The function admtf_Table_Constraintes - a list of limitations of the database table and their characteristics” .
Fig. 6. Functions that admtf_Index_ComplexFeatures calls.
Table 24. Assignment of functions.
Table 25. Result of execution of the function admtf_Index_ComplexFeatures ('public', 'xie9street', 7).
Table 26. Result of execution of the function admtf_Table_ComplexFeatures ('public', 'street').
Comments on the source code of the function can be found here.
Comments on the source code of the function can be found here.
Comments on the source code of the function can be found here.
Comments on the source code of the function can be found here , as well as in the first part of the article in the sections "Structure of the head function" and "What advanced characteristics are we talking about?" .
I anticipate that I must apologize in advance to those readers who are interested only in the device configuration of the PostgrSQL system directories, as well as in the techniques for extracting data from them. The functions that are described in this part of the article do not apply to the system directories that have not yet been reviewed, and the methods for extracting data are no different from those described in the previous parts. Such readers can finish viewing the article right here.
To all those who decide to continue reading, I inform you that this part of the article discusses functions that return the extended characteristics of primary and foreign keys , as well as indexes of tables. And of course, there is a brief description and code for the function admtf_Table_ComplexFeatures , which was stated as the purpose of the publication in the first part of the article .
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 the list of characteristics of the primary key of the table
Fig. 4. Functions that admtf_PrimaryKey_ComplexFeatures calls.
Table 20. Assignment of functions.
')
Text version of the table in the figure
| No | Title | Purpose |
|---|---|---|
| one | admtf_PrimaryKey_Features | The function returns the characteristics of the primary key (PRIMARY KEY) table |
| 2 | admtf_PrimaryKey_Attributes | The function returns a list of primary key attributes (PRIMARY KEY) and their characteristics. |
| 3 | admtf_PrimaryKey_ComplexFeatures | The function returns the characteristics of the primary key (PRIMARY KEY) of the table, as well as a list of the key attributes that make up the key. |
The function admtf_PrimaryKey_ComplexFeatures - a comprehensive list of the characteristics of the primary key of the table
Table 21. Result of execution of the function admtf_PrimaryKey_ComplexFeatures ('public', 'xpkstreet').
Text version of the table in the figure
| Category | No | Title | Comment | type of | Base type | ? not NULL |
|---|---|---|---|---|---|---|
| pkl | 0 | xpkstreet | Primary key table street | |||
| pkatt | one | wcrccode | Country code | wcrccode | smallint | t |
| pkatt | 2 | localityid | ID of the settlement | localityid | integer | t |
| pkatt | 3 | streetid | ID street of the village | streetid | smallint | t |
As the required parameters, the function takes the name of the primary key ( a_PrimaryKeyName ) and the name of the schema within which the table is created ( a_SchemaName ). The function code is a sequential invocation of two table functions.
Source code can be viewed and downloaded here.
The first function ( admtf_PrimaryKey_Features ) prepares and executes a SELECT that returns the characteristics of the primary key.
source code operator in the figure
SELECT con.conname, COALESCE(dsc.description,' '|| tbl.relname) FROM pg_constraint con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_class tbl ON con.conrelid=tbl.oid LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid AND dsc.objsubid=0 WHERE con.contype ='p' AND nspc.nspname=LOWER(a_SchemaName) AND con.conname =LOWER(a_PrimaryKeyName); The second function ( admtf_PrimaryKey_Attributes ) returns the characteristics of the attributes that make up the primary key.
source code operator in the figure
SELECT (rank() OVER (PARTITION BY con.conrelid ORDER BY con.No)) ::SMALLINT, attr.attnum,attr.attname::NAME, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::VARCHAR(100) ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::VARCHAR(256), attr.attnotnull FROM (SELECT c.oid, c.conrelid,c.connamespace,c.confrelid,c.conname, c.contype, c.conkey::SMALLINT[],generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid AND attr.attnum=con.conkey[con.No] LEFT OUTER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_type btyp ON typ.typbasetype=btyp.oid WHERE con.contype ='p' AND nspc.nspname=LOWER(a_SchemaName) AND con.conname =LOWER(a_PrimaryKeyName) ORDER BY con.No; Here you should pay attention to the order of displaying records about the attributes of the primary key. They are displayed in the order of description in the primary key ( con.No ), and not in the order of their description in the table ( attr.attnum ).
Features of the connection records used by system directories are discussed in detail in the section “The function admtf_Table_Constraintes list of database table constraints and their characteristics” .
The structure of the function that returns the list of foreign key characteristics of the table
Fig. 5. Functions that admtf_ForeignKey_ComplexFeatures calls.
Table 22. Assignment of functions.
Text version of the table in the figure
| No | Title | Purpose |
|---|---|---|
| one | admtf_ ForeignKey _Features | The function returns the foreign key characteristics (FOREIGN KEY) of the table. |
| 2 | admtf_ ForeignKey_Attributes | The function returns a list of attributes of the foreign key of the table and their characteristics. |
| 3 | admtf_ForeignKey_ReferenceTableFeatures | The function returns a list of characteristics of the database table referenced by the foreign key. |
| four | admtf_ForeignKey_ReferenceTableAttributes | The function returns a list of attributes of the database table referenced by the foreign key and their characteristics. |
| five | admtf_ForeignKey_ReferenceTableComplexFeatures | The function returns a complete (extended) list of characteristics of the database table referenced by the foreign key. |
| 6 | admtf_ForeignKey_ComplexFeatures | The function returns the foreign key characteristics (FOREIGN KEY) of the table, as well as a list of attributes included in the index. |
The function admtf_ForeignKey_ComplexFeatures is a comprehensive list of foreign key characteristics of a table.
The admtf_ForeignKey_ComplexFeatures function returns a list of the following foreign key characteristics of a table.
<strongTable 23. The result of the function admtf_ForeignKey_ComplexFeatures ('public', 'fk_street_locality', 3).
Text version of the table in the figure
| Category | No | Title | Comment | type of | Base type | ? not NULL |
|---|---|---|---|---|---|---|
| fk03 | 3 | fk_street_locality | Foreign key table street | |||
| fk03att | one | wcrccode | Country code | wcrccode | smallint | t |
| fk03att | 2 | localityid | ID of the settlement | localityid | integer | t |
| fk03rtbl | 0 | locality | List of locations | |||
| fk03ratt | one | wcrccode | Country code | wcrccode | smallint | t |
| fk03ratt | 2 | localityid | ID of the settlement | localityid | integer | t |
As parameters, the function takes the name of the foreign key ( a_ ForeignKey ) and the name of the scheme within which the foreign key ( a_SchemaName ) is created.
Source code can be viewed and downloaded here.
The function has one more optional parameter — the index number of the table ( a_ForeignKeyNo ). This parameter is needed to add the sequence number of the foreign key of the table to the category values. In particular, in the above example, the function was executed with the value of this parameter equal to 3 . Therefore, an entry with foreign key characteristics is marked with the value “ fk03 ”, entries with attribute characteristics are “ fk03att ”, an entry about the external table is “ fk03rtbl ”, and an entry about the attributes of the external table is “ fk03ratt ”. If this parameter were omitted when calling the function, the category values ​​in the entries would be “ fk ”, “ fkatt ”, “ fkrtbl ” and “ fkratt ”, respectively. For the same reason, the category value is formed inside the function admtf_ForeignKey_ComplexFeatures , and not in the code of the function calling it.
For details, see “What are the advanced features in question?”
.Function code is a sequential call of three table functions.
The first function ( admtf_ForeignKey_Features ) prepares and executes a SELECT that returns the characteristics of the foreign key.
source code operator in the figure
SELECT con.conname, COALESCE(dsc.description,' '|| tbl.relname) FROM pg_constraint con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_class tbl ON con.conrelid=tbl.oid LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid AND dsc.objsubid=0 WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype='f' AND con.conname =LOWER(a_ForeignKeyName); The second function ( admtf_ForeignKey_Attributes ) returns the characteristics of foreign key attributes.
Here you should pay attention to the order of displaying records about foreign key attributes. They are displayed in the order of description in the foreign key ( con.No ), and not in the order of their description in the table ( attr.attnum ).
Features of the connection records used by the system catalogs are discussed in detail in the section “The function admtf_Table_Constraintes - a list of limitations of the database table and their characteristics” .
source code operator in the figure
SELECT (rank() OVER (PARTITION BY con.conrelid ORDER BY con.No))::SMALLINT AS r_ForeingKeyNo, attr.attnum AS r_AttributeNumber,attr.attname::NAME AS r_AttributeName, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE '' END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME AS r_TypeName, attr.attnotnull AS r_isNotNULL,TRIM(dsc.description) AS r_Description FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.connamespace,c.contype,c.conkey::SMALLINT[],c.consrc, c.confkey::SMALLINT[],generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid AND attr.attnum=con.conkey[con.No] INNER 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 nspc.nspname=LOWER(a_SchemaName) AND con.contype ='f' AND con.conname =LOWER(a_ForeignKeyName) ORDER BY con.No; The third function ( admtf_ForeignKey_ReferenceTableComplexFeatures ) returns the characteristics of the table referenced by the foreign key. To solve its problem, it sequentially calls two additional functions.
The function admtf_ForeignKey_ReferenceTableComplexFeatures is a comprehensive list of table characteristics referenced by a foreign key.
As parameters, the function takes the name of the foreign key ( a_ForeignKey ) and the name of the scheme within which the foreign key is created ( a_SchemaName ).
The function has one more optional parameter — the index number of the table ( a_ForeignKeyNo ). This parameter is needed in order to replace the category number with the sequence number character '%' in " fk% rtbl " and " fk% ratt ", respectively.
The function sequentially calls two additional functions.
The first admtf_ForeignKey_ReferenceTableFeatures returns directly the characteristics of the table referenced by the foreign key, and is a simplified version of the function admtf_Table_Features .
The second admtf_ForeignKey_ReferenceTableAttributes is the characteristics of the attributes of the external table that correspond to the attributes of the foreign key. It almost completely repeats the function code admtf_ForeignKey_Attributes . Only in some places, con.confrelid is used instead of the identifier con.conrelid , and con.confkey is used instead of the con.conkey array .
source code operator in the figure
SELECT (rank() OVER (PARTITION BY con.confrelid ORDER BY con.No))::SMALLINT, attr.attnum AS r_AttributeNumber,attr.attname::NAME AS r_AttributeName, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME, attr.attnotnull AS r_isNotNULL,TRIM(dsc.description) AS r_Description FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.connamespace,c.contype, c.conkey::SMALLINT[],c.consrc,c.confkey::SMALLINT[], generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_attribute attr ON attr.attrelid=con.confrelid AND attr.attnum=con.confkey[con.No] INNER 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 nspc.nspname=LOWER(a_SchemaName) AND con.contype ='f' AND con.conname =LOWER(a_ForeignKeyName) ORDER BY con.No; The structure of the function that returns a list of table index characteristics
Fig. 6. Functions that admtf_Index_ComplexFeatures calls.
Table 24. Assignment of functions.
Text version of the table in the figure
| No | Title | Purpose |
|---|---|---|
| one | admtf_Index_Features | The function returns the characteristics of the index table. |
| 2 | admtf_Index_Attributes | The function returns a list of attributes of the table included in the index, and their characteristics. |
| 3 | admtf_Index_ComplexFeatures | The function returns the index characteristics of the table, as well as a list of attributes included in the index. |
The function admtf_Index_ComplexFeatures - a comprehensive list of table index characteristics
The admtf_Index_ComplexFeatures function returns a list of the following table index characteristics.
Table 25. Result of execution of the function admtf_Index_ComplexFeatures ('public', 'xie9street', 7).
Text version of the table in the figure
| Category | No | Title | Order | Comment | type of | Base type | ? not NULL |
|---|---|---|---|---|---|---|---|
| idx07 | 7 | xie9street | Index by street name of a settlement in descending order | ||||
| idx07att | one | wcrccode | ASC | Country code | wcrccode | smallint | t |
| idx07att | 2 | localityid | ASC | ID of the settlement | localityid | integer | t |
| idx07att | 3 | streetname | Desc | Street name of the settlement | VARCHAR (150) | t |
As parameters, the function takes the name of the index ( a_ Index ) and the name of the scheme within which the index is created ( a_SchemaName ).
Source code can be viewed and downloaded here.
The function has one more optional parameter - the index index index number ( a_IndexNo ). This parameter is needed in order to add the index number of the table to the category values. In particular, in the above example, the function was executed with the value of this parameter equal to 7 . Therefore, an entry with index characteristics is marked with the value “ idx07 ”, and an entry with attribute characteristics is “ idx07att ”. If this parameter were omitted when calling the function, the category values ​​in the entries would be “ idx ” and “ idxatt ”, respectively.
For details, see the section “What advanced features are we talking about?” . For the same reason, the category value is formed inside the function admtf_Index_ComplexFeatures , and not in the code of the function calling it.
The function code is a sequential invocation of two table functions.
The first function ( admtf_Index_Features ) prepares and executes a SELECT that returns the characteristics of the index.
source code operator in the figure
SELECT inxcls.relname, CASE WHEN COALESCE(TRIM(dsc.description),'')='' THEN '' || CASE WHEN inx.indisunique THEN ' ' || CASE WHEN inx.indisprimary THEN '( )' ELSE '' END ELSE '' END ||CASE WHEN inxam.amname='gist' THEN ' ' ELSE '' END ||' ' ||tbl.relname ELSE dsc.description END FROM pg_index inx INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid INNER JOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid LEFT OUTER JOIN pg_Description dsc ON inxcls.oid=dsc.objoid AND dsc.objsubid=0 LEFT OUTER JOIN pg_am inxam ON inxcls.relam=inxam.oid LEFT OUTER JOIN pg_class tbl ON inx.indrelid=tbl.oid WHERE inxcls.relkind='i' AND nsp.nspname =LOWER(a_SchemaName) AND inxcls.relname=LOWER(a_IndexName); The second function ( admtf_ Index_Attributes ) returns the characteristics of the attributes included in the index. Please note that the order of the attribute records is determined by the order of their description in the index ( inx.No ), and not by the order of physical following in the table ( attr.attnum ).
source code operator in the figure
SELECT (inx.No+1)::SMALLINT,attr.attnum::SMALLINT, attr.attname::NAME, CASE WHEN NOT inxam.amcanorder THEN NULL ELSE CASE WHEN inx.indoption[inx.No] & 1=1 THEN 'DESC' ELSE 'ASC' END END::VARCHAR(10), CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE '' END, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-),attr.atttypmod))::NAME, attr.attnotnull,dsc.description FROM (SELECT i.indrelid, i.indexrelid,i.indkey::SMALLINT[], i.indoption::SMALLINT[], generate_subscripts(i.indkey, 1) as No FROM pg_index i) inx INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid INNER JOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid LEFT OUTER JOIN pg_am inxam ON inxcls.relam=inxam.oid LEFT OUTER JOIN pg_class tbl ON inx.indrelid=tbl.oid INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid AND attr.attnum=inx.indkey[inx.No] 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 nsp.nspname=LOWER(a_SchemaName) AND inxcls.relkind='i' AND inxcls.relname =LOWER(a_IndexName) ORDER BY nsp.nspname,inxcls.relname,inx.No; Features of the connection records used by system directories are discussed in detail in the section “The function admtf_Table_Indexes list of database table indexes and their characteristics.
Create function admtf_Table_ComplexFeatures
The admtf_Table_ComplexFeatures function returns a comprehensive list of database table characteristics, which includes the characteristics returned by the functions described in the article. As parameters, the function takes the name of the source table ( a_TableName ) and the name of the schema within which the table is created ( a_SchemaName ).
Source code can be viewed and downloaded here.
Table 26. Result of execution of the function admtf_Table_ComplexFeatures ('public', 'street').
Text version of the table in the figure
| Category | No | Title | Comment | type of | Base type | ? not NULL |
|---|---|---|---|---|---|---|
| tbl | 0 | street | List of streets in settlements | |||
| att | one | wcrccode | Country code | wcrccode | smallint | t |
| att | 2 | localityid | ID of the settlement | localityid | integer | t |
| att | 3 | streetid | ID street of the village | streetid | smallint | t |
| att | four | streettypeacrm | Street type acronym | streettypeacrm | character (8) | f |
| att | five | streetname | Street name | streettypeacrm | varchar (150) | t |
| pk | 0 | xpkstreet | Primary key table street | |||
| pkatt | one | wcrccode | Country code | wcrccode | smallint | t |
| fk01 | one | fk_street_locality | Foreign key table | |||
| fk02 | 2 | fk_street_streettype | Foreign key table | |||
| idx01 | one | xie1street | Index on the type and name of the street settlements | |||
| idx02 | 2 | xie2street | Index on the name of the street settlements | |||
| idx03 | 3 | xie3street | Index of street names of all settlements | |||
| idx04 | four | xpkstreet | Index unique (primary key) of the street table |
In the course of its execution, the function sequentially calls 9 additional functions, a list of which is given in the section “Structure of the head function” .
The joint implementation of the head and additional functions results in the creation of a table with advanced characteristics of the table .
Where are the functions used?
The functions described in the article were created in the process of preparing an application for state registration of the database. The application procedure and the requirements for its execution are set forth in the document “Rules for filing an application for state registration of a program for electronic computers or a database”, approved by Order No. 211 of the Ministry of Economic Development of Russia dated April 5, 2016 (hereinafter referred to as the Rules).
The mandatory part of the application is the document "Materials identifying the database." The rules interpret the contents of this document as follows.
“Materials identifying the database should reflect the objective form of representing the totality of the independent materials contained in it as examples of real content and the principles of their systematization (database structure), which allow finding and processing these materials using a computer.”
In other words, the document should contain a description of the database structure and examples of its actual content.
As can be seen from the figure, when preparing an application for database registration, it was not only the functions described in this article that were used. Additionally, 3-4 functions were created to convert the descriptions of database tables into the PlantUML format. More precisely, these functions create code in the format of a plug- in for the TRAC project management system , so if you want to check the code created by these functions, do not forget to remove two lines from the top before @startuml and all lines below after @enduml .
PlantUML script generated by functions
{{{ #!plantuml @startuml object public.ID_DISTRICTS{ id_np : integer NOT NULL (PK1)(FK1 id_nps(id_np)) id_district : integer NOT NULL (PK2) name_district : character varying(25) NULL type_district : character varying(25) NULL okato : character varying(11) NULL oktmo : character varying(11) NULL } object public.ID_NPS{ id_region : integer NOT NULL (FK1 id_regions(id_region)) id_atu : integer NULL (FK1 id_rayons(id_atu)) id_selsov : integer NULL (FK1 id_selsovs(id_selsov)) id_np : integer NOT NULL (PK1) name_np : character varying(25) NULL type_np : character varying(25) NULL (FK1 type_np(scname)) okato : character varying(11) NULL oktmo : character varying(11) NULL } public.ID_DISTRICTS *-- public.ID_NPS legend center <b><i><u></u></i></b> <b>ID_DISTRICTS</b>- - <b>ID_NPS</b>- - endlegend @enduml }}} ---- PS Why aren't additional functions listed here for converting the description of database tables to the PlantUML plugin format for the TRAC project management system? First, they did not fit into the stated topic. Secondly, it seems, I tired the readers with the texts of functions. But if someone is interested in these functions, then write to me and I will send their texts.
see also
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Two ;
Functions for documenting PostgreSQL databases. Part Three
APPENDIX 1. Scripts
Create function admtf_PrimaryKey_ComplexFeatures
Comments on the source code of the function can be found here.
function code
Create function admtf_PrimaryKey_Features
function code
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_PrimaryKey_Features (a_SchemaName NAME,a_PrimaryKeyName NAME); /******************************************************************************/ /* , */ /* */ /******************************************************************************/ CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Features (a_SchemaName NAME default 'public', /* */ a_PrimaryKeyName NAME default NULL /* */ ) RETURNS TABLE (rs_PrimaryKeyName NAME,rs_PrimaryKeyDescription TEXT) AS $BODY$ DECLARE c_PrimaryKeyKind CONSTANT CHAR:='p'; v_PrimaryKeyOID OID; /* */ v_PrimaryKeyName NAME; /* */ v_PrimaryKeyDescription TEXT; /* */ v_MasterTableName NAME; /* , */ --******************************************************************* BEGIN SELECT INTO rs_PrimaryKeyName,rs_PrimaryKeyDescription con.conname,COALESCE(dsc.description,' '|| tbl.relname) FROM pg_constraint con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_class tbl ON con.conrelid=tbl.oid LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid AND dsc.objsubid=0 WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype =c_PrimaryKeyKind AND con.conname =LOWER(a_PrimaryKeyName); RETURN QUERY SELECT rs_PrimaryKeyName,rs_PrimaryKeyDescription; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_PrimaryKey_Features(a_SchemaName NAME,a_PrimaryKeyName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_PrimaryKey_Features (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)); /******************************************************************************/ /* , */ /* */ /******************************************************************************/ CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Features (a_SchemaName VARCHAR(256) default 'public', /* */ a_PrimaryKeyName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (rs_PrimaryKeyName VARCHAR(256),rs_PrimaryKeyDescription TEXT) AS $BODY$ DECLARE c_PrimaryKeyKind CONSTANT CHAR:='p'; --****************************************************************** BEGIN RETURN QUERY SELECT pkf.rs_PrimaryKeyName::VARCHAR(256), pkf.rs_PrimaryKeyDescription::TEXT FROM admtf_PrimaryKey_Features(a_SchemaName::NAME,a_PrimaryKeyName::NAME) pkf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_PrimaryKey_Features(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_PrimaryKey_Features('public'::NAME,'xpkstreet'::NAME); SELECt * FROM admtf_PrimaryKey_Features('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256)); Create function admtf_PrimaryKey_Attributes
function code
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_PrimaryKey_Attributes (a_SchemaName NAME,a_PrimaryKeyName NAME); /********************************************************************/ /* */ /********************************************************************/ CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Attributes (a_SchemaName NAME default 'public', /* */ a_PrimaryKeyName NAME default NULL /* */ ) RETURNS TABLE (r_PrimaryKeyNo SMALLINT,r_AttributeNumber SMALLINT, r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME, r_isNotNULL BOOLEAN,r_Description Text) AS $BODY$ DECLARE c_PrimaryKeyKind CONSTANT CHAR:='p'; v_PrimaryKeyOID OID; /* */ v_PrimaryKeyName NAME; /* */ v_PrimaryKeyDescription TEXT; /* */ v_MasterTableName NAME; /* , */ v_PrimaryKeyArray SMALLINT[]; /* */ v_MasterTableOID OID; /* , */ v_AttributeNumber SMALLINT; /* */ v_PKAttributeCount SMALLINT; /* */ v_AttNo SMALLINT; /* */ --********************************************************************** BEGIN RETURN QUERY SELECT (rank() OVER (PARTITION BY con.conrelid ORDER BY attr.attnum))::SMALLINT AS r_PrimaryKeyNo, attr.attnum AS r_AttributeNumber,attr.attname::NAME AS r_AttributeName, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''::NAME END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME AS r_TypeName, attr.attnotnull AS r_isNotNULL, TRIM(dsc.description) AS r_Description FROM (SELECT c.oid, c.conrelid,c.connamespace,c.confrelid,c.conname, c.contype,c.conkey::SMALLINT[], consrc, c.confkey::SMALLINT[],generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid AND attr.attnum=con.conkey[con.No] 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 con.contype=c_PrimaryKeyKind AND LOWER(nspc.nspname)=LOWER(a_SchemaName) AND LOWER(con.conname)=LOWER(a_PrimaryKeyName) ORDER BY attr.attnum; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_PrimaryKey_Attributes(a_SchemaName NAME,a_PrimaryKeyName NAME) IS ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_PrimaryKey_Attributes (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)); /********************************************************************/ /* */ /********************************************************************/ CREATE OR REPLACE FUNCTION admtf_PrimaryKey_Attributes (a_SchemaName VARCHAR(256) default 'public', /* */ a_PrimaryKeyName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (r_PrimaryKeyNo SMALLINT,r_AttributeNumber SMALLINT,r_AttributeName VARCHAR(256),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN,r_Description TEXT) AS $BODY$ DECLARE c_PrimaryKeyKind CONSTANT CHAR:='p'; --******************************************************************* BEGIN RETURN QUERY SELECT pka.r_PrimaryKeyNo::SMALLINT,pka.r_AttributeNumber::SMALLINT, pka.r_AttributeName::VARCHAR(256),pka.r_UserTypeName::VARCHAR(256), pka.r_TypeName::VARCHAR(256),pka.r_isNotNULL::BOOLEAN, pka.r_Description::TEXT FROM admtf_PrimaryKey_Attributes(a_SchemaName::NAME,a_PrimaryKeyName::NAME) pka; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_PrimaryKey_Attributes(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS ' '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_PrimaryKey_Attributes('public'::NAME,'xpkstreet'::NAME); SELECt * FROM admtf_PrimaryKey_Attributes('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256)); BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_PrimaryKey_ComplexFeatures (a_SchemaName NAME,a_PrimaryKeyName NAME); /*****************************************************************************/ /* , */ /* , */ /*****************************************************************************/ CREATE OR REPLACE FUNCTION admtf_PrimaryKey_ComplexFeatures (a_SchemaName NAME default 'public', /* */ a_PrimaryKeyName NAME default NULL /* */ ) RETURNS TABLE (rpk_FeatureCategory VARCHAR(10),rpk_FeatureNumber SMALLINT,rpk_FeatureName NAME,rpk_FeatureDescription TEXT, rpk_UserTypeName NAME,rpk_TypeName NAME,rpk_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_PrimaryKeyCategory CONSTANT VARCHAR(10):='pk'; /* */ /* */ c_AttributeCategory CONSTANT VARCHAR(10):='pkatt'; /* */ /* */ v_PrimaryKeyOID OID; /* */ v_PrimaryKeyName NAME; /* */ v_PrimaryKeyDescription TEXT; /* */ v_FeatureCategory VARCHAR(10); /* */ v_FeatureNumber SMALLINT; /* */ --*********************************************************************** BEGIN v_FeatureCategory:=c_PrimaryKeyCategory; v_FeatureNumber:=0; SELECT INTO v_PrimaryKeyName,v_PrimaryKeyDescription rs_PrimaryKeyName,rs_PrimaryKeyDescription FROM admtf_PrimaryKey_Features(a_SchemaName,a_PrimaryKeyName); IF FOUND AND v_PrimaryKeyName IS NOT NULL THEN RETURN QUERY SELECT v_FeatureCategory,v_FeatureNumber,v_PrimaryKeyName, v_PrimaryKeyDescription, NULL::NAME AS rpk_UserTypeName, NULL::NAME AS rpk_TypeName, NULL::BOOLEAN AS rpk_isNotNULL; v_FeatureCategory:=c_AttributeCategory; v_FeatureNumber:=0; RETURN QUERY SELECT v_FeatureCategory,r_PrimaryKeyNo,r_AttributeName,r_Description, r_UserTypeName,r_TypeName,r_isNotNULL FROM admtf_PrimaryKey_Attributes(a_SchemaName,a_PrimaryKeyName); END IF; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_PrimaryKey_ComplexFeatures(a_SchemaName NAME,a_PrimaryKeyName NAME) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_PrimaryKey_ComplexFeatures (a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)); /******************************************************************************/ /* , */ /* , */ /******************************************************************************/ CREATE OR REPLACE FUNCTION admtf_PrimaryKey_ComplexFeatures (a_SchemaName VARCHAR(256) default 'public', /* */ a_PrimaryKeyName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (rpk_FeatureCategory VARCHAR(10),rpk_FeatureNumber SMALLINT,rpk_FeatureName VARCHAR(256),rpk_FeatureDescription TEXT, rpk_UserTypeName VARCHAR(256),rpk_TypeName VARCHAR(256),rpk_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_PrimaryKeyCategory CONSTANT VARCHAR(10):='pk'; /* */ /* */ --************************************************************************* BEGIN RETURN QUERY SELECT pk.rpk_FeatureCategory::VARCHAR(10), pk.rpk_FeatureNumber::SMALLINT, pk.rpk_FeatureName::VARCHAR(256),pk.rpk_FeatureDescription::TEXT, pk.rpk_UserTypeName::VARCHAR(256),pk.rpk_TypeName::VARCHAR(256), pk.rpk_isNotNULL::BOOLEAN FROM admtf_PrimaryKey_ComplexFeatures(a_SchemaName::NAME, a_PrimaryKeyName::NAME) pk; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_PrimaryKey_ComplexFeatures(a_SchemaName VARCHAR(256),a_PrimaryKeyName VARCHAR(256)) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_PrimaryKey_ComplexFeatures('public'::NAME,'xpkstreet'::NAME); SELECt * FROM admtf_PrimaryKey_ComplexFeatures('public'::VARCHAR(256),'xpkstreet'::VARCHAR(256)); Creating the function admtf_ForeignKey_ComplexFeatures
Comments on the source code of the function can be found here.
function code
.
admtf_ForeignKey_Features
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_Features (a_SchemaName NAME,a_ForeignKeyName NAME); /**************************************************************/ /* , */ /* */ /**************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_Features (a_SchemaName NAME default 'public', /* */ a_ForeignKeyName NAME default NULL /* */ ) RETURNS TABLE (rs_ForeignKeyName NAME,rs_ForeignKeyDescription TEXT) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; v_ForeignKeyOID OID; /* */ v_ForeignKeyName NAME; /* */ v_ForeignKeyDescription TEXT; /* */ v_MasterTableName NAME; /* , */ --************************************************************************ BEGIN SELECT INTO rs_ForeignKeyName,rs_ForeignKeyDescription con.conname,COALESCE(dsc.description,' '|| tbl.relname) FROM pg_constraint con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_class tbl ON con.conrelid=tbl.oid LEFT OUTER JOIN pg_Description dsc ON con.oid=dsc.objoid AND dsc.objsubid=0 WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype =c_ForeignKeyKind AND con.conname =LOWER(a_ForeignKeyName); RETURN QUERY SELECT rs_ForeignKeyName,rs_ForeignKeyDescription; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_Features(a_SchemaName NAME,a_ForeignKeyName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_Features (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)); /********************************************************************************************************/ /* , */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_Features (a_SchemaName VARCHAR(256) default 'public', /* */ a_ForeignKeyName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (rs_ForeignKeyName VARCHAR(256),rs_ForeignKeyDescription TEXT) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; --******************************************************************* BEGIN RETURN QUERY SELECT fkf.rs_ForeignKeyName::VARCHAR(256), fkf.rs_ForeignKeyDescription::TEXT FROM admtf_ForeignKey_Features (a_SchemaName::NAME,a_ForeignKeyName::NAME) fkf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_Features(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_ForeignKey_Features('public'::VARCHAR(256),'fk_street_locality'::VARCHAR(256)); SELECt * FROM admtf_ForeignKey_Features('public'::NAME,'fk_street_locality'::NAME); admtf_ForeignKey_Attributes
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_Attributes (a_SchemaName NAME,a_ForeignKeyName NAME); /**************************************************************/ /* , */ /* */ /**************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_Attributes (a_SchemaName NAME default 'public', /* */ a_ForeignKeyName NAME default NULL /* */ ) RETURNS TABLE (r_ForeignKeyNo SMALLINT,r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN,r_Description Text) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; --**************************************************************** BEGIN RETURN QUERY SELECT (rank() OVER (PARTITION BY con.conrelid ORDER BY con.No))::SMALLINT AS r_ForeingKeyNo, attr.attnum AS r_AttributeNumber,attr.attname::NAME AS r_AttributeName, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1),attr.atttypmod))::NAME AS r_TypeName, attr.attnotnull AS r_isNotNULL,TRIM(dsc.description) AS r_Description FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.connamespace, c.contype,c.conkey::SMALLINT[],c.consrc, c.confkey::SMALLINT[], generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_attribute attr ON attr.attrelid=con.conrelid AND attr.attnum=con.conkey[con.No] INNER 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 nspc.nspname=LOWER(a_SchemaName) AND con.contype =c_ForeignKeyKind AND con.conname =LOWER(a_ForeignKeyName) ORDER BY con.No; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_Attributes(a_SchemaName NAME,a_ForeignKeyName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_Attributes (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)); /**************************************************************/ /* , */ /* */ /**************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_Attributes (a_SchemaName VARCHAR(256) default 'public', /* */ a_ForeignKeyName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (r_ForeignKeyNo SMALLINT,r_AttributeNumber SMALLINT,r_AttributeName VARCHAR(256),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN,r_Description Text) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; --***************************************************************** BEGIN RETURN QUERY SELECT fka.r_ForeignKeyNo::SMALLINT,fka.r_AttributeNumber::SMALLINT, fka.r_AttributeName::VARCHAR(256), fka.r_UserTypeName::VARCHAR(256),fka.r_TypeName::VARCHAR(256), fka.r_isNotNULL::BOOLEAN,fka.r_Description::TEXT FROM admtf_ForeignKey_Attributes(a_SchemaName::NAME,a_ForeignKeyName::NAME) fka; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_Attributes(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_ForeignKey_Attributes('public'::NAME,'fk_mapHouse_MapStreet'::NAME); SELECt * FROM admtf_ForeignKey_Attributes('public'::NAME,'fk_street_locality'::NAME); SELECt * FROM admtf_ForeignKey_Attributes('public'::NAME,'fk_street_streettype'::NAME); SELECt * FROM admtf_ForeignKey_Attributes('public'::VARCHAR(256),'fk_street_locality'::VARCHAR(256)); SELECt * FROM admtf_ForeignKey_Attributes('public'::VARCHAR(256),'fk_street_streettype'::VARCHAR(256)); admtf_ForeignKey_ReferenceTableComplexFeatures
.
admtf_ForeignKey_ReferenceTableFeatures
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableFeatures (a_SchemaName NAME,a_ForeignKeyName NAME); /*******************************************************************/ /* , */ /* */ /*******************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableFeatures (a_SchemaName NAME default 'public', /* */ a_ForeignKeyName NAME default NULL /* */ ) RETURNS TABLE (rfkrt_ReferenceTableName NAME,rfkrt_ReferenceTableDescription TEXT) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; v_ReferenceTableOID OID; /* , */ v_ReferenceTableName NAME; /* , */ v_ReferenceTableDescription TEXT; /* , */ v_MasterTableName NAME; /* , */ --******************************************************************* BEGIN SELECT INTO v_ReferenceTableName rtbl.relname FROM pg_constraint con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_class rtbl ON con.confrelid=rtbl.oid WHERE nspc.nspname=LOWER(a_SchemaName) AND con.contype =c_ForeignKeyKind AND con.conname =LOWER(a_ForeignKeyName); IF FOUND THEN RETURN QUERY SELECT rs_TableName,rs_TableDescription FROM admtf_Table_Features(a_SchemaName,v_ReferenceTableName); END IF; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName NAME,a_ForeignKeyName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableFeatures (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)); /*******************************************************************/ /* , */ /* */ /******************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableFeatures (a_SchemaName VARCHAR(256) default 'public', /* */ a_ForeignKeyName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (rfkrt_ReferenceTableName VARCHAR(256),rfkrt_ReferenceTableDescription TEXT) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; --********************************************************************* BEGIN RETURN QUERY SELECT fkrt.rfkrt_ReferenceTableName::VARCHAR(256), fkrt.rfkrt_ReferenceTableDescription::TEXT FROM admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName::NAME, a_ForeignKeyName::NAME) fkrt; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_ForeignKey_ReferenceTableFeatures('public'::VARCHAR(256),'fk_street_locality'::VARCHAR(256)); SELECt * FROM admtf_ForeignKey_ReferenceTableFeatures('public'::NAME,'fk_street_locality'::NAME); admtf_ForeignKey_ReferenceTableAttributes
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableAttributes (a_SchemaName NAME,a_ForeignKeyName NAME); /******************************************************************/ /* , */ /* */ /******************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableAttributes (a_SchemaName NAME default 'public', /* */ a_ForeignKeyName NAME default NULL /* */ ) RETURNS TABLE(r_ReferenceTableKeyNo SMALLINT,r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN,r_Description Text) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; v_ForeignKeyName NAME;/* */ v_ForeignKeyDescription TEXT;/* */ v_ReferenceTableKeyArray SMALLINT[];/* , */ /* */ v_ReferenceTableName NAME;/* , */ v_ReferenceTableDescription TEXT;/* , */ v_ReferenceTableOID OID; /* , */ v_AttributeNumber SMALLINT; /* */ v_FKAttributeCount INTEGER; /* */ v_AttNo SMALLINT; /* */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT (rank() OVER (PARTITION BY con.confrelid ORDER BY con.No))::SMALLINT AS r_ReferenceTableKeyNo, attr.attnum AS r_AttributeNumber,attr.attname::NAME AS r_AttributeName, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1), attr.atttypmod))::NAME AS r_TypeName, attr.attnotnull AS r_isNotNULL,TRIM(dsc.description) AS r_Description FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.connamespace,c.contype, c.conkey::SMALLINT[],c.consrc, c.confkey::SMALLINT[], generate_subscripts(c.conkey, 1) as No FROM pg_constraint c) con INNER JOIN pg_namespace nspc ON con.connamespace = nspc.oid INNER JOIN pg_attribute attr ON attr.attrelid=con.confrelid AND attr.attnum=con.confkey[con.No] INNER 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 nspc.nspname=LOWER(a_SchemaName) AND con.contype =c_ForeignKeyKind AND con.conname =LOWER(a_ForeignKeyName) ORDER BY con.No; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableAttributes(a_SchemaName NAME,a_ForeignKeyName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableAttributes (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)); /*********************************************************/ /* , */ /* */ /********************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableAttributes (a_SchemaName VARCHAR(256) default 'public', /* */ a_ForeignKeyName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (r_ReferenceTableKeyNo SMALLINT,r_AttributeNumber SMALLINT,r_AttributeName VARCHAR(256),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN,r_Description TEXT) AS $BODY$ DECLARE c_ForeignKeyKind CONSTANT CHAR:='f'; --**************************************************************** BEGIN RETURN QUERY SELECT fkra.r_ReferenceTableKeyNo::SMALLINT, fkra.r_AttributeNumber::SMALLINT,fkra.r_AttributeName::VARCHAR(256), fkra.r_UserTypeName::VARCHAR(256),fkra.r_TypeName::VARCHAR(256), fkra.r_isNotNULL::BOOLEAN,fkra.r_Description::TEXT FROM admtf_ForeignKey_ReferenceTableAttributes(a_SchemaName::NAME, a_ForeignKeyName::NAME) fkra; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableAttributes(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256)) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_ForeignKey_ReferenceTableAttributes('public'::VARCHAR(256),'fk_street_locality'::VARCHAR(256)); SELECt * FROM admtf_ForeignKey_ReferenceTableAttributes('public'::NAME,'fk_street_locality'::NAME); BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableComplexFeatures (a_SchemaName NAME,a_ForeignKeyName NAME,a_ForeignKeyNo SMALLINT); /*************************************************************/ /* , */ /* , */ /*************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures (a_SchemaName NAME default 'public', /* */ a_ForeignKeyName NAME default NULL, /* */ a_ForeignKeyNo SMALLINT default NULL /* */ ) RETURNS TABLE (fkrt_FeatureCategory VARCHAR(10),fkrt_FeatureNumber SMALLINT,fkrt_FeatureName NAME,fkrt_FeatureDescription TEXT,fkrt_UserTypeName NAME,fkrt_TypeName NAME,fkrt_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(1):='%'; c_ForeignKeyCategory CONSTANT VARCHAR(10):='fk'||c_WildChar||'rtbl'; /* */ /* , */ c_AttributeCategory CONSTANT VARCHAR(10):='fk'||c_WildChar||'ratt'; /* */ /* , */ v_ForeignKeyCharNo VARCHAR(2); /* */ v_ForeignKeyOID OID; /* */ v_ForeignKeyName NAME; /* */ v_ForeignKeyDescription TEXT; /* */ v_FeatureCategory VARCHAR(10); /* */ v_FeatureNumber SMALLINT; /* */ --********************************************************************* BEGIN v_ForeignKeyCharNo:=COALESCE(TRIM(TO_CHAR(a_ForeignKeyNo,'09')),''); v_FeatureCategory:=REPLACE(c_ForeignKeyCategory,c_WildChar, v_ForeignKeyCharNo); v_FeatureNumber:=0; SELECT INTO v_ForeignKeyName,v_ForeignKeyDescription rfkrt_ReferenceTableName,rfkrt_ReferenceTableDescription FROM admtf_ForeignKey_ReferenceTableFeatures(a_SchemaName,a_ForeignKeyName); IF FOUND AND v_ForeignKeyName IS NOT NULL THEN RETURN QUERY SELECT v_FeatureCategory,v_FeatureNumber,v_ForeignKeyName, v_ForeignKeyDescription,NULL::NAME AS fkrt_UserTypeName, NULL::NAME AS fkrt_TypeName, NULL::BOOLEAN AS fkrt_isNotNULL ; END IF; v_FeatureCategory:=REPLACE(c_AttributeCategory,c_WildChar, v_ForeignKeyCharNo); v_FeatureNumber:=0; RETURN QUERY SELECT v_FeatureCategory,r_ReferenceTableKeyNo,r_AttributeName, r_Description,r_UserTypeName,r_TypeName,r_isNotNULL FROM admtf_ForeignKey_ReferenceTableAttributes(a_SchemaName,a_ForeignKeyName); END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures(a_SchemaName NAME,a_ForeignKeyName NAME,a_ForeignKeyNo SMALLINT) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ReferenceTableComplexFeatures (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256),a_ForeignKeyNo SMALLINT); /*************************************************************/ /* , */ /* , */ /*************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures (a_SchemaName VARCHAR(256) default 'public', /* */ a_ForeignKeyName VARCHAR(256) default NULL,/* */ a_ForeignKeyNo SMALLINT default NULL /* */ ) RETURNS TABLE (fkrt_FeatureCategory VARCHAR(10),fkrt_FeatureNumber SMALLINT,fkrt_FeatureName VARCHAR(256),fkrt_FeatureDescription TEXT, fkrt_UserTypeName VARCHAR(256),fkrt_TypeName VARCHAR(256),fkrt_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(1):='%';/* */ /* */ --****************************************************************** BEGIN RETURN QUERY SELECT fkrt.fkrt_FeatureCategory::VARCHAR(10), fkrt.fkrt_FeatureNumber::SMALLINT, fkrt.fkrt_FeatureName::VARCHAR(256),fkrt.fkrt_FeatureDescription::TEXT, fkrt.fkrt_UserTypeName::VARCHAR(256), fkrt.fkrt_TypeName::VARCHAR(256),fkrt.fkrt_isNotNULL::BOOLEAN FROM admtf_ForeignKey_ReferenceTableComplexFeatures(a_SchemaName::NAME, a_ForeignKeyName::NAME,a_ForeignKeyNo::SMALLINT ) fkrt; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ReferenceTableComplexFeatures(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256),a_ForeignKeyNo SMALLINT) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_ForeignKey_ReferenceTableComplexFeatures('public'::NAME,'fk_street_locality'::NAME); SELECt * FROM admtf_ForeignKey_ReferenceTableComplexFeatures('public'::NAME,'fk_street_locality'::NAME,10::SMALLINT); SELECt * FROM admtf_ForeignKey_ReferenceTableComplexFeatures('public'::VARCHAR(256),'fk_street_locality'::VARCHAR(256),10::SMALLINT); BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ComplexFeatures (a_SchemaName NAME,a_ForeignKeyName NAME,a_ForeignKeyNo SMALLINT); /**************************************************************/ /* , */ /* , */ /**************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ComplexFeatures (a_SchemaName NAME default 'public', /* */ a_ForeignKeyName NAME default NULL, /* */ a_ForeignKeyNo SMALLINT default NULL /* */ ) RETURNS TABLE (rfk_FeatureCategory VARCHAR(10),rfk_FeatureNumber SMALLINT,rfk_FeatureName NAME,rfk_FeatureDescription TEXT, rfk_UserTypeName NAME,rfk_TypeName NAME,rfk_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(1):='%'; c_ForeignKeyCategory CONSTANT VARCHAR(10):='fk'||c_WildChar; /* */ /* */ c_AttributeCategory CONSTANT VARCHAR(10):='fk'||c_WildChar||'att'; /* */ /* */ /* */ v_ForeignKeyOID OID; /* */ v_ForeignKeyName NAME; /* */ v_ForeignKeyCharNo VARCHAR(2); /* */ v_ForeignKeyDescription TEXT; /* */ v_FeatureCategory VARCHAR(10); /* */ v_FeatureNumber SMALLINT; /* */ --************************************************************************ BEGIN v_ForeignKeyCharNo:=COALESCE(TRIM(TO_CHAR(a_ForeignKeyNo,'09')),''); v_FeatureCategory:=REPLACE(c_ForeignKeyCategory,c_WildChar, v_ForeignKeyCharNo); v_FeatureNumber:=0; SELECT INTO v_ForeignKeyName,v_ForeignKeyDescription rs_ForeignKeyName,rs_ForeignKeyDescription FROM admtf_ForeignKey_Features(a_SchemaName,a_ForeignKeyName); IF FOUND AND v_ForeignKeyName IS NOT NULL THEN RETURN QUERY SELECT v_FeatureCategory,COALESCE(a_ForeignKeyNo,v_FeatureNumber), v_ForeignKeyName,v_ForeignKeyDescription, NULL::NAME AS rfk_UserTypeName, NULL::NAME AS rfk_TypeName, NULL::BOOLEAN AS rfk_isNotNULL; END IF; v_FeatureCategory:=REPLACE(c_AttributeCategory,c_WildChar, v_ForeignKeyCharNo); v_FeatureNumber:=0; RETURN QUERY SELECT v_FeatureCategory,r_ForeignKeyNo,r_AttributeName,r_Description, r_UserTypeName,r_TypeName,r_isNotNULL FROM admtf_ForeignKey_Attributes(a_SchemaName,a_ForeignKeyName); RETURN QUERY SELECT fkrt_FeatureCategory,fkrt_FeatureNumber,fkrt_FeatureName, fkrt_FeatureDescription,fkrt_UserTypeName,fkrt_TypeName, fkrt_isNotNULL AS rfk_isNotNULL FROM admtf_ForeignKey_ReferenceTableComplexFeatures(a_SchemaName, a_ForeignKeyName,a_ForeignKeyNo); END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ComplexFeatures(a_SchemaName NAME,a_ForeignKeyName NAME,a_ForeignKeyNo SMALLINT) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_ForeignKey_ComplexFeatures (a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256),a_ForeignKeyNo SMALLINT); /**************************************************************/ /* , */ /* , */ /**************************************************************/ CREATE OR REPLACE FUNCTION admtf_ForeignKey_ComplexFeatures (a_SchemaName VARCHAR(256) default 'public', /* */ a_ForeignKeyName VARCHAR(256) default NULL, /* */ a_ForeignKeyNo SMALLINT default NULL /* */ ) RETURNS TABLE (rfk_FeatureCategory VARCHAR(10),rfk_FeatureNumber SMALLINT,rfk_FeatureName VARCHAR(256),rfk_FeatureDescription TEXT, rfk_UserTypeName VARCHAR(256),rfk_TypeName VARCHAR(256),rfk_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(1):='%'; --******************************************************************** BEGIN RETURN QUERY SELECT fkcf.rfk_FeatureCategory::VARCHAR(10), fkcf.rfk_FeatureNumber::SMALLINT, fkcf.rfk_FeatureName::VARCHAR(256),fkcf.rfk_FeatureDescription::TEXT, fkcf.rfk_UserTypeName::VARCHAR(256),fkcf.rfk_TypeName::VARCHAR(256), fkcf.rfk_isNotNULL::BOOLEAN FROM admtf_ForeignKey_ComplexFeatures(a_SchemaName::NAME, a_ForeignKeyName::NAME,a_ForeignKeyNo::SMALLINT) fkcf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_ForeignKey_ComplexFeatures(a_SchemaName VARCHAR(256),a_ForeignKeyName VARCHAR(256),a_ForeignKeyNo SMALLINT) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_ForeignKey_ComplexFeatures('public'::NAME,'fk_street_locality'::NAME); SELECt * FROM admtf_ForeignKey_ComplexFeatures('public'::NAME,'fk_street_locality'::NAME,1::SMALLINT); SELECt * FROM admtf_ForeignKey_ComplexFeatures('public'::VARCHAR,'fk_street_locality'::VARCHAR,3::SMALLINT); Creating function admtf_Index_ComplexFeatures
Comments on the source code of the function can be found here.
function code
admtf_Index_Features
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Index_Features (a_SchemaName NAME,a_IndexName NAME); /*******************************************************/ /* , */ /* */ /*******************************************************/ CREATE OR REPLACE FUNCTION admtf_Index_Features (a_SchemaName NAME default 'public', /* */ a_IndexName NAME default NULL /* */ ) RETURNS TABLE (rs_IndexName NAME,rs_IndexDescription TEXT) AS $BODY$ DECLARE c_IndexKind CONSTANT CHAR:='i'; v_IndexOID OID; /* */ v_IndexName NAME; /* */ v_IndexDescription TEXT; /* */ --*************************************************** BEGIN SELECT INTO rs_IndexName,rs_IndexDescription inxcls.relname, CASE WHEN COALESCE(TRIM(dsc.description),'')='' THEN '' || CASE WHEN inx.indisunique THEN ' ' || CASE WHEN inx.indisprimary THEN '( )' ELSE '' END ELSE '' END || CASE WHEN inxam.amname='gist' THEN ' ' ELSE '' END || ' '||tbl.relname ELSE dsc.description END FROM pg_index inx INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid INNER JOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid LEFT OUTER JOIN pg_Description dsc ON inxcls.oid=dsc.objoid AND dsc.objsubid=0 LEFT OUTER JOIN pg_am inxam ON inxcls.relam=inxam.oid LEFT OUTER JOIN pg_class tbl ON inx.indrelid=tbl.oid WHERE nsp.nspname=LOWER(a_SchemaName) AND inxcls.relkind=c_IndexKind AND inxcls.relname =LOWER(a_IndexName); RETURN QUERY SELECT rs_IndexName,rs_IndexDescription; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Index_Features(a_SchemaName NAME,a_IndexName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Index_Features (a_SchemaName VARCHAR(256),a_IndexName VARCHAR(256)); /*******************************************************/ /* , */ /* */ /*******************************************************/ CREATE OR REPLACE FUNCTION admtf_Index_Features (a_SchemaName VARCHAR(256) default 'public', /* */ a_IndexName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (rs_IndexName VARCHAR(256),rs_IndexDescription TEXT) AS $BODY$ DECLARE c_IndexKind CONSTANT CHAR:='i'; --*********************************************************** BEGIN RETURN QUERY SELECT ixf.rs_IndexName::VARCHAR(256), ixf.rs_IndexDescription::TEXT FROM admtf_Index_Features(a_SchemaName::NAME,a_IndexName::NAME) ixf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Index_Features(a_SchemaName VARCHAR(256),a_IndexName VARCHAR(256)) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_Index_Features('public'::NAME,'xie1street'::NAME); SELECt * FROM admtf_Index_Features('public'::VARCHAR(256),'xie1street'::VARCHAR(256)); SELECt * FROM admtf_Index_Features('public'::VARCHAR(256),'xie9street'::VARCHAR(256)); admtf_Index_Attributes
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Index_Attributes (a_SchemaName NAME,a_IndexName NAME); /*******************************************************/ /* , */ /* */ /*******************************************************/ CREATE OR REPLACE FUNCTION admtf_Index_Attributes (a_SchemaName NAME default 'public', /* */ a_IndexName NAME default NULL /* */ ) RETURNS TABLE (r_IndexNo SMALLINT,r_AttributeNumber SMALLINT,r_AttributeName NAME,r_OrderDirect VARCHAR(10), r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN,r_Description Text) AS $BODY$ DECLARE cursor_IndexNoOfAttribute refcursor; /* */ c_IndexKind CONSTANT CHAR:='i'; v_IndexOID OID; /* */ v_IndexName NAME; /* */ v_IndexDescription TEXT; /* */ v_MasterTableName NAME; /* , */ v_IndexArray SMALLINT[]; /* */ v_IndexKeyOps SMALLINT[]; /* */ v_AmCanOrder BOOLEAN; /* */ /* */ /* ?*/ v_MasterTableOID OID; /* , */ v_AttributeNumber SMALLINT; /* */ v_AttributeOrderCode INTEGER; /* */ v_IndexAttributeCount INTEGER; /* */ v_AttNo SMALLINT; /* */ --************************************************************************* BEGIN RETURN QUERY SELECT (inx.No+1)::SMALLINT AS r_IndexNo, attr.attnum::SMALLINT AS r_AttributeNumber, attr.attname::NAME AS r_AttributeName, CASE WHEN NOT inxam.amcanorder THEN NULL ELSE CASE WHEN inx.indoption[inx.No] & 1=1 THEN 'DESC' ELSE 'ASC' END END::VARCHAR(10) AS r_OrderDirect, CASE WHEN COALESCE(typ.typbasetype,0)>0 THEN typ.typname::NAME ELSE ''END AS r_UserTypeName, FORMAT_TYPE(COALESCE(NULLIF(typ.typbasetype,0),typ.oid), COALESCE(NULLIF(typ.typtypmod,-1), attr.atttypmod))::NAME AS r_TypeName, attr.attnotnull AS r_isNotNULL, dsc.description AS r_Description FROM (SELECT i.indrelid, i.indexrelid,i.indkey::SMALLINT[], i.indoption::SMALLINT[], generate_subscripts(i.indkey, 1) as No FROM pg_index i) inx INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid INNER JOIN pg_namespace nsp ON inxcls.relnamespace=nsp.oid LEFT OUTER JOIN pg_am inxam ON inxcls.relam=inxam.oid LEFT OUTER JOIN pg_class tbl ON inx.indrelid=tbl.oid INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid AND attr.attnum=inx.indkey[inx.No] 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 nsp.nspname=LOWER(a_SchemaName) AND inxcls.relkind=c_IndexKind AND inxcls.relname =LOWER(a_IndexName) ORDER BY nsp.nspname,inxcls.relname,inx.No; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Index_Attributes(a_SchemaName NAME,a_IndexName NAME) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Index_Attributes (a_SchemaName VARCHAR(256),a_IndexName VARCHAR(256)); /*******************************************************/ /* , */ /* */ /******************************************************/ CREATE OR REPLACE FUNCTION admtf_Index_Attributes (a_SchemaName VARCHAR(256) default 'public', /* */ a_IndexName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (r_IndexNo SMALLINT,r_AttributeNumber SMALLINT,r_AttributeName VARCHAR(256),r_OrderDirect VARCHAR(10),r_UserTypeName VARCHAR(256),r_TypeName VARCHAR(256),r_isNotNULL BOOLEAN,r_Description Text) AS $BODY$ DECLARE c_IndexKind CONSTANT CHAR:='i'; --******************************************************************* BEGIN RETURN QUERY SELECT ia.r_IndexNo::SMALLINT, ia.r_AttributeNumber::SMALLINT, ia.r_AttributeName::VARCHAR(256), ia.r_OrderDirect::VARCHAR(10), ia.r_UserTypeName::VARCHAR(256), ia.r_TypeName::VARCHAR(256), ia.r_isNotNULL::BOOLEAN, ia.r_Description::TEXT FROM admtf_Index_Attributes(a_SchemaName::NAME,a_IndexName::NAME) ia; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Index_Attributes(a_SchemaName VARCHAR(256),a_IndexName VARCHAR(256)) IS ' , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_Index_Attributes('public'::NAME,'xie1street'::NAME); SELECt * FROM admtf_Index_Attributes('public'::VARCHAR(256),'xie1street'::VARCHAR(256)); SELECt * FROM admtf_Index_Attributes('public'::VARCHAR(256),'xie9street'::VARCHAR(256)); BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Index_ComplexFeatures (a_SchemaName NAME,a_IndexName NAME,a_IndexNo SMALLINT); /************************************************************/ /* , */ /* , */ /************************************************************/ CREATE OR REPLACE FUNCTION admtf_Index_ComplexFeatures (a_SchemaName NAME default 'public', /* */ a_IndexName NAME default NULL, /* */ a_IndexNo SMALLINT default NULL /* */ ) RETURNS TABLE (rix_FeatureCategory VARCHAR(10),rix_FeatureNumber SMALLINT,rix_FeatureName NAME,rix_OrderDirect VARCHAR(10), rix_FeatureDescription TEXT, rix_UserTypeName NAME,rix_TypeName NAME,rix_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(1):='%'; c_IndexCategory CONSTANT VARCHAR(10):='idx'||c_WildChar;/* */ /* */ c_AttributeCategory CONSTANT VARCHAR(10):='idx'||c_WildChar||'att';/* */ /* */ v_IndexOID OID; /* */ v_IndexName NAME; /* */ v_IndexCharNo VARCHAR(2); /* */ v_IndexDescription TEXT; /* */ v_FeatureCategory VARCHAR(10); /* */ v_FeatureNumber SMALLINT; /* */ --*************************************************************************** BEGIN v_IndexCharNo:=COALESCE(TRIM(TO_CHAR(a_IndexNo,'09')),''); v_FeatureCategory:=REPLACE(c_IndexCategory,c_WildChar, v_IndexCharNo); v_FeatureNumber:=0; SELECT INTO v_IndexName,v_IndexDescription rs_IndexName,rs_IndexDescription FROM admtf_Index_Features(a_SchemaName,a_IndexName); IF FOUND AND v_IndexName IS NOT NULL THEN RETURN QUERY SELECT v_FeatureCategory, COALESCE(a_IndexNo,v_FeatureNumber), v_IndexName,NULL::VARCHAR(10), v_IndexDescription, NULL::NAME AS rix_UserTypeName, NULL::NAME AS rix_TypeName, NULL::BOOLEAN AS rix_isNotNULL; END IF; v_FeatureCategory:=REPLACE(c_AttributeCategory,c_WildChar, v_IndexCharNo); v_FeatureNumber:=0; RETURN QUERY SELECT v_FeatureCategory,r_IndexNo, r_AttributeName,r_OrderDirect,r_Description, r_UserTypeName,r_TypeName,r_isNotNULL FROM admtf_Index_Attributes(a_SchemaName,a_IndexName); RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Index_ComplexFeatures(a_SchemaName NAME,a_IndexName NAME,a_IndexNo SMALLINT) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Index_ComplexFeatures (a_SchemaName VARCHAR(256),a_IndexName VARCHAR(256),a_IndexNo SMALLINT); /************************************************************/ /* , */ /* , */ /************************************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Index_ComplexFeatures (a_SchemaName VARCHAR(256) default 'public', /* */ a_IndexName VARCHAR(256) default NULL, /* */ a_IndexNo SMALLINT default NULL /* */ ) RETURNS TABLE (rix_FeatureCategory VARCHAR(10),rix_FeatureNumber SMALLINT, rix_FeatureName VARCHAR(256),rix_OrderDirect VARCHAR(10),rix_FeatureDescription TEXT, rix_UserTypeName VARCHAR(256),rix_TypeName VARCHAR(256),rix_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_WildChar CONSTANT VARCHAR(1):='%'; --******************************************************************** BEGIN RETURN QUERY SELECT icf.rix_FeatureCategory::VARCHAR(10), icf.rix_FeatureNumber::SMALLINT, icf.rix_FeatureName::VARCHAR(256), icf.rix_OrderDirect::VARCHAR(10), icf.rix_FeatureDescription::TEXT, icf.rix_UserTypeName::VARCHAR(256), icf.rix_TypeName::VARCHAR(256), icf.rix_isNotNULL::BOOLEAN FROM admtf_Index_ComplexFeatures(a_SchemaName::NAME, a_IndexName::NAME,a_IndexNo::SMALLINT) icf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Index_ComplexFeatures(a_SchemaName VARCHAR(256),a_IndexName VARCHAR(256),a_IndexNo SMALLINT) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Index_ComplexFeatures('public'::NAME,'xie1street'::NAME,7::SMALLINT); SELECT * FROM admtf_Index_ComplexFeatures('public'::VARCHAR(256),'xie1street'::VARCHAR(256),7::SMALLINT); SELECT * FROM admtf_Index_ComplexFeatures('public'::VARCHAR(256),'xie9street'::VARCHAR(256),7::SMALLINT); Create function admtf_Table_ComplexFeatures
Comments on the source code of the function can be found here , as well as in the first part of the article in the sections "Structure of the head function" and "What advanced characteristics are we talking about?" .
function code
BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_ComplexFeatures (a_SchemaName NAME,a_TableName NAME); /*******************************************************************/ /* , , */ /* */ /********************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_ComplexFeatures (a_SchemaName NAME default 'public', /* */ a_TableName NAME default NULL /* */ ) RETURNS TABLE (rr_FeatureCategory VARCHAR(12),rr_FeatureNumber SMALLINT,rr_FeatureName NAME,rr_FeatureDescription TEXT,rr_UserTypeName NAME,rr_TypeName NAME,rr_isNotNULL BOOLEAN) AS $BODY$ DECLARE cursor_ForeignKeys refcursor; /* */ cursor_Indexes refcursor; /* */ c_WildChar CONSTANT VARCHAR(1):='%'; c_TableCategory CONSTANT VARCHAR(10):='tbl'; /* */ /* */ c_SequenceCategory CONSTANT VARCHAR(10):='seq'||c_WildChar; /* */ /* */ c_InheritanceTableCategory CONSTANT VARCHAR(10):='inhtbl'; /* */ /* */ c_AttributeCategory CONSTANT VARCHAR(10):='att'; /* */ /* */ c_PrimaryKeyCategory CONSTANT VARCHAR(10):='pk'; /* */ /* */ c_ForeignKeyCategory CONSTANT VARCHAR(10):='fk'; /* */ /* */ c_IndexCategory CONSTANT VARCHAR(10):='idx'; /* */ /* */ c_PrimaryKeyKind CONSTANT CHAR:='p'; c_ForeignKeyKind CONSTANT CHAR:='f'; v_TableOID OID; /* */ v_TableName NAME; /* */ v_TableDescription TEXT; /* */ v_FeatureCategory VARCHAR(12); /* */ v_FeatureNumber SMALLINT; /* */ v_PrimaryKeyName NAME; /* */ v_ForeignKeyName NAME; /* */ v_FKeyCount INTEGER; /* */ v_IndexName NAME; /* */ v_IndexCount INTEGER; /* */ --******************************************************************* BEGIN v_FeatureCategory:=c_TableCategory; v_FeatureNumber:=0; RETURN QUERY SELECT v_FeatureCategory,v_FeatureNumber,rs_TableName, rs_TableDescription,NULL::NAME AS rr_UserTypeName, NULL::NAME AS rr_TypeName,NULL::BOOLEAN AS rr_isNotNULL FROM admtf_Table_Features(a_SchemaName,a_TableName); v_FeatureCategory:=c_AttributeCategory; v_FeatureNumber:=0; RETURN QUERY SELECT v_FeatureCategory,r_AttributeNumber,r_AttributeName,r_Description, r_UserTypeName,r_TypeName,r_isNotNULL FROM admtf_Table_Attributes(a_SchemaName,a_TableName); v_FeatureCategory:=c_SequenceCategory; v_FeatureNumber:=0; RETURN QUERY SELECT REPLACE(c_SequenceCategory,c_WildChar, COALESCE(TRIM(TO_CHAR(r_SequenceNumber,'09')),'')):: VARCHAR(12) AS rr_FeatureCategory, r_SequenceNumber,r_SequenceName,r_SequenceDescription, NULL::NAME AS rr_UserTypeName, NULL::NAME AS rr_TypeName, NULL::BOOLEAN AS rr_isNotNULL FROM admtf_Table_Sequences(a_SchemaName,a_TableName); v_FeatureCategory:=c_PrimaryKeyCategory; v_FeatureNumber:=0; SELECT INTO v_PrimaryKeyName r_ConstraintName FROM admtf_Table_Constraintes(a_SchemaName,a_TableName) WHERE r_ConstraintType=c_PrimaryKeyKind; IF FOUND THEN RETURN QUERY SELECT rpk_FeatureCategory ,rpk_FeatureNumber, rpk_FeatureName,rpk_FeatureDescription,rpk_UserTypeName, rpk_TypeName, rpk_isNotNULL FROM admtf_PrimaryKey_ComplexFeatures(a_SchemaName,v_PrimaryKeyName); END IF; OPEN cursor_ForeignKeys FOR SELECT r_ConstraintName FROM admtf_Table_Constraintes(a_SchemaName,a_TableName) WHERE r_ConstraintType=c_ForeignKeyKind ORDER BY r_ConstraintName; v_FeatureCategory:=c_ForeignKeyCategory; v_FKeyCount:=0; FETCH FIRST FROM cursor_ForeignKeys INTO v_ForeignKeyName; WHILE FOUND LOOP v_FKeyCount:=v_FKeyCount+1; RETURN QUERY SELECT rfk_FeatureCategory , CASE WHEN rfk_FeatureCategory = c_ForeignKeyCategory THEN v_FKeyCount::SMALLINT ELSE rfk_FeatureNumber END, rfk_FeatureName,rfk_FeatureDescription, rfk_UserTypeName,rfk_TypeName, rfk_isNotNULL FROM admtf_ForeignKey_ComplexFeatures(a_SchemaName, v_ForeignKeyName,v_FKeyCount::SMALLINT); FETCH NEXT FROM cursor_ForeignKeys INTO v_ForeignKeyName; END LOOP; CLOSE cursor_ForeignKeys ; OPEN cursor_Indexes FOR SELECT r_IndexName FROM admtf_Table_Indexes(a_SchemaName,a_TableName) ORDER BY r_IndexName ; v_FeatureCategory:=c_IndexCategory; v_IndexCount:=0; FETCH FIRST FROM cursor_Indexes INTO v_IndexName; WHILE FOUND LOOP v_IndexCount:=v_IndexCount+1; RETURN QUERY SELECT rix_FeatureCategory , CASE WHEN rix_FeatureCategory = c_IndexCategory THEN v_IndexCount::SMALLINT ELSE rix_FeatureNumber END, rix_FeatureName,rix_FeatureDescription, rix_UserTypeName,rix_TypeName, rix_isNotNULL FROM admtf_Index_ComplexFeatures(a_SchemaName,v_IndexName, v_IndexCount::SMALLINT); FETCH NEXT FROM cursor_Indexes INTO v_IndexName; END LOOP; CLOSE cursor_Indexes ; v_FeatureCategory:=c_InheritanceTableCategory; v_FeatureNumber:=0; RETURN QUERY SELECT v_FeatureCategory,RANK() OVER(PARTITION BY v_FeatureCategory ORDER BY rs_TableName )::SMALLINT, rs_TableName,rs_TableDescription, NULL::NAME AS rr_UserTypeName, NULL::NAME AS rr_TypeName, NULL::BOOLEAN rr_isNotNULL FROM admtf_Table_InheritanceChildrens(a_SchemaName,a_TableName); END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_ComplexFeatures(a_SchemaName NAME,a_TableName NAME) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_ComplexFeatures (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************/ /* , , */ /* */ /*******************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_ComplexFeatures (a_SchemaName VARCHAR(256) default 'public',/* */ a_TableName VARCHAR(256) default NULL /* */ ) RETURNS TABLE (rr_FeatureCategory VARCHAR(12),rr_FeatureNumber SMALLINT,rr_FeatureName VARCHAR(256),rr_FeatureDescription TEXT, rr_UserTypeName VARCHAR(256),rr_TypeName VARCHAR(256),rr_isNotNULL BOOLEAN) AS $BODY$ DECLARE c_TableCategory CONSTANT VARCHAR(10):='tbl'; /* */ /* */ --*********************************************************** BEGIN RETURN QUERY SELECT tcf.rr_FeatureCategory::VARCHAR(12), tcf.rr_FeatureNumber::SMALLINT, tcf.rr_FeatureName::VARCHAR(256), tcf.rr_FeatureDescription::TEXT, tcf.rr_UserTypeName::VARCHAR(256), tcf.rr_TypeName::VARCHAR(256), tcf.rr_isNotNULL::BOOLEAN FROM admtf_Table_ComplexFeatures(a_SchemaName::NAME,a_TableName::NAME) tcf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_ComplexFeatures(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS ' , , '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_ComplexFeatures('public'::NAME,'Street'::NAME); see also
Functions for documenting PostgreSQL databases. Part One ;
Functions for documenting PostgreSQL databases. Part Two ;
Functions for documenting PostgreSQL databases. Part Three .
Source: https://habr.com/ru/post/419749/
All Articles