Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Teil zwei

Published on July 06, 2018

Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Teil zwei

    Dies ist der zweite Teil des Artikels, in dem Benutzerfunktionen für die Arbeit mit Systemkatalogen beschrieben werden: pg_class, pg_attribute, pg_constraints usw.

    In diesem Teil des Artikels werden Funktionen erläutert, die Eigenschaften von Abhängigkeiten und Indizes zurückgeben .

    Die erste Hälfte des Artikels enthält Kommentare zur Implementierung von Funktionen. Im zweiten der Quellcode der Funktionen. Für Leser, die sich nur für den Quellcode interessieren, empfehlen wir, direkt zum Anhang zu gehen .

    Siehe auch
    Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Teil Eins ;
    Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Teil drei .
    Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Das Ende (vierter Teil) .

    admtf_Table_Constraintes Liste der Einschränkungen für Datenbanktabellen und ihrer Merkmale



    Die Funktion admtf_Table_Constraintes gibt eine Liste der Einschränkungen (CONSTRAINT) einer Datenbanktabelle und ihrer Merkmale zurück. Der Quellcode kann hier angezeigt und heruntergeladen werden , und hier ist die Version der Funktion, in der der Cursor nicht verwendet wird .



    Die Funktion verwendet als Parameter den Namen der Quelltabelle ( a_TableName ) und den Namen des Schemas, in dem die Tabelle erstellt wird ( a_SchemaName ).

    Die Beschreibung einer einzelnen Einschränkung ist eine Sammlung von Einträgen in pg_class , die diese als physische Beziehung beschreiben, und Einträgen in pg_constraint, die Daten zu den spezifischen Merkmalen der Einschränkung enthalten.




    Quellcode-Operator in der Abbildung
    SELECT tbl.OID,con.conname,con.contype,con.conkey,reftbl.OID,
           reftbl.relname,con.confkey,con.consrc 
       FROM pg_constraint con 
         INNER JOIN pg_namespace nsp ON con.connamespace=nsp.oid 
         LEFT OUTER JOIN pg_class tbl ON con.conrelid=tbl.oid
         LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
    WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
         AND LOWER(tbl.relname)=LOWER(a_TableOID)
    ORDER BY con.contype DESC,con.conname;
    


    Die Hauptdaten (Name und Art der Einschränkung) werden aus dem Verzeichniseintrag pg_constraint abgerufen . Die Merkmale jeder Einschränkung werden aus demselben Katalog dargestellt, der in Form von OID-Tabellen ( conrelid , confrelid ) oder Arrays von Atomsequenznummern ( conkey , confkey ) vorliegt , die an der Einschränkung beteiligt sind.



    Die Merkmale der Einschränkungen werden von der Funktion in Form von Tabellen- und Attributnamen zurückgegeben. In diesem Fall werden die Tabellennamen aus dem Katalogeintrag pg_class mit dem Bezeichner (OID) und die Attributnamen aus den Katalogeinträgen pg_attribute mit dem Tabellenbezeichner und der Attributsequenznummer extrahiert . Weil Die Sequenznummern werden im Hauptverzeichnis in Form eines Arrays (einer Liste) gespeichert. Anschließend werden mithilfe einer Schleife Listen mit Attributnamen innerhalb der Funktion generiert.


    Die Funktion gibt ein spezielles Merkmal zurück - eine Regel zum Prüfen von Feldwerten in Tabelleneinträgen (CHECK-Einschränkung). Diese Funktion wird als Textwert in einem Feld gespeichert consrc , Verzeichnis pg_constraint .


    Tabelle 7. Das Ergebnis der Funktionsausführung admtf_Table_Constraintes ('public', 'Street').

    Textversion der Tabelle in der Abbildung
    Name Typ Attribute der Quelltabelle Name der externen Tabelle Attribute einer externen Tabelle Validierungsregel
    xpkstreet p wcrccode, localityid, streetid
    fk_street_locality f wcrccode, localityid Ortschaft wcrccode, localityid
    fk_street_streettype f Streettypeacrm Streettyp Streettypeacrm
    ck_street_streetname c Straßenname ((Straßenname) :: Text! ~ * '[az]' :: Text)
    ck_street_streettypeacrm c Streettypeacrm ((Streettypeacrm) :: bpchar! ~ * '[az]' :: text)



    Version ohne Cursor


    Ich sehe Fragen und Kommentare zur Verwendung des Cursors in der Hauptversion der Funktion vor.


    Ich werde nicht auf den Geschmack und die Farbe der Kameraden eingehen. Aber ich werde die Version der Funktion ohne Cursor geben. Die Version der Implementierung der Funktion ohne Verwendung des Cursors kann hier angezeigt und heruntergeladen werden .


    Die Hauptschwierigkeit besteht darin, die Verbindung (JOIN) von Tabellen anhand der Werte zu organisieren, die sich im Attribut des Array-Typs eines dieser Tabellen befinden. Solche Arrays sind in diesem Fall conkey und confkey .



    SELECT c.conname,c.contype,c.conkey::SMALLINT[],
           GENERATE_SUBSCRIPTS(c.conkey, 1) as No
       FROM  pg_constraint c 
       WHERE c.conname='fk_street_locality'
       ORDER BY No;
    

    Um dies zu lösen, enthält PostgrSQL Funktionen, die eine Tabelle mit den Werten von Zeigern auf Array-Elemente zurückgeben. In unserem Fall wird die Funktion generate_subscripts verwendet . Es wird nicht nur eine Reihe von Zeigern auf die Position des Arrays generiert, das als Parameter übergeben wird, sondern es wird auch ein Datensatz, der das Array enthält, durch die Anzahl der Elemente im Array in mehrere umgewandelt. Jeder Datensatz einer solchen Tabelle enthält einen eindeutigen Wert - die Position des Arrays.



    Tabelle 8. Wiedergabe der Quellzeichenfolge mit generate_subscripts .

    Name der Einschränkung Typ Attributnummernarray Zeiger auf Array Position
    fk_street_locality f {1,2} 1
    fk_street_locality f {1,2} 2



    Quellcode-Operator in der Abbildung
    SELECT con.conname AS ConstraintName,con.contype::VARCHAR(2) AS ConstraintType,
           STRING_AGG(attr.attname, ', 'ORDER BY con.No) AS r_ConstraintKeyNames,
           reftbl.relname AS RefTableName,
           STRING_AGG(rattr.attname,', 'ORDER BY con.No) AS r_RefTableKeyNames,
           con.consrc AS ConstraintSource
       FROM (SELECT c.oid, c.conrelid,c.confrelid,c.conname,c.contype,
                    c.conkey::SMALLINT[],c.consrc, c.confkey::SMALLINT[],
                    generate_subscripts(c.conkey, 1) as No 
                FROM pg_constraint c) con 
                  INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
                  INNER JOIN pg_attribute attr ON attr.attrelid=tbl.oid 
                                                AND attr.attnum=con.conkey[con.No]
                  INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
                  LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
                  LEFT OUTER JOIN pg_attribute rattr ON rattr.attrelid=reftbl.oid
                                                      AND rattr.attnum=con.confkey[con.No]
       WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
           AND LOWER(tbl.relname)=LOWER(a_TableName)
       GROUP BY con.conname,con.contype,reftbl.relname,con.consrc
       ORDER BY con.contype DESC,con.conname;
    


    Eine solche Tabelle kann mit dem Attributkatalog pg_attribute verbunden werden, indem Attributnamen mit der Bedingung attr.attrelid = tbl.oid AND attr.attnum = con.conkey [con.No] aus ihm extrahiert werden .
    Jetzt müssen Sie die zusätzlichen Einträge durch Gruppieren der Einträge entfernen und aus den Attributnamen eine Zeichenfolge erstellen.


    Das Erstellen eines Strings erfolgt mit der Aggregationsfunktion STRING_AGG , in der die Sortieroption (ORDER BY) angegeben werden muss. Andernfalls entspricht die Reihenfolge der Attribute möglicherweise nicht der Reihenfolge der Deklaration der Attribute im Index.



    Die Ausführungszeit beider Versionen der Funktionen stimmte mit mir überein. Die Ausgabe der Daten in der Ergebnistabelle dauerte 20 ms.


    Die Funktion admtf_Table_Index gibt eine Liste der Indizes einer Datenbanktabelle und ihrer Merkmale an



    Die Funktion admtf_Table_Indexes gibt eine Liste von Indizes (INDEX) einer Datenbanktabelle und deren Eigenschaften zurück. Der Quellcode kann hier angezeigt und heruntergeladen werden , und hier ist die Version der Funktion, in der der Cursor nicht verwendet wird .


    Die Funktion verwendet als Parameter den Namen der Quelltabelle ( a_TableName ) und den Namen des Schemas, in dem die Tabelle erstellt wird ( a_SchemaName ).

    Quellcode-Operator in der Abbildung
    SELECT tbl.oid,inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary,
           inx.indkey::SMALLINT[],inx.indoption::SMALLINT[],inxam.amcanorder
       FROM pg_index inx 
         INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid 
         INNER JOIN pg_namespace inxnsp ON inxcls.relnamespace=inxnsp.oid
         INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
         INNER JOIN pg_class tbl ON inx.indrelid=tbl.oid
         INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
    WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
         AND LOWER(tbl.relname)=LOWER(a_TableOID)
    ORDER BY inxam.amname, inxcls.relname;
    


    Eine einzelne Indexbeschreibung ist eine Auflistung eines Eintrags pg_class , der diese als physische Beziehung beschreibt, und eines Datensatzes pg_index , der Daten zu den spezifischen Merkmalen des Index enthält. Darüber hinaus werden Informationen zu Indexzugriffsmethoden im Systemverzeichnis pg_am gespeichert.


    CASE inxam.amcanorder WHEN true THEN CASE inx.indoption[inx.No] & 1 
                                            WHEN 1 THEN ' DESC' 
                                            ELSE ' ASC'  END 
                                    ELSE '' END;
    

    Aus dem Verzeichniseintrag pg_index extrahierten Merkmal eindeutigen Index ( indisunique ), eine Angabe , dass der Index in Übereinstimmung mit der Beschreibung des Primärschlüssels (eingebaute indisprimary sowie als Arrays von Sequenztabelle Attributen der Räume), aus den Werten , den Index (aufgebaut ist indkey ) und Vorzeichen der Sortierreihenfolge der Attributwerte im Index ( Indoption ).


    Aus dem Katalogeintrag, der die Zugriffsmethode des pg_am- Index beschreibt, wird ein Merkmal der Eignung der im Index enthaltenen Daten zum Sortieren ( amcanorder ) und des Namens oder der Art der Indexzugriffsmethode ( amname ) abgerufen .

    Mit anderen Worten, das Attribut amcanorder gibt an, ob es möglich ist, die Sortierreihenfolge der im Attributindex enthaltenen Werte festzulegen. Wenn amcanorder = true ist , kann die Sortierreihenfolge angegeben werden, andernfalls nicht. Die Bedeutung der Werte des Indoptions- Arrays ergibt sich aus derselben Abbildung. Wenn das rechte Bit der Binärform 1B enthält, wird der Wert des entsprechenden Attributs in absteigender Reihenfolge sortiert, ansonsten in aufsteigender Reihenfolge.


    Listen der im Index enthaltenen Attributnamen sowie Zeichen für die Reihenfolge der Attributwerte werden mithilfe einer Schleife innerhalb der Funktion erstellt.



    Tabelle 9. Ergebnis der Ausführung der Funktion admtf_Table_Indexes ('public', 'Street').


    Textversion der Tabelle in der Abbildung
    Indexname Methode ? Einzigartig ? Primärschlüssel Attribute im Index enthalten
    xie1street btree f f wcrccode ASC, localityid ASC, streettypeacrm ASC, streetname ASC
    xie2stree btree f f wcrccode ASC, localityid ASC, streetname ASC
    xie3street btree f f streetname ASC
    xie9street btree f f wcrccode ASC, localityid ASC, streetname DESC
    xpkstreet btree t t wcrccode ASC, localityid ASC, streetid ASC
    xts1street gin f f streettsvector
    xts2street gin f f streettsvector


    Version ohne Cursor


    Der Ansatz zum Erstellen einer Version einer Funktion ohne Cursor stimmt vollständig mit dem im vorherigen Abschnitt beschriebenen überein:


    • Datensätze mit generate_subscripts replizieren;
    • nachfolgende Gruppierung von Datensätzen;
    • Erstellen einer Liste von Indexattributen mithilfe der Funktion STRING_AGG mit der Option ORDER BY.


    Quellcode-Operator in der Abbildung
    SELECT inxcls.relname AS r_IndexName ,inxam.amname AS r_IndexType,
           inx.indisunique AS r_isUnique,inx.indisprimary AS r_isPrimary,
           STRING_AGG(attr.attname||
                 CASE inxam.amcanorder  WHEN true THEN CASE inx.indoption[inx.No] & 1 
                                                          WHEN 1 THEN ' DESC' 
                                                          ELSE ' ASC'  END 
                                                   ELSE '' END, 
    		   c_Delimiter ORDER BY inx.No)
         FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,i.indisprimary,
                      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_am inxam ON inxcls.relam=inxam.oid
                   INNER JOIN  pg_class tbl ON inx.indrelid=tbl.oid
                   INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
                   INNER JOIN pg_attribute attr ON attr.attrelid=tbl.OID 
                                                AND attr.attnum=inx.indkey[inx.No]
         WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
               AND LOWER(tbl.relname)=LOWER(a_TableName)
         GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
         ORDER BY inxcls.relname;
    



    Die Ausführungszeit beider Versionen der Funktionen fiel mit mir zusammen, um die Daten in der Ergebnistabelle in 20 ms anzuzeigen.


    Daher werde ich seitdem keine Versionen von Funktionen mehr produzieren Diejenigen, die es wünschen, können sie nach Ihren Wünschen ändern oder mich kontaktieren. Ich werde eine modifizierte Version kostenlos senden .

    Siehe auch den ersten , dritten und vierten Teil des Artikels.



    ANHANG 1. Skripte



    Erstellen Sie die Funktion admtf_Table_Constraintes


    Kommentare zum Quellcode der Funktion finden Sie hier.
    Funktionsquellcode
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME);
    /********************************************************************************************************/
    /*  Функция возвращает список ограничений таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
    	(a_SchemaName	name default 'public',	/* название схемы базы данных	 */			
    	a_TableName	name default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS
    $BODY$
    DECLARE	v_Scale			INTEGER;		/* Масштаб колонки */
    	v_ConstraintRec		RECORD;			/* Данные об ограничении*/
    	v_TableOID		INTEGER;		/* OID таблицы*/
    	v_ConstraintOID		INTEGER;		/* OID ограничения*/
    	v_ConstraintKeyNos	SMALLINT[];	/* */
    	v_ConstraintName	name;		/* Название ограничения */
    	v_ConstraintType	name;		/* Буквенное обозначение типа ограничения */
    	v_isUnique	 	BOOLEAN;		/* Признак уникальности ограничения*/
    	v_isPrimary	BOOLEAN;	/* Признак того что индекс представляет Primary KEY таблицы*/
    	v_AttributeNum		INTEGER;		/* Порядковый номер аттрибута*/
    	v_AttributeName		name;		/* Наименование аттрибута*/
    	v_ConstraintKeyNames	TEXT;	/* Строка со списком аттрибутов ограничения*/
    	v_RefTableOID		INTEGER;	/* OID таблицы, на которую ссылается ограничение */
    	v_RefTableName		name;/* Название таблицы, на которую ссылается ограничение */
    	v_RefTableKeyNos	SMALLINT[];		/* */
    	v_RefTableKeyNames	TEXT;	/* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
    	v_ConstraintSource	TEXT;	/* Строка с описанием уловия CHECK*/
    	c_Delimiter		CONSTANT VARCHAR(2):=',';
    	--******************************************************************************************************		
    BEGIN		
    	FOR v_ConstraintRec IN SELECT con.oid AS ConstraintOID ,tbl.OID AS TableOID,
    					con.conname AS ConstraintName,
    					con.contype AS ConstraintType,
    					con.conkey AS ConstraintKeyNos,
    					reftbl.OID AS RefTableOID,
    					reftbl.relname AS RefTableName,
    					con.confkey AS RefTableKeyNos,
    					con.consrc AS ConstraintSource 
    				FROM pg_constraint con 
    					INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
    					INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
    					LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
    				WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
    					AND LOWER(tbl.relname)=LOWER(a_TableName)
    				ORDER BY con.contype DESC,con.conname 
    	LOOP
    		v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
    		v_TableOID:=v_ConstraintRec.TableOID;
    		v_ConstraintName:=v_ConstraintRec.ConstraintName;
    		v_ConstraintType:=v_ConstraintRec.ConstraintType;
    		v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
    		v_RefTableOID:=v_ConstraintRec.RefTableOID;
    		v_RefTableName:=v_ConstraintRec.RefTableName;
    		v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
    		v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
    		v_ConstraintKeyNames:='';
    		FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
    		LOOP
    			SELECT INTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
    				FROM pg_attribute attr 
    				WHERE attr.attrelid=v_TableOID 
    					AND attr.attnum=v_AttributeNum;
    			v_ConstraintKeyNames:=v_ConstraintKeyNames||
    					CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END 
    						||v_AttributeName;	
    		END LOOP;		
    		v_RefTableKeyNames:='';
    		IF v_RefTableKeyNos IS NOT NULL THEN
    			FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
    			LOOP
    				SELECT INTO v_AttributeName 
    						attr.attname::VARCHAR(100) AS r_AttributeName
    					FROM pg_attribute attr 
    					WHERE attr.attrelid=v_RefTableOID AND attr.attnum=v_AttributeNum;
    				v_RefTableKeyNames:=v_RefTableKeyNames||
    					CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
     						||v_AttributeName;	
    			END LOOP;		
    		END IF;	
    		RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
    						v_RefTableName,v_RefTableKeyNames,
    						v_ConstraintSource;					
    	END LOOP;			
    	RETURN;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает возвращает ограничений таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
    /********************************************************************************************************/
    /*  Функция возвращает список ограничений таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
    	(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных */
    	a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
    $BODY$
    DECLARE		
    	--******************************************************************************************************
    BEGIN		
    	RETURN QUERY SELECT tc.r_ConstraintName::VARCHAR(256),
    				tc.r_ConstraintType::VARCHAR(256),tc.r_ConstraintKeyNames::TEXT,
    				tc.r_RefTableName::VARCHAR(256),tc.r_RefTableKeyNames::TEXT,
    				tc.r_ConstraintSource::TEXT
    		FROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает возвращает ограничений таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
    SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);
    



    Das Erstellen einer Version der Funktion admtf_Table_Constraintes ohne Cursor


    Kommentare zum Quellcode der Funktion finden Sie hier.
    Funktionsquellcode
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName NAME, a_TableName NAME);
    /********************************************************************************************************/
    /*  Функция возвращает список ограничений таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
    		(a_SchemaName	name default 'public',	/* название схемы базы данных */
    		a_TableName	name default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_ConstraintName NAME,r_ConstraintType NAME,r_ConstraintKeyNames Text,r_RefTableName NAME,r_RefTableKeyNames Text,r_ConstraintSource Text) AS
    $BODY$
    DECLARE	v_Scale			INTEGER;		/* Масштаб колонки */
    	v_ConstraintRec		RECORD;			/* Данные об ограничении*/
    	v_TableOID		INTEGER;		/* OID таблицы*/
    	v_ConstraintOID		INTEGER;		/* OID ограничения*/
    	v_ConstraintKeyNos	SMALLINT[];	/* */
    	v_ConstraintName	name;		/* Название ограничения */
    	v_ConstraintType	name;		/* Буквенное обозначение типа ограничения */
    	v_isUnique	 	BOOLEAN;		/* Признак уникальности ограничения*/
    	v_isPrimary	 BOOLEAN;/* Признак того что индекс представляет Primary KEY таблицы*/
    	v_AttributeNum		INTEGER;		/* Порядковый номер аттрибута*/
    	v_AttributeName		name;		/* Наименование аттрибута*/
    	v_ConstraintKeyNames TEXT;		/* Строка со списком аттрибутов ограничения*/
    	v_RefTableOID		INTEGER;	/* OID таблицы, на которую ссылается ограничение */
    	v_RefTableName		name;/* Название таблицы, на которую ссылается ограничение */
    	v_RefTableKeyNos	SMALLINT[];		/* */
    	v_RefTableKeyNames	TEXT;/* Строка со списком аттрибутов таблицы, на которую ссылается ограничение*/
    	v_ConstraintSource	TEXT;			/* Строка с описанием уловия CHECK*/
    	c_Delimiter		CONSTANT VARCHAR(2):=',';
    	--******************************************************************************************************		
    BEGIN		
    	FOR v_ConstraintRec IN SELECT con.oid AS ConstraintOID ,
    					tbl.OID AS TableOID,
    					con.conname AS ConstraintName,
    					con.contype AS ConstraintType,
    					con.conkey AS ConstraintKeyNos,
    					reftbl.OID AS RefTableOID,
    					reftbl.relname AS RefTableName,
    					con.confkey AS RefTableKeyNos,
    					con.consrc AS ConstraintSource 
    				FROM pg_constraint con 
    					INNER JOIN pg_class tbl ON con.conrelid=tbl.oid
    					INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
    					LEFT OUTER JOIN pg_class reftbl ON con.confrelid=reftbl.oid
    				WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName)
    					 AND LOWER(tbl.relname)=LOWER(a_TableName)
    				ORDER BY con.contype DESC,con.conname 
    	LOOP
    		v_ConstraintOID:=v_ConstraintRec.ConstraintOID;
    		v_TableOID:=v_ConstraintRec.TableOID;
    		v_ConstraintName:=v_ConstraintRec.ConstraintName;
    		v_ConstraintType:=v_ConstraintRec.ConstraintType;
    		v_ConstraintKeyNos:=v_ConstraintRec.ConstraintKeyNos;
    		v_RefTableOID:=v_ConstraintRec.RefTableOID;
    		v_RefTableName:=v_ConstraintRec.RefTableName;
    		v_RefTableKeyNos:=v_ConstraintRec.RefTableKeyNos;
    		v_ConstraintSource:=v_ConstraintRec.ConstraintSource;
    		v_ConstraintKeyNames:='';
    		FOREACH v_AttributeNum IN ARRAY v_ConstraintKeyNos
    		LOOP
    			SELECT INTO v_AttributeName 
    					attr.attname::VARCHAR(100) AS r_AttributeName
    				FROM pg_attribute attr 
    				WHERE attr.attrelid=v_TableOID 
    					AND attr.attnum=v_AttributeNum;
    			v_ConstraintKeyNames:=v_ConstraintKeyNames||
    					CASE WHEN v_ConstraintKeyNames='' THEN '' ELSE c_Delimiter END
    										||v_AttributeName;	
    		END LOOP;		
    		v_RefTableKeyNames:='';
    		IF v_RefTableKeyNos IS NOT NULL THEN
    			FOREACH v_AttributeNum IN ARRAY v_RefTableKeyNos
    			LOOP
    				SELECT INTO v_AttributeName 
    						attr.attname::VARCHAR(100) AS r_AttributeName
    					FROM pg_attribute attr 
    					WHERE attr.attrelid=v_RefTableOID
    						AND attr.attnum=v_AttributeNum;
    				v_RefTableKeyNames:=v_RefTableKeyNames||
    					CASE WHEN v_RefTableKeyNames='' THEN '' ELSE c_Delimiter END
     										||v_AttributeName;	
    			END LOOP;		
    		END IF;	
    		RETURN QUERY SELECT v_ConstraintName,v_ConstraintType,v_ConstraintKeyNames,
    						v_RefTableName,v_RefTableKeyNames,
    						v_ConstraintSource;					
    	END LOOP;			
    	RETURN;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает возвращает ограничений таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Constraintes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
    /********************************************************************************************************/
    /*  Функция возвращает список ограничений таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Constraintes
    	(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных	*/
    	a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_ConstraintName VARCHAR(256),r_ConstraintType VARCHAR(256),r_ConstraintKeyNames Text,r_RefTableName VARCHAR(256),r_RefTableKeyNames Text,r_ConstraintSource Text) AS
    $BODY$
    DECLARE		
    	--******************************************************************************************************
    BEGIN		
    	RETURN QUERY SELECT tc.r_ConstraintName::VARCHAR(256),
    				tc.r_ConstraintType::VARCHAR(256),
    				tc.r_ConstraintKeyNames::TEXT,
    				tc.r_RefTableName::VARCHAR(256),
    				tc.r_RefTableKeyNames::TEXT,
    				tc.r_ConstraintSource::TEXT
    		FROM admtf_Table_Constraintes(a_SchemaName::NAME,a_TableName::NAME) tc;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Constraintes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает возвращает ограничений таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    SELECT * FROM admtf_Table_Constraintes('public'::VARCHAR(256),'Street'::VARCHAR(256));
    SELECT * FROM admtf_Table_Constraintes('public'::name,'Street'::name);
    



    Erstellen Sie die Funktion admtf_Table_Indexes


    Kommentare zum Quellcode der Funktion finden Sie hier.
    Funktionsquellcode
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME);
    /********************************************************************************************************/
    /*  Функция возвращает список индексов таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Indexes
    	(a_SchemaName	NAME default 'public',	/* название схемы базы данных	*/
    	a_TableName	NAME default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
    $BODY$
    DECLARE
    	c_IndexKind	CONSTANT CHAR:='i';
    	v_IndexRec		RECORD;			/* Данные об индексе*/
    	v_Scale			INTEGER;		/* Масштаб колонки */
    	v_TableOID		INTEGER;		/* OID таблицы*/
    	v_IndexOID		INTEGER;		/* OID индекса*/
    	v_IndexKeyNos		SMALLINT[];		/* */
    	v_IndexName		NAME;		/* Название индекса */
    	v_IndexAMName		NAME;		/* Наименование типа индекса (метода доступа) */
    	v_isUnique	 	BOOLEAN;		/* Признак уникальности индекса*/
    	v_isPrimary	 	BOOLEAN;		/* Признак того что индекс представляет Primary KEY таблицы*/
    	v_AttributeNum		INTEGER;		/* Порядковый номер атрибута*/
    	v_AttributeName		NAME;		/* Наименование атрибута*/
    	v_IndexKeyNames		TEXT;			/* Строка со списком атрибутов индекса*/
    	c_Delimiter		CONSTANT VARCHAR(2):=',';
    	--******************************************************************************************************		
    BEGIN		
    	FOR v_IndexRec IN SELECT inxcls.oid AS IndexOID,tbl.oid AS TableOID,
    						inxcls.relname AS IndexName,inxam.amname AS IndexAMName,
    						inx.indisunique AS isUnique,inx.indisprimary isPrimary,
    						inx.indkey::SMALLINT[] AS IndexKeyNos
    			FROM pg_index inx 
    				INNER JOIN pg_class inxcls ON inx.indexrelid=inxcls.oid 
    				INNER JOIN pg_am inxam ON inxcls.relam=inxam.oid
    				INNER JOIN  pg_class tbl ON inx.indrelid=tbl.oid
    				INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
    			WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
    				AND inxcls.relkind=c_IndexKind
    				AND tbl.relname=LOWER(a_TableName)
    			ORDER BY inxam.amname, inxcls.relname LOOP
    		v_IndexOID:=v_IndexRec.IndexOID;
    		v_TableOID:=v_IndexRec.TableOID;
    		v_IndexName:=v_IndexRec.IndexName;
    		v_IndexAMName:=v_IndexRec.IndexAMName;
    		v_isUnique:=v_IndexRec.isUnique;
    		v_isPrimary:=v_IndexRec.isPrimary;
    		v_IndexKeyNos:=v_IndexRec.IndexKeyNos;
    		v_IndexKeyNames:='';
    		FOREACH v_AttributeNum IN ARRAY v_IndexKeyNos
    		LOOP
    			SELECT INTO v_AttributeName attr.attname::VARCHAR(100) AS r_AttributeName
    				FROM pg_attribute attr 
    				WHERE attr.attrelid=v_TableOID AND attr.attnum=v_AttributeNum;
    			v_IndexKeyNames:=v_IndexKeyNames||
    						CASE WHEN v_IndexKeyNames='' THEN '' 
    									ELSE c_Delimiter||' ' END ||
    									v_AttributeName;
    		END LOOP;		
    		RETURN QUERY SELECT v_IndexName,v_IndexAMName,v_isUnique,
    					v_isPrimary,v_IndexKeyNames;					
    	END LOOP;			
    	RETURN;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список индексов таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
    /********************************************************************************************************/
    /*  Функция возвращает список индексов таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Indexes
    	(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных	*/
    	a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
    $BODY$
    DECLARE
    	--******************************************************************************************************		
    BEGIN		
    	RETURN QUERY SELECT ti.r_IndexName::VARCHAR(256),
    						ti.r_IndexType::VARCHAR(256),
    						ti.r_isUnique::BOOLEAN,
    						ti.r_isPrimary::BOOLEAN, 
    						ti.r_IndexKeyNames::TEXT
    		FROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список индексов таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
    SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));	
    



    Eine Version der Funktion admtf_Table_Indexes ohne Cursor erstellen


    Kommentare zum Quellcode der Funktion finden Sie hier.
    Funktionsquellcode
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName NAME, a_TableName NAME);
    /********************************************************************************************************/
    /*  Функция возвращает список индексов таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Indexes
    		(a_SchemaName	NAME default 'public',	/* название схемы базы данных		*/
    		a_TableName	NAME default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_IndexName NAME,r_IndexType NAME,r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames Text) AS
    $BODY$
    DECLARE
    	c_IndexKind	CONSTANT CHAR:='i';
    	c_Delimiter		CONSTANT VARCHAR(2):=', ';
    	--******************************************************************************************************		
    BEGIN		
    	RETURN QUERY SELECT inxcls.relname AS r_IndexName,
    				inxam.amname AS r_IndexType,
    				inx.indisunique AS r_isUnique,
    				inx.indisprimary r_isPrimary,
    			STRING_AGG(attr.attname||CASE inxam.amcanorder  WHEN true 
    					THEN CASE inx.indoption[inx.No] & 1 
    							WHEN 1 THEN ' DESC' 
    								ELSE ' ASC' 
    							END 
    					ELSE '' 
    				END, 
    					c_Delimiter ORDER BY inx.No)
    		FROM (SELECT i.indrelid, i.indexrelid,i.indisunique,
    					i.indisprimary,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_am inxam ON inxcls.relam=inxam.oid
    			INNER JOIN  pg_class tbl ON inx.indrelid=tbl.oid
    			INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 
    			INNER JOIN pg_attribute attr ON attr.attrelid=tbl.OID 
    					AND attr.attnum=inx.indkey[inx.No]
    		WHERE LOWER(nsp.nspname)=LOWER(a_SchemaName) 
    			AND inxcls.relkind=c_IndexKind
    			AND tbl.relname=LOWER(a_TableName)
    		GROUP BY inxcls.relname,inxam.amname,inx.indisunique,inx.indisprimary
    		ORDER BY inxcls.relname; 			
    	RETURN;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName NAME, a_TableName NAME) IS 'Возвращает список индексов таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    BEGIN TRANSACTION;
    DROP FUNCTION IF EXISTS admtf_Table_Indexes (a_SchemaName VARCHAR(256), a_TableName VARCHAR(256));
    /********************************************************************************************************/
    /*  Функция возвращает список индексов таблицы								*/
    /********************************************************************************************************/
    CREATE OR REPLACE FUNCTION admtf_Table_Indexes
    		(a_SchemaName	VARCHAR(256) default 'public',	/* название схемы базы данных*/
    		a_TableName	VARCHAR(256) default NULL	/* Название таблицы */
     )										 
    RETURNS  TABLE (r_IndexName VARCHAR(256),r_IndexType VARCHAR(256),r_isUnique BOOLEAN,r_isPrimary BOOLEAN, r_IndexKeyNames TEXT) AS
    $BODY$
    DECLARE
    	--******************************************************************************************************		
    BEGIN		
    	RETURN QUERY SELECT ti.r_IndexName::VARCHAR(256),
    					ti.r_IndexType::VARCHAR(256),
    					ti.r_isUnique::BOOLEAN,
    					ti.r_isPrimary::BOOLEAN,
    					ti.r_IndexKeyNames::TEXT
    		FROM admtf_Table_Indexes(a_SchemaName::NAME,a_TableName::NAME) ti;
    END
    $BODY$
    LANGUAGE plpgsql;
    COMMENT ON FUNCTION admtf_Table_Indexes(a_SchemaName VARCHAR(256), a_TableName VARCHAR(256)) IS 'Возвращает список индексов таблицы	';
    --ROLLBACK TRANSACTION;
    COMMIT TRANSACTION;
    SELECT * FROM admtf_Table_Indexes('public'::NAME,'Street'::NAME);
    SELECT * FROM admtf_Table_Indexes('public'::VARCHAR(256),'Street'::VARCHAR(256));
    


    Siehe auch


    Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Teil Eins ;
    Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Teil drei .
    Funktionen zur Dokumentation von PostgreSQL-Datenbanken. Das Ende (vierter Teil) .