Hallo Forum,
ich möchte in Postgres 8.3 automatisiert Strukturinformationen sammeln. Allerdings würde ich gerne die Indexnamen auch den Spalten zuordnen. Dabei möchte ich wissen, ob eine konkrete Tabellenspalte einen oder mehrere Indexe hat.
In psql hilft bekanntlich
\di
Startet man psql mit der Option “-E”, wird psql gesprächig und zeigt das dazugehörige SQL-Statement:
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as
"Type",
r.rolname as "Owner",
c2.relname as "Table",
pg_catalog.obj_description(c.oid, 'pg_class') as "Description"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind IN ('i','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
Etwas abgewandelt kann man die Suche schon auf eine konkrete Tabelle begrenzen ($schema und $table sind nur Platzhalter):
SELECT n.nspname as "schema_name",
c2.relname as "table_name",
c.relname as "index_name"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid
LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid
WHERE c.relkind='i'
AND n.nspname='$schema'
AND c2.relname='$table'
ORDER BY 1,2;
In der Ausgabe erscheint der Schema- und Tabellenname, nicht jedoch der Spaltenname. Ein Pattern-Matching auf den Index-Namen ist keine Lösung, weil der Index-Name frei gewählt werden kann.
Ich habe schon den pg_catalog durchsucht, aber keine Tabelle gefunden, die die Spaltennamen der Tabellen enthält. Davon abgesehen weiß ich schon nicht, ob die Tabelle pg_class überhaupt Informationen über die Spalten enthält. Nach meiner laienhaften Vorstellung muß diese Information irgendwo hinterlegt sein. Woher sollte der Query Planner wissen, ob er einen Index befragen kann…?
Vielen Dank
ratio