Wie Metadaten zu Tabellen/Typen extrahieren?

Hallo allerseits!

ich bin alles andere als ein erfahrener Datenbank-Programmierer, muss mich allerdings derzeit mit folgender Aufgabenstellung befassen:

Ich soll aus einer PostgreSQL-DB (PostgreSQL 9.5) bestimmte, fuer eine Anwendung wichtige Metadaten extrahieren. Konkret geht es um die von Benutzern angelegten Tabellen (deren Namen, Namen der Tabellenspalten sowie deren Datentypen) und um eventuell von Anwendern definierte und in ihren Tabellen benutzte Datentypen. Ich habe schon intensiv gegoogelt und immerhin einen Anfang geschafft.

  1. Mittels
SELECT table_name FROM information_schema.tables
  WHERE table_schema='public'
  AND table_type='BASE TABLE';

erhalte ich die fuer mich interessanten Tabellen.

  1. So ermittle ich zu einer Tabelle () die enthaltenen Spalten sowie Informationen zu deren Datentypen:
SELECT column_name, data_type, udt_name
  FROM information_schema.columns
  WHERE table_schema='public' AND table_name='<tablename>';
  1. Und auch, wie ich an custom datatypes reankomme, weiss ich schon (vorausgesetzt, ich kenne den Namen eines solchen Datentyps bereits (); etwa wenn er fuer ein Attribut einer zuvor analysierten Tabelle verwendet wurde):
SELECT attname, format_type(atttypid, atttypmod)
  FROM pg_type
  JOIN pg_class on pg_class.oid = pg_type.typrelid
  JOIN pg_attribute on pg_attribute.attrelid = pg_class.oid
  WHERE typname = '<typename>' 
  ORDER BY attnum;

Nun allerdings stecke ich fest. Wie wir oben in 2. sehen, kann ich zu einer Tabellenspalte den “data_type” und auch, sofern es sich um einen “USER-DEFINED” type handelt, den Namen dieses custom datatypes ermitteln. Wie ermittle ich die gleiche Information zu den Attributen eines custom datatypes?

Ich habe inzwischen rausbekommen, dass in PostgreSQL die erzeugten Tabellen auch implizit als Datentypen abgelegt sind. Also eigentlich muesste ich gar nicht zwischen Tabellen und custom datatypes unterscheiden und koennte stattdessen einfach alle user-defined custom datatypes auswerten. Im Prinzip muesste man nach meinem Verstaendnis eine Query definieren, mit der ich die betreffende Untermenge der Eintraege in dem Katalog pg_type erhalte.

Ausserdem muesste ich noch irgendwie ein paar zusaetzliche Informationen ermitteln:

  • Ist ein Typ ein enum-Type?


  • Bei custom datatypes: die Attribute und ihre Typen und zu denen die Information, ob es sich um arrays von Werten des jew. Typs handelt.


  • Ein custom datatype kann ja selbst wieder Attribute enthalten, deren Typen custom datatypes sind. Diese Eigenschaft muss ich also ebenfalls erkennen koennen.


  • Weiterhin sollte es moeglich sein, Datentypen, die numerische Werte speichern (also integer sowie die Fliesskommatypen) von anderen Typen, die Text, Datumsinformationen etc. speichern, zu unterscheiden.

Die Kommunikation mit der Datenbank implementiere ich in einem C+±Programm unter Linux (RHEL 6.x), welches die Qt-Bibliothek (Version 5.9.1) und die darin bereitgestellte Funktionalitaet zum Zugriff auf Datenbanken (QSqlDatabase, QSqlQuery, …) benutzt.

Waere toll, wenn ich ein paar sachdienliche Hinweise bekommen koennte.

Pffff…


Erst einmal: Willkommen im Forum hier.

Auf die Schnelle kann ich das nicht beantworten, das ist schon recht tief drin und das braucht man eher niemals in der täglichen Arbeit. Dir ist bewußt, daß sich diese internen Katalog-Tabellen von Version zu Version auch ändern können?

Oh! Damit hatte ich nicht gerechnet. Wenn die Tabellenstruktur, in der die “Metadaten”, also die in der DB enthaltenen Tabellen/-spalten/Datentypen usw. definiert sind, nicht einigermassen fix ist, dann habe ich natuerlich mit jedem Versionswechsel der DB potenziell ein Problem …
Vielen Dank! Das ist schon mal eine wichtige Information!

Ja, das passiert nicht oft, aber es passiert…

Vielen Dank! hatte schon geahnt, dass das nicht ganz einfach wird. Ich probiere das in den naechsten Tagen mal aus und melde mich dann noch mal.

OT: Hatte ein bisschen Probleme mit den Einstellungen meines Teilnehmeraccounts in diesem Forum, weshalb ich keine EMail-Nachricht erhielt, wenn jemand sich zu meinem Problem geaeussert hat. Hoffe, in Zukunft funktioniert das …

bekanntes Problem …

Habe inzwischen die ganz famose Query von Forenmitglied “castorp” ausprobiert. Gab einen klitzekleinen Typo in der letzten Zeile. Statt

ORDER BY column_position;

war ganz sicher gemeint

ORDER BY position;

Dann aber kommt zu dem von mir angegebenen user defined type eine Liste mit den Attribut-Namen, deren jew. Position in dem Typ sowie ihr eigener Typ raus. Das ist schon mal ganz grossartig!!!

Habe, glaube ich, auch im Grossen und Ganzen verstanden, wie diese Query funktioniert. Beim Ausprobieren an meinen vorhandenen user defined types sind mir noch zwei Phaenomene aufgefallen:

  1. Wenn ein Attribut einen Array-Type hat, dann erscheint hinter dem Type-Namen “[]”. Gaebe es vielleicht eine Moeglichkeit, diese Information irgendwie in der Ausgabe explizit darzustellen, also etwa in einer weiteren Ausgabespalte “is_array”, deren Wert true oder false (“t” oder “f” korrekt?) waere? Im Notfall koennte ich das natuerlich durch Testen des Typnamens auf “[]” am Ende des Namens rauskriegen. Das erscheint mir aber nicht besonders elegant.

  2. Ich habe auch mit einem user defined type zu tun, bei dem es sich um einen enum type handelt. Wende ich die Query auf so einen Typ an, dann wird kein Output erzeugt. Offenbar wird diese Art von user defined types irgendwie anders in den Metadaten gespeichert. Wie koennte man diese Information gewinnen, also “Typ ist ein enum-Typ” und am besten gleich noch die definierten Enum-Werte desselben?

Enums sind anders abgelegt:

select n.nspname as enum_schema,  
       t.typname as enum_name,  
       string_agg(e.enumlabel, ', ' order by e.enumsortorder) as enum_values
from pg_type t 
   join pg_enum e on t.oid = e.enumtypid  
   join pg_catalog.pg_namespace n ON n.oid = t.typnamespace
group by n.nspname, t.typname

Sehr schoen! Das hat mir sehr geholfen. Vielen Dank!