Dies ist der zweite Teil des Artikels, der benutzerdefinierte Funktionen für die Arbeit mit Systemverzeichnissen beschreibt: pg_class, pg_attribute, pg_constraints usw.
In diesem Teil des Artikels werden Funktionen erläutert,
die die Eigenschaften von Einschränkungen und Indizes zurückgeben .
Die erste Hälfte des Artikels enthält Kommentare zur Implementierung von Funktionen. Der zweite ist der Quellcode der Funktionen. Für diejenigen Leser, die nur an den Ausgangstexten interessiert sind, empfehlen wir, sofort mit dem
Anhang fortzufahren .
Siehe auch
Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Teil eins ;
Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Teil drei .
Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Das Ende (Teil vier) .
admtf_Table_Constraintes Liste der Einschränkungen der Datenbanktabelle und ihrer Eigenschaften
Die Funktion admtf_Table_Constraintes gibt eine Liste der Einschränkungen (CONSTRAINT) der Datenbanktabelle und ihrer Merkmale zurück. Der Quellcode kann hier angezeigt und heruntergeladen werden . Hier ist eine Version einer Funktion, die keinen Cursor verwendet .
Als Parameter verwendet die Funktion den Namen der
Quelltabelle (
a_TableName ) und den Namen des Schemas, in dem die Tabelle erstellt wird (
a_SchemaName ).
Die Beschreibung einer bestimmten Einschränkung ist eine Kombination aus dem Datensatz in pg_class , der sie als physikalische Beziehung beschreibt, und dem Datensatz in pg_constraint , der Daten zu den spezifischen Merkmalen der Einschränkung enthält.
der Quellcode des Operators in der AbbildungSELECT 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 extrahiert, der in Form von OID-Tabellen ( conrelid , confrelid ) oder Arrays von Attribut-Seriennummern ( conkey , confkey ) dargestellt wird, die an der Einschränkung teilnehmen.
Die Funktion gibt Einschränkungsmerkmale in Form von Tabellennamen und Attributen zurück. In diesem Fall werden die Tabellennamen aus dem Katalogeintrag pg_class nach Kennung (OID) und die Attributnamen aus den Katalogeinträgen pg_attribute nach Tabellenkennung und Attributseriennummer extrahiert . Weil Da Seriennummern in Form eines Arrays (einer Liste) im Hauptverzeichnis gespeichert sind, werden Listen von Attributnamen innerhalb der Funktion mithilfe einer Schleife erstellt.
Die Funktion gibt ein spezielles Merkmal zurück - die Regel zum Überprüfen der Werte von Feldern in Tabelleneinträgen (Einschränkung CHECK). Dieses Merkmal wird als Textwert im Feld consrc des Verzeichnisses pg_constraint gespeichert .
Tabelle 7. Das Ergebnis der Ausführung der Funktion admtf_Table_Constraintes ('public', 'Street').
Die Textversion der Tabelle in der Abbildung| Titel | Typ | Quellentabellenattribute | Der Name der externen Tabelle | Attribute einer externen Tabelle | Validierungsregel |
|---|
| xpkstreet | p | wcrccode, localityid, streetid | | | |
| fk_street_locality | f | wcrccode, localityid | Lokalität | 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 voraus.
Ich werde nicht antworten - es gibt keine Kameraden für den Geschmack und die Farbe. Aber ich werde eine Version der Funktion ohne Cursor geben. Die Version der Funktionsimplementierung ohne Verwendung des Cursors kann hier angezeigt und heruntergeladen werden .
Die Hauptschwierigkeit besteht darin, den Join (JOIN) der Tabellen gemäß den Werten zu organisieren, die sich im Attributtyp des Arrays 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 ein solches Problem zu lösen, enthält PostgrSQL Funktionen, die eine Wertetabelle von Zeigern auf Array-Elemente zurückgeben. In unserem Fall wird die Funktion generate_subscripts verwendet. Es werden nicht nur viele Zeiger auf die Position des Arrays generiert, das als Parameter an das Array ü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. Weitergabe der ursprünglichen Zeichenfolge mithilfe von generate_subscripts .| Restriktionsname | Typ | Array von Attributnummern | Zeiger auf eine Array-Position |
|---|
| fk_street_locality | f | {1,2} | 1 |
| fk_street_locality | f | {1,2} | 2 |
der Quellcode des Operators 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 unter der Bedingung attr.attrelid = tbl.oid AND attr.attnum = con.conkey [con.No] daraus extrahiert werden .
Jetzt müssen Sie nicht mehr unnötige Datensätze entfernen, indem Sie Datensätze gruppieren und aus den Namen der Attribute eine Zeichenfolge erstellen.
Das Erstellen einer Zeile erfolgt mit der Aggregationsfunktion STRING_AGG , in der Sie die Sortieroption (ORDER BY) angeben müssen. Andernfalls stimmt die Reihenfolge der Attribute möglicherweise nicht mit der Reihenfolge der Deklaration der Attribute im Index überein.
Die Ausführungszeit beider Versionen der Funktionen stimmte überein. Die Ausgabe der Daten in der Ergebnistabelle dauerte 20 ms.
Admtf_Table_Indexes Funktionsliste der Datenbanktabellenindizes und ihrer Eigenschaften
Die Funktion admtf_Table_Indexes gibt eine Liste der Indizes (INDEX) der Datenbanktabelle und ihrer Merkmale zurück. Der Quellcode kann hier angezeigt und heruntergeladen werden . Hier ist eine Version einer Funktion, die keinen Cursor verwendet .
Als Parameter verwendet die Funktion den Namen der
Quelltabelle (
a_TableName ) und den Namen des Schemas, in dem die Tabelle erstellt wird (
a_SchemaName ).
der Quellcode des Operators 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;
Die Beschreibung eines einzelnen Index ist eine Kombination aus einem Datensatz in pg_class , der ihn als physikalische Beziehung beschreibt, und einem Datensatz in 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;
Ein Attribut der Index-Eindeutigkeit ( indisunique ), ein Zeichen dafür, dass der Index gemäß der Beschreibung des Primärschlüssels ( indisprimary ) erstellt wurde, sowie Arrays von Seriennummern der Tabellenattribute, basierend auf den Werten, deren Index ( indkey ) und Zeichen der Reihenfolge der Sortierattributwerte aus dem Katalogeintrag pg_index extrahiert werden , werden extrahiert im Index ( indoption ).
Aus dem Katalogeintrag, der die Zugriffsmethode des pg_am- Index beschreibt, werden das Attribut der Eignung der im Index enthaltenen Daten für die Sortierung ( amcanorder ) und der Name oder Typ der Zugriffsmethode des Index ( amname ) extrahiert .
Mit anderen Worten, das amcanorder-Attribut gibt an, ob es möglich ist, eine Sortierreihenfolge für die Werte der im Index enthaltenen Attribute festzulegen. Wenn amcanorder = true ist , kann die Sortierreihenfolge angegeben werden, andernfalls nicht. Aus derselben Abbildung ist die Bedeutung der Werte des Indoptionsarrays ersichtlich. Wenn das rechte Bit der Binärform des Werts 1B enthält, wird der Wert des entsprechenden Attributs in absteigender Reihenfolge sortiert, andernfalls in aufsteigender Reihenfolge.
Innerhalb der Funktion werden mithilfe eines Zyklus Listen mit Namen von Attributen erstellt, die im Index enthalten sind, sowie Zeichen für die Reihenfolge der Attributwerte.
Tabelle 9. Das Ergebnis der Ausführung der Funktion admtf_Table_Indexes ('public', 'Street').
Die Textversion der Tabelle in der Abbildung| Indexname | Methode | ? Einzigartig | ? Primärschlüssel | Attribute im Index |
|---|
| xie1street | btree | f | f | wcrccode ASC, localityid ASC, streettypeacrm ASC, Straßenname ASC |
| xie2stree | btree | f | f | wcrccode ASC, localityid ASC, Straßenname ASC |
| xie3street | btree | f | f | Straßenname ASC |
| xie9street | btree | f | f | wcrccode ASC, localityid ASC, Straßenname 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 ist genau der gleiche wie im vorherigen Abschnitt beschrieben:
- Reproduktion von Datensätzen mit generate_subscripts;
- nachfolgende Gruppierung von Datensätzen;
- Erstellen einer Liste von Indexattributen mit der Funktion STRING_AGG mit der Option ORDER BY.
der Quellcode des Operators 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 stimmte überein. Ich brauchte 20 ms, um die Daten in der Ergebnistabelle auszugeben.
Daher werde ich keine Funktionsversionen mehr produzieren, wie Wer möchte, kann sie nach seinen Wünschen neu gestalten oder mich kontaktieren. Ich werde eine modifizierte Version kostenlos senden .
Siehe auch den ersten , dritten und vierten Teil des Artikels.
ANHANG 1. Skripte
Erstellen der Funktion admtf_Table_Constraintes
Kommentare zum Funktionsquellcode 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; v_ConstraintOID INTEGER; v_ConstraintKeyNos SMALLINT[]; v_ConstraintName name; v_ConstraintType name; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName name; v_ConstraintKeyNames TEXT; v_RefTableOID INTEGER; v_RefTableName name; v_RefTableKeyNos SMALLINT[]; v_RefTableKeyNames TEXT; v_ConstraintSource TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Erstellen einer Version von admtf_Table_Constraintes ohne Cursor
Kommentare zum Funktionsquellcode 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; v_ConstraintOID INTEGER; v_ConstraintKeyNos SMALLINT[]; v_ConstraintName name; v_ConstraintType name; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName name; v_ConstraintKeyNames TEXT; v_RefTableOID INTEGER; v_RefTableName name; v_RefTableKeyNos SMALLINT[]; v_RefTableKeyNames TEXT; v_ConstraintSource TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Erstellen der Funktion admtf_Table_Indexes
Kommentare zum Funktionsquellcode 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; v_IndexOID INTEGER; v_IndexKeyNos SMALLINT[]; v_IndexName NAME; v_IndexAMName NAME; v_isUnique BOOLEAN; v_isPrimary BOOLEAN; v_AttributeNum INTEGER; v_AttributeName NAME; v_IndexKeyNames TEXT; c_Delimiter CONSTANT VARCHAR(2):=',';
Erstellen einer Version von admtf_Table_Indexes ohne Cursor
. 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):=', ';
Siehe auch
PostgreSQL. ;PostgreSQL. .PostgreSQL. ( ) .