Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Teil eins

Während der Arbeit mit PostgreSQL haben sich mehr als hundert Funktionen für die Arbeit mit Systemverzeichnissen angesammelt: pg_class, pg_attribute, pg_constraints usw.


Was tun mit ihnen? Sie werden relativ selten eingesetzt. In ein Projekt einbeziehen? Der Krasnojarsker Kunde wird für solchen "Unsinn" nicht bezahlen. Und doch, was ist, wenn sie jemand anderem als dem Autor nützlich sind? Und er beschloss, sie wie gelesene Bücher in einen öffentlichen Schrank für diejenigen zu stellen, die es wünschen.

Jemand möchte sie in seiner Arbeit verwenden. Und jemand, der sich für etwas anderes als seine Erfahrung mit Systemkatalogen interessiert.

Aber um die Veröffentlichung nicht in eine langweilige Aufzählung zu verwandeln, ist nicht bekannt, warum die Funktionen erstellt wurden. Ich habe mich entschlossen, auf diejenigen von ihnen einzugehen, die durch ein gemeinsames Ziel vereint werden können. Daher werden die Funktionen ausgewählt, mit denen eine erweiterte Liste von Merkmalen einer beliebigen Datenbanktabelle angezeigt wird.

Die erweiterte Liste der Datenbanktabellenmerkmale wird von der Funktion admtf_Table_ComplexFeatures zurückgegeben , die in diesem Artikel als Kopffunktion bezeichnet wird. Daher beschränkt sich der Artikel auf die Berücksichtigung von Funktionen, die beim Ausführen der Kopffunktion aufgerufen werden.

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 zwei ;
Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Teil drei .
Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Das Ende (Teil vier) .




Über welche erweiterten Funktionen sprechen wir?


Um eine Vorstellung davon zu bekommen, was unter den erweiterten Merkmalen der Datenbanktabelle zu verstehen ist, betrachten wir zunächst die folgende Liste von Merkmalen. Die Liste enthält die Merkmale der Street-Datenbanktabelle, die von admtf_Table_ComplexFeatures ( 'public' , 'street' ) zurückgegeben wird .

Die folgende Tabelle enthält eine abgekürzte Liste der Street-Tabellenfunktionen. Ein vollständiger Satz von Merkmalen dieser Tabelle ist in den ergänzenden Materialien von Anhang 2 angegeben.

Tabelle 1. Erweiterte Funktionen der Street-Tabelle.

Die Textversion der Tabelle in der Abbildung
KategorieNein, nein.TitelKommentarTypBasistyp? nicht NULL
tbl0StraßeListe der Straßen in Siedlungen
att1wcrccodeLändercodewcrccodesmallintt
att2localityidCommunity IDlocalityidGanzzahlt
att3streetidID Straße des Dorfesstreetidsmallintt
att4StreettypeacrmStraßenakronymStreettypeacrmZeichen (8)f
att5StraßennameStraßennameStreettypeacrmvarchar (150)t
pk0xpkstreetStraßentisch Primärschlüssel
pkatt1wcrccodeLändercodewcrccodesmallintt
fk011fk_street_localityFremdschlüssel der Tabelle
fk022fk_street_streettypeFremdschlüssel der Tabelle
idx011xie1streetIndex nach Typ und Straßenname der Siedlung
idx022xie2streetPostleitzahl
idx033xie3streetIndex der Straßennamen aller Siedlungen
idx044xpkstreetDer eindeutige Index (Primärschlüssel) der Straßentabelle


Wenn Sie die Feindseligkeit gegenüber diesem Satz von Buchstaben und Zahlen aufzählen, können Sie sehen, dass es sich um die üblichen Merkmale einer Datenbanktabelle handelt:


  • Tabellennamen;
  • Die Liste der Tabellenattribute und ihrer Typen;
  • Der Primärschlüssel und die Liste der Fremdschlüssel der Tabelle zusammen mit den Attributen der Tabelle, aus denen sie bestehen.
  • Eine Liste der Tabellenindizes.

Die Eindeutigkeit jedes Eintrags aus der Liste der Merkmale wird durch die Werte der Felder "Kategorie" und die Seriennummer ("Nr.") Des Merkmals sichergestellt.



Tabelle 2. Kategorien von Tabelleneigenschaften.

Die Textversion der Tabelle in der Abbildung
AkronymTermin
tbTabelleneigenschaften
attEigenschaften von Tabellenattributen
seqSequenzcharakteristika
pkHauptmerkmale
pkAttMerkmale der Primärschlüsselattribute
fk99Fremdschlüsselmerkmale
fk99attFremdschlüsselattributmerkmale
fk99rtblMerkmale der Tabelle, auf die der Fremdschlüssel verweist
fk99rattAttributmerkmale der Tabelle, auf die der Fremdschlüssel verweist
Idx99Indexmerkmale
Idx99attIndexattributmerkmale
Inhtbl99Eigenschaften der generierten Tabelle


Der Kategoriewert ist erforderlich, um verschiedene Gruppen von Merkmalen voneinander zu unterscheiden. Eine Seriennummer, um die Merkmale innerhalb der Gruppe zu unterscheiden.


In der Datenbanktabelle können mehrere Fremdschlüssel (FOREIGN KEY) und Indizes deklariert werden. Daher enthält der Kategoriewert für diese Merkmale und ihre Nachkommen eine Seriennummer. Beispielsweise gibt ein Eintrag mit dem Schlüssel "Category" = idx02att und "No." = 1 das erste Attribut des 2. Index an.


In der obigen Liste der Kategorien wird der Speicherort der Seriennummer als "99" angegeben.


Bemerkung 1



der Quellcode des Operators in der Abbildung
SELECT * FROM admtf_Table_ComplexFeatures('pg_catalog','pg_class'); 



Der Artikel enthält Beispiele für die Eigenschaften von Tabellen, die in einem Hilfsschema kurz beschrieben werden , das speziell zur Demonstration der Funktionen von Funktionen erstellt wurde. Nachdem der Leser diese oder jene Funktion in seiner Datenbank erstellt hat, kann er die Namen seiner Schemata und Tabellen als Parameter verwenden. Darüber hinaus kann beispielsweise das Verzeichnis pg_class als Parameter verwendet werden, obwohl in diesem Fall eine begrenzte Anzahl von Merkmalen ausgegeben wird.

Das Ende der Bemerkung.



Struktur der Kopffunktion



Abb. 1. Die Funktionen, die die Kopffunktion verursachen.

Tabelle 3. Zuweisung von Funktionen.

Die Textversion der Tabelle in der Abbildung
Nein, nein.TitelTermin
1admtf_Table_FeaturesDie Funktion gibt eine Liste der Datenbanktabellenmerkmale zurück
2admtf_Table_AttributesDie Funktion gibt eine Liste der Datenbanktabellenattribute und ihrer Eigenschaften zurück.
3admtf_Table_ConstraintesDie Funktion gibt eine Liste der Datenbanktabelleneinschränkungen und ihrer Eigenschaften zurück.
4admtf_Table_IndexesDie Funktion gibt eine Liste der Datenbanktabellenindizes und ihrer Merkmale zurück.
5admtf_Table_InheritanceChildrensDie Funktion gibt eine Liste der von (IHERITS) aus der Quelldatenbanktabelle generierten Tabellen zurück.
6admtf_Table_SequencesDie Funktion gibt eine Liste von Sequenzen (SEQUENCE) zurück, von denen die Tabelle abhängt.
7admtf_PrimaryKey_ComplexFeaturesDie Funktion gibt eine vollständige (erweiterte) Liste der Primärschlüsselmerkmale (PRIMARY KEY) der Datenbanktabelle zurück.
8admtf_ForeignKey_ComplexFeaturesDie Funktion gibt eine vollständige (erweiterte) Liste der Fremdschlüsselmerkmale (FOREIGN KEY) der Datenbanktabelle zurück.
9admtf_Index_ComplexFeaturesDie Funktion gibt eine vollständige (erweiterte) Liste der Merkmale des Datenbanktabellenindex zurück.
10admtf_Table_ComplexFeaturesDie Funktion gibt eine vollständige (erweiterte) Liste der Datenbanktabellenmerkmale zurück.


Bemerkung 2.


Funktionsbeschreibungen werden in der oben angegebenen Reihenfolge angeordnet. Der Grund ist, dass der Artikel in mehrere Teile unterteilt werden muss. Und Funktionen, die in dieser Reihenfolge angeordnet sind, können verwendet werden, unabhängig davon, dass einige von ihnen nur in den folgenden Teilen der Veröffentlichung beschrieben werden.


Das Ende der Bemerkung.



Admtf_Table_Features-Funktionsliste der Datenbanktabellenmerkmale




Die Funktion admtf_Table_Features gibt eine Liste der Merkmale der Datenbanktabelle selbst zurück. Der Quellcode kann hier angezeigt und heruntergeladen werden.


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 Funktion extrahiert die Hauptdaten aus dem Katalogeintrag pg_class , der neben Tabelleneinträgen auch Datensätze zu Sequenzen, Ansichten, materialisierten Ansichten und zusammengesetzten Typen enthält. Daher wird zur Auswahl von Tabellen die Bedingung relkind = 'r' verwendet .



 SELECT tbl.relname,dsc.description,tbl.relnatts::INTEGER,tbl.relchecks::INTEGER, tbl.relhaspkey,tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::INTEGER FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid LEFT OUTER JOIN pg_description dsc ON tbl.oid=dsc.objoid AND dsc.objsubid=0 WHERE LOWER( nspc.nspname)=LOWER(a_SchemaName) AND tbl.relkind='r' AND LOWER(tbl.relname) =LOWER(a_TableName); 

Zusätzlich greift die Funktion auf die Verzeichnisdaten pg_namespace und pg_description zu . Das erste enthält die Namen der Datenbankschemata und das zweite enthält Kommentare zu allen Datenbankobjekten.


Es ist wichtig, auf die Bedingung objsubid = 0 zu achten. Es definiert den Kommentar zur Tabelle, da der Wert des Feldes objoid sowohl für die Tabelle als auch für ihre Attribute gleich ist. Der Kommentar zum Attribut der Tabelle ist in dem Eintrag enthalten, in dem objsubid mit der Nummer dieses Attributs übereinstimmt .



Tabelle 4. Das Ergebnis der Ausführung der Funktion admtf_Table_Features ('public', 'Street').

TitelKommentarAnzahl der AttributeAnzahl der CHECK-Einschränkungen? Gibt es einen Primärschlüssel?? sind Indizes deklariert? Gibt es Nachkommen?Die Anzahl der Einträge in der Tabelle
StraßeListe der Straßen in Siedlungen220ttf20150

Bemerkung 3


Achten Sie auf die Anzahl der Straßentabellenattribute. Es unterscheidet sich erheblich von der Anzahl der im Hilfsschema angegebenen Attribute.


Tabelle 5. Zusätzliche Attribute der Street-Tabelle.


attnameatttypidattnumHinweis
cmin29-4Systemattribut
xmin28-3Systemattribut
ctid27-1Systemattribut
wcrccode7953691Gültiges Attribut
localityid7953522Gültiges Attribut
streetid7953643Gültiges Attribut
Streettypeacrm19191684Gültiges Attribut
Straßenname10435Gültiges Attribut
........ pg.dropped.6 ........06Remote-Attribut
........ pg.dropped.7 ........07Remote-Attribut

Tatsache ist, dass PostgreSQL zusätzlich zu den Hauptattributen zusätzlich mehrere Systemattribute und sogar gelöschte Attribute berücksichtigt.


Bemerkung beenden



Funktion admtf_Table_Attributes Liste der Datenbanktabellenattribute und ihrer Eigenschaften


Die Funktion admtf_Table_Attributes gibt eine Liste der Datenbanktabellenattribute zurück. Der Quellcode kann hier angezeigt und heruntergeladen werden.


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 Funktion ruft die Hauptdaten aus den Verzeichniseinträgen pg_attribute und pg_type ab . Der erste enthält Datensätze mit Daten zu den Attributen von Tabellen, Ansichten, materialisierten Ansichten, zusammengesetzten Typen und sogar Funktionen. Die zweite betrifft die Eigenschaften von Attributtypen.


Möglicherweise ist eine Klarstellung erforderlich, wie die Benutzer- und Basistypen in der Funktion definiert sind.


Ein Tabellenattribut wird mit einem benutzerdefinierten Typ deklariert, wenn das Feld typbasetype im entsprechenden Verzeichniseintrag pg_type größer als 0 ist. Andernfalls ist das Attribut vom Basistyp. Daher ist das Verzeichnis pg_type zweimal an der FROM-Klausel beteiligt. Im ersten Verzeichniseintrag wird das Vorhandensein eines Benutzertyps ermittelt. Wenn dieser nicht definiert ist ( typbasetype = 0 ), wird aus diesem Eintrag ein Basistypwert generiert. Andernfalls wird der Basistyp aus dem Datensatz bestimmt, für den btyp.OID = typ.typbasetype ist.


Direkt wird die Linie mit dem Basistyp mit der Funktion des Systemkatalogs FORMAT_TYPE (type_oid, typemod) gebildet . Der erste Parameter sind die OID-Datensätze des Basistyps. Der zweite Parameter ist der Modifikatorwert für Typen, die eine Größe enthalten. Zum Beispiel VARCHAR (100) oder NUMERIC (4,2), DECIMAL (4,2). Der Wert des typemod- Parameters wird von typ.typtypmod übernommen, wenn das Attribut von einem Benutzertyp ist, andernfalls von attr.atttypmod , d. H. direkt aus einem Attributdatensatz.



der Quellcode des Operators in der Abbildung
 SELECT attr.attnum, attr.attname::VARCHAR(100), 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, dsc.description FROM pg_attribute attr INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE LOWER( nspc.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname) =LOWER(a_TableName) AND tbl.relkind='r' AND attr.attnum>0 AND attr.atttypID>0 ORDER BY tbl.relname,attr.attnum; 


Zusätzlich greift die Funktion auf die Verzeichnisdaten pg_class , pg_namespace und pg_description zu . Das erste und zweite Verzeichnis werden verwendet, um nach Attributen anhand der Namen des Schemas und der Datenbanktabelle zu suchen.

Das dritte Verzeichnis wird verwendet, um den Kommentar für das Tabellenattribut abzurufen.


Der Kommentar zum Attribut der Tabelle befindet sich in dem Eintrag, in dem dsc.objoid die OID der Quelltabelle enthält, und dsc.objsubid ist die Seriennummer des Attributs in der Tabelle, d. H. attr.attnum .


Um zu verhindern, dass die Funktion System- und Remote-Attribute zurückgibt , setzt die WHERE-Klausel die Bedingung attr.attnum> 0 AND attr.atttypID> 0 .



Tabelle 6. Das Ergebnis der Ausführung der Funktion admtf_Table_Attributes ('public', 'Street').

Nein, nein.TitelBenutzerdefinierter TypBasistyp? nicht NULLKommentar
1wcrccodewcrccodesmallinttLändercode
2localityidlocalityidGanzzahltCommunity ID
3streetidstreetidsmallinttID Straße des Dorfes
4StreettypeacrmStreettypeacrmZeichen (8)fStraßenakronym
5Straßennamevarchar (150)tName der Straße der Siedlung

Funktionsversion mit Regclass-Alias ​​für Typ oid


PostgreSQL-Objektkennungen (OIDs) sind vom gleichen Typ wie OID, die derzeit als vorzeichenlose 4-Byte-Ganzzahl implementiert ist. Aufgrund des Vorhandenseins von Aliasnamen dieses Typs kann eine Ganzzahl als Name eines Objekts dargestellt werden. Und umgekehrt - konvertieren Sie den Objektnamen in eine Ganzzahl vom Typ OID.


Schauen Sie sich als Beispiel die folgende SELECT-Anweisung an . Es ruft ungewöhnlich die Namen der Attributtabelle und die Namen ihrer Typen ab - anstatt auf die entsprechenden Verzeichnisfelder mit den Namen dieser Merkmale zuzugreifen, werden sie verwendet:

  • attrelid :: regclass (attrelid :: regclass: NAME) ,
  • atttypid :: regtype (atttypid :: regtype: NAME)
  • typbasetype :: regtype (typbasetype :: regtype: NAME) .


 SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype, typ.typbasetype::regtype,attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=('public'||'.'||'Street')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; 

Das Folgende ist das Ergebnis der Ausführung dieser Abfrage.



In der Liste der Ausgabewerte des Operators SELECT vor der Konvertierung unter Verwendung von Aliasnamen des Typs OID sind alle Werte außer dem Attributnamen numerisch. Infolgedessen werden die Tabellennamen und Attributtypen angezeigt. Die Arten von Ausgabewerten können in der zweiten Zeile des Tabellenkopfs berücksichtigt werden.


Außerdem befindet sich in der WHERE-Klausel der Anweisung die Bedingung attr.attrelid = ('public' || '.' || 'Street') :: regclass , auf deren linker Seite sich ein numerischer Wert befindet und auf der rechten Seite eine Zeichenfolge, die mit in einen numerischen Wert konvertiert wird Verwenden der Alias- Regklasse .



der Quellcode des Operators in der Abbildung
 SELECT attr.attnum, attr.attname::VARCHAR(100), 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, dsc.description FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid LEFT OUTER JOIN pg_description dsc ON dsc.objoid=attr.attrelid AND dsc.objsubid=attr.attnum WHERE attr.attrelid=( a_SchemaName ||'.'|| a_TableName)::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; 


Mit der Alias-Regklasse aus der Hauptanweisung können Sie die Verbindung zu zwei Verzeichnissen entfernen. Eine solche Verbesserung hatte jedoch fast keine Auswirkungen auf die Leistung der Funktion - in beiden Versionen wird die Funktion in 11 ms ausgeführt. Möglicherweise aufgrund der Tatsache, dass die Testtabelle nur wenige Attribute aufweist.
Bemerkung 4

Ein schwerwiegender Fehler in der Bedingung in der Form attr.attrelid = (a_SchemaName || '.' || a_TableName) :: regclass wird angezeigt, wenn die Datenbank ein Schema und / oder eine Tabelle mit einem ungewöhnlichen Namen enthält. Zum Beispiel "Mein Schema" und / oder "Meine Tabelle" . Solche Werte müssen in doppelten Anführungszeichen übertragen werden oder die Funktion QUOTE_IDENT verwenden, da sonst die Funktion mit einem Systemfehler fehlschlägt.



der Quellcode des Operators in der Abbildung
 /*       «   »*/ SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype, attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=(' '||'.'||' ')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; /*       */ SELECT attr.attname,attr.attrelid::regclass, attr.atttypid::regtype,typ.typbasetype::regtype, attr.attrelid::regclass::name, attr.atttypid::regtype::name,typ.typbasetype::regtype::name FROM pg_attribute attr INNER JOIN pg_type typ ON attr.atttypid=typ.oid WHERE attr.attrelid=('" "'||'.'||'" "')::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; 


Daher bevorzuge ich Bedingungen in der Form LOWER (nspc.nspname) = LOWER (a_SchemaName) UND LOWER (tbl.relname) = LOWER (a_TableName) , was nicht zu Systemfehlern führt.

Bemerkung beenden

ANHANG 1. Skripte


Erstellen der Funktion admtf_Table_Features


Kommentare zum Funktionsquellcode finden Sie hier.
Funktionsquellcode
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Features (a_SchemaName NAME,a_TableName NAME); /********************************************************************************************************/ /*     ,   */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Features (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (rs_TableName NAME,rs_TableDescription TEXT,rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_TableOID OID; /*   */ v_TableName NAME; /*   */ v_TableDescription TEXT; /*   */ v_TableNumberOfRowCalc INTEGER; /*     */ --****************************************************************************************************** BEGIN SELECT INTO rs_TableName,rs_TableDescription,rs_NumberOfAttribute, rs_NumberOfChecks,rs_hasPKey,rs_hasIndex,rs_hasSubClass, rs_NumberOfRow tbl.relname,dsc.description,tbl.relnatts::INTEGER,tbl.relchecks::INTEGER, tbl.relhaspkey,tbl.relhasindex,tbl.relhassubclass,tbl.reltuples::INTEGER FROM pg_class tbl INNER JOIN pg_namespace nspc ON tbl.relnamespace = nspc.oid LEFT OUTER JOIN pg_Description dsc ON tbl.oid=dsc.objoid AND dsc.objsubid=0 WHERE nspc.nspname=LOWER(a_SchemaName) AND tbl.relkind=c_TableKind AND tbl.relname =LOWER(a_TableName); EXECUTE 'SELECT count(*) FROM ' ||LOWER(a_SchemaName) ||'.'||quote_ident(LOWER(a_TableName)) INTO v_TableNumberOfRowCalc; RETURN QUERY SELECT rs_TableName,rs_TableDescription,rs_NumberOfAttribute, rs_NumberOfChecks,rs_hasPKey,rs_hasIndex, rs_hasSubClass,v_TableNumberOfRowCalc AS rs_NumberOfRow; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Features(a_SchemaName NAME,a_TableName NAME) IS '   ,  '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Features (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************************************************/ /*     ,   */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Features (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (rs_TableName VARCHAR(256),rs_TableDescription TEXT,rs_NumberOfAttribute INTEGER,rs_NumberOfChecks INTEGER,rs_hasPKey BOOLEAN,rs_hasIndex BOOLEAN,rs_hasSubClass BOOLEAN,rs_NumberOfRow INTEGER) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_TableOID OID; /*   */ v_TableName VARCHAR(256); /*   */ v_TableDescription TEXT; /*   */ v_TableNumberOfRowCalc INTEGER; /*     */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT tf.rs_TableName::VARCHAR(256), tf.rs_TableDescription::TEXT, tf.rs_NumberOfAttribute::INTEGER, tf.rs_NumberOfChecks::INTEGER, tf.rs_hasPKey::BOOLEAN, tf.rs_hasIndex::BOOLEAN, tf.rs_hasSubClass::BOOLEAN, tf.rs_NumberOfRow::INTEGER FROM admtf_Table_Features(a_SchemaName::NAME,a_TableName::NAME) tf; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Features(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS '   ,  '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECt * FROM admtf_Table_Features('public'::VARCHAR(256),'Street'::VARCHAR(256)); SELECt * FROM admtf_Table_Features('public':: NAME,'Street'::NAME); 



Erstellen der Funktion admtf_Table_Attributes


Kommentare zum Funktionsquellcode finden Sie hier.
Funktionsquellcode
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT 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, dsc.description AS r_Description FROM pg_attribute attr INNER JOIN pg_class tbl ON tbl.oid = attr.attrelid INNER JOIN pg_namespace nsp ON tbl.relnamespace=nsp.oid 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 LOWER(nsp.nspname)=LOWER(a_SchemaName) AND LOWER(tbl.relname)=LOWER(a_TableName) AND tbl.relkind=c_TableKind AND attr.attnum>0 AND attr.atttypID>0 ORDER BY tbl.relname,attr.attnum; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName NAME,a_TableName NAME) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (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_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT ta.r_AttributeNumber::SMALLINT, ta.r_AttributeName::VARCHAR(256), ta.r_UserTypeName::VARCHAR(256), ta.r_TypeName::VARCHAR(256), ta.r_isNotNULL::BOOLEAN, ta.r_Description::TEXT FROM admtf_Table_Attributes(a_SchemaName::NAME,a_TableName::NAME) ta; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_Attributes('public'::VARCHAR(256),'Street'::VARCHAR(256)); SELECT * FROM admtf_Table_Attributes('public'::NAME,'Street'::NAME); 


Erstellen der Funktion admtf_Table_Attributes mithilfe des Regclass-Alias


Kommentare zum Funktionsquellcode finden Sie hier.
Funktionsquellcode
 BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName NAME,a_TableName NAME); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName NAME default 'public', /*     */ a_TableName NAME default NULL /*   */ ) RETURNS TABLE (r_AttributeNumber SMALLINT,r_AttributeName NAME,r_UserTypeName NAME,r_TypeName NAME,r_isNotNULL BOOLEAN, r_Description Text) AS $BODY$ DECLARE c_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT 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, dsc.description AS r_Description FROM pg_attribute attr 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 attr.attrelid=(LOWER(a_SchemaName)||'.'|| LOWER(a_TableName))::regclass AND attr.attnum>0 AND attr.atttypID>0 ORDER BY attr.attnum; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName NAME,a_TableName NAME) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; BEGIN TRANSACTION; DROP FUNCTION IF EXISTS admtf_Table_Attributes (a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)); /********************************************************************************************************/ /*      */ /********************************************************************************************************/ CREATE OR REPLACE FUNCTION admtf_Table_Attributes (a_SchemaName VARCHAR(256) default 'public', /*     */ a_TableName VARCHAR(256) default NULL /*   */ ) RETURNS TABLE (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_TableKind CONSTANT CHAR:='r'; v_Scale INTEGER; /*   */ --****************************************************************************************************** BEGIN RETURN QUERY SELECT ta.r_AttributeNumber::SMALLINT, ta.r_AttributeName::VARCHAR(256), ta.r_UserTypeName::VARCHAR(256), ta.r_TypeName::VARCHAR(256), ta.r_isNotNULL::BOOLEAN, ta.r_Description::TEXT FROM admtf_Table_Attributes(a_SchemaName::NAME,a_TableName::NAME) ta; RETURN; END $BODY$ LANGUAGE plpgsql; COMMENT ON FUNCTION admtf_Table_Attributes(a_SchemaName VARCHAR(256),a_TableName VARCHAR(256)) IS '   '; --ROLLBACK TRANSACTION; COMMIT TRANSACTION; SELECT * FROM admtf_Table_Attributes('public'::VARCHAR(256),'Street'::VARCHAR(256)); SELECT * FROM admtf_Table_Attributes('public'::NAME,'Street'::NAME); 



ANHANG 2. Zusätzliche Materialien


Hilfsdatenbankschema





  • LAND - Klassifikator der Länder der Welt - OKSM (Allrussische Klassifikation der Länder der Welt);
  • HOUSEADDR - Liste der Hausnummern auf den Straßen von Siedlungen;
  • LCLTYTYPE - Verzeichnis der Siedlungsarten;
  • LOKALITÄT - Liste der Siedlungen;
  • STRASSE - Liste der Straßen in Siedlungen;
  • STREETTYPE — ;
  • TERRITORY — (, , , ..);
  • TERRITORYTYPE — .



Street ()


.
1. Street ().

KategorieNein, nein.TitelKommentar? not NULL
tbl0street
att1wcrccodewcrccodesmallintt
att2localityidlocalityidintegert
att3streetidstreetidsmallintt
att4streettypeacrmstreettypeacrmcharacter(8)f
att5streetnamestreettypeacrmvarchar(150)t
pk0xpkstreetstreet
pkatt1wcrccodewcrccodesmallintt
pkatt2localityidlocalityidintegert
pkatt3streetidstreetidsmallintt
fk011fk_street_locality
fk01att1wcrccodewcrccodesmallintt
fk01att2localityidlocalityidintegert
fk01rtbl0locality
fk01ratt1wcrccodewcrccodesmallintt
fk01ratt2localityidlocalityidintegert
fk022fk_street_streettype
fk02att1streettypeacrmstreettypeacrmcharacter(8)f
fk02rtbl0streettype
fk02ratt1streettypeacrmstreettypeacrmcharacter(8)t
idx011xie1street
idx01att1wcrccodewcrccodesmallintt
idx01att2localityidlocalityidintegert
idx01att3streettypeacrmstreettypeacrmcharacter(8)f
idx01att4streetnamevarchar(150)t
idx022xie2street
idx02att1wcrccodewcrccodesmallint
idx02att2localityidlocalityidintegert
idx02att3streetnamevarchar(150)t
idx033xie3street
idx03att1streetnamevarchar(150)t
idx044xpkstreet( ) street
idx04att1wcrccodewcrccodesmallintt
idx04att2localityidlocalityidintegert
idx04att3streetidstreetidsmallintt


Siehe auch


Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. ;
Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Teil drei .
Funktionen zum Dokumentieren von PostgreSQL-Datenbanken. Das Ende (Teil vier) .

Source: https://habr.com/ru/post/de415575/


All Articles