Spaltenanzahl einer Abfrage dynamisch Anhand anderer Tabelle erzeugen

Hallo zusammen,

ich wurde heute mit einem Problem konfrontiert, dass mir so bisher eigentlich nicht untergekommen ist :slight_smile:
Ich soll Abfrageergebnisse als csv-Datei liefern, die dann in Excel geladen werden und per Makro in Diagramme umgewandelt werden. Die Makros stehen schon und sollen erstmal nicht verändert werden, das heißt das CSV-Format ist mir genau vorgegeben. Leider erwarten diese Makros Ergebnisstabellen, bei denen die Ergebnisse nicht nur Zeilenweise sondern auch Spaltenweise dynamisch sind.
Ich bin mir nicht sicher, ob so ein Ansatz mit einer Datenbankanfrage überhaupt möglich ist, mir fällt zumindest im Moment nix Gescheites dazu ein. Zur Klärung hier mal ein Beispiel:

Nehmen wir an, wir haben eine Tabelle in der für alle Bauteile eines bestimmten Typs (z.B. Typ Stoßstange) Fehler beim Produktionsvorgang abgespeichert werden. Dafür haben wir eine Tabelle, in der alle aufgetretenen Fehler verzeichent sind, und zwar bezüglich eines Bauteils (für jede konkrete Stoßstange, die per id gezeichnet ist) und der Stelle, an der der Fehler auftrat (die Naht). Sie könnte z.B. so aussehen:

bauteil_id   Naht_id   Fehlercode
1            Naht_1         5
1            Naht_1         3
1            Naht_2         5
1            Naht_2         7
2            Naht_1         4
2            Naht_2         6
2            Naht_2         7

wobei bauteil_id und Naht_id eben foreign keys sind. An der Stoßstange mit der id 1 traten also die Fehler 5 und 3 an der Naht mit der Naht_id Naht_1 auf, die Fehler 5 und 7 an Naht_2. An der nächsten Stoßstange mit der id 2 trat dann an Naht_1 Fehler 4 auf usw.

Erwartet wird nun eine Abfrage, die z.B. für jedes Bauteil für alle Nähte des Bauteils (es gibt eine Tabelle in der alle möglichen Fehlerstellen (also Nähte) des Bauteils gelistet sind) angibt, ob dort Fehler aufgetreten sind oder nicht. Nehmen wir an, eine Stoßstange hätte 3 Nähte. Das Ganze sähe dann folgendermaßen aus:

Bauteil_id   Naht_1   Naht_2   Naht_3 usw.
1            1        1        0
2            1        1        0

d.h. pro Bauteil genau eine Zeile und eine dynamische Spaltenanzahl, nämlich pro Naht aus der Nahttabelle eine Spalte (betitelt mit der jeweiligen Naht_id). Als Wert steht in den Nahtspalten 0 falls kein Fehler an dieser Naht auftrat, 1 wenn 1 oder mehr Fehler auftraten, also quasi „Ist die Naht korrekt oder nicht“. Ich habe nun keine Ahnung, wie man dynamisch aufgrund von Informationen in einer anderen Tabelle (der Nahttabelle) die Spaltenanzahl erzeugen und diese dann auch noch mit Werten betiteln soll. Gibt es dafür irgendeinen Weg, den ich einfach nicht sehe? Ich rede hier davon, dass das Ganze durch eine Datenbankabfrage gelöst werden soll, nicht über die Möglichkeit, die Daten per Programm aus der Datenbank zu holen und dann ein Programm die gewünschte CSV-Datei zusammenbasteln zu lassen. Das ist natürlich möglich, wäre aber viel zu starr und ich müsste jeden Select extra behandeln (es gibt noch viel mehr Typen als die Stoßstange und alle haben eigene Nahtdefinitionen etc.).

Gibt es dafür eine Datenbankseitige Lösungsmöglichkeit? Ich hoffe das Problem ist verständlich geschildert, bei Rückfragen versuch ich natürlich noch mal, das Ganze zu erklären 

Dir reicht also die Ausgabe der CSV-Datei, als Tabelle brauchst Du es nicht wirklich, oder?

Erwartet wird nun eine Abfrage, die z.B. für jedes Bauteil für alle Nähte des Bauteils (es gibt eine Tabelle in der alle möglichen Fehlerstellen (also Nähte) des Bauteils gelistet sind) angibt, ob dort Fehler aufgetreten sind oder nicht. Nehmen wir an, eine Stoßstange hätte 3 Nähte. Das Ganze sähe dann folgendermaßen aus:

Bauteil_id Naht_1 Naht_2 Naht_3 usw.
1 1 1 0
2 1 1 0

>
> Gibt es dafür eine Datenbankseitige Lösungsmöglichkeit? Ich hoffe das Problem ist verständlich geschildert, bei Rückfragen versuch ich natürlich noch mal, das Ganze zu erklären 

Ich denke, das man das durchaus in plpgsql lösen könnte. Du mußt ja nur eine Tabelle erzeugen, die die CSV-Daten enthält, als TEXT, die Du dann ausgeben kannst.
Den Inhalt, also die CSV-Daten, mit plpgsql zu erstellen sollte nicht das Problem sein, man iteriert halt durch die Tabelle, schaut, welche Spalten bei entstehen, und generiert das CSV.
Falls ich heute Abend mal lange Weile habe, bastle ich vielleicht mal eine Demo, mal sehen. Hab 'ne schwangere Frau zu Hause... nicht immer Zeit deswegen.

Andreas

Hallo Andreas,

Dir reicht also die Ausgabe der CSV-Datei, als Tabelle brauchst Du es nicht wirklich, oder?

Jein, theoretisch reicht es mir erstmal als csv, ja. Es muss eben gewährleistet sein, dass die dynamischen Spaltennamen als erste Zeile des csv auftaucht.

Den Inhalt, also die CSV-Daten, mit plpgsql zu erstellen sollte nicht das Problem sein, man iteriert halt durch die Tabelle, schaut, welche Spalten bei entstehen, und generiert das CSV.

Hmm, jo, irgendwie würde das wohl schon funktionieren - faktisch ist es dann aber fast das Gleiche, wie wenn ich es mit einer externen Programmiersprache hole, sprich z.B. ein Java oder C# Progrämmchen schreib (ok, auf der Datenbank isses natürlich schneller, aber es ist eben extra eine Funktion geschrieben, um exakt diese eine Anfrage zu bearbeiten, also total starr). Früher oder später wird das wahrscheinlich eh passieren müssen, da ich eine Oberfläche erstellen muss, mit deren Hilfe Auswertungen eben dynamsich zusammengeklickt werden können. Ob ich dann allerdings noch auf Excel ausweiche oder Charts usw. einfach selbst generiere steht noch in den Sternen. Aus meiner Sicht ist es halt recht unflexibel, das auf programmatische Art zu lösen. Im Prinzip schwebt mir sowas vor, dass ich mir unter der oberfläche nen dynamsichen SQl-Genartor bau, der das Ganze dann mit Standard-Sql-Abfragen abhandelt. Vielleicht lieg ich da aber auch falsch und man kann das per PSQL so dynamisch gestalten, dass es wirklich für verschiedene Anfragearten passt. Im Prinzip sollten eigentlich neue Anfragekombinationen möglich sein, ohne dass ich dafür ne neue Funktion schreiben muss.

Ne Lösungsmöglichkeit in PSQL wär natürlich trotzdem net schlecht - deine schwangere Frau kann ja eh nix unternehmen :wink:

Vorweg: vielleicht hab ich Dich zumindest partiell falsch verstanden. Ich hab im folgenden auch auf Details wie CSV verzichtet, dafür aber es so gemacht, daß man Ergebnistabellen hat. Diese kann man ja trivial via COPY als CSV raushauen.

Nehmen wir an, wir haben eine Tabelle in der für alle Bauteile eines bestimmten Typs (z.B. Typ Stoßstange) Fehler beim Produktionsvorgang abgespeichert werden. Dafür haben wir eine Tabelle, in der alle aufgetretenen Fehler verzeichent sind, und zwar bezüglich eines Bauteils (für jede konkrete Stoßstange, die per id gezeichnet ist) und der Stelle, an der der Fehler auftrat (die Naht). Sie könnte z.B. so aussehen:

Hier mal meine Tabellen:

test=*# \d bauteile
                         Table "fehler.bauteile"
 Column |  Type   |                       Modifiers
--------+---------+-------------------------------------------------------
 id     | integer | not null default nextval('bauteile_id_seq'::regclass)
 name   | text    |
Indexes:
    "bauteile_pkey" PRIMARY KEY, btree (id)

test=*# \d fehlerorte
                         Table "fehler.fehlerorte"
 Column |  Type   |                        Modifiers
--------+---------+---------------------------------------------------------
 id     | integer | not null default nextval('fehlerorte_id_seq'::regclass)
 ort    | text    |
Indexes:
    "fehlerorte_pkey" PRIMARY KEY, btree (id)

test=*# \d bauteil_fehlerorte
 Table "fehler.bauteil_fehlerorte"
    Column    |  Type   | Modifiers
--------------+---------+-----------
 bauteil_id   | integer |
 fehlerort_id | integer |
Indexes:
    "idx_bauteil_fehlerorte" UNIQUE, btree (bauteil_id, fehlerort_id)
Foreign-key constraints:
    "bauteil_fehlerorte_bauteil_id_fkey" FOREIGN KEY (bauteil_id) REFERENCES bauteile(id)
    "bauteil_fehlerorte_fehlerort_id_fkey" FOREIGN KEY (fehlerort_id) REFERENCES fehlerorte(id)

test=*# \d fehler
      Table "fehler.fehler"
   Column   |  Type   | Modifiers
------------+---------+-----------
 bauteil_id | integer |
 ort_id     | integer |
 fehler     | integer |
Foreign-key constraints:
    "fehler_bauteil_id_fkey" FOREIGN KEY (bauteil_id, ort_id) REFERENCES bauteil_fehlerorte(bauteil_id, fehlerort_id)

Die tablen enthalten:

test=*# select * from fehler;
 bauteil_id | ort_id | fehler
------------+--------+--------
          1 |      1 |      5
          1 |      1 |      3
          1 |      2 |      5
          1 |      2 |      7
          2 |      1 |      4
          2 |      2 |      6
          2 |      2 |      7
          3 |      2 |      3
(8 rows)

test=*# select * from bauteile;
 id |   name
----+-----------
  1 | Bauteil 1
  2 | Bauteil 2
  3 | Bauteil 3
(3 rows)

test=*# select * from fehlerorte;
 id |  ort
----+--------
  1 | Naht 1
  2 | Naht 2
  3 | Naht 3
(3 rows)

test=*# select * from bauteil_fehlerorte ;
 bauteil_id | fehlerort_id
------------+--------------
          1 |            1
          1 |            2
          1 |            3
          2 |            3
          3 |            2
          3 |            3
          2 |            1
          2 |            2
(8 rows)



Erwartet wird nun eine Abfrage, die z.B. für jedes Bauteil für alle Nähte des Bauteils (es gibt eine Tabelle in der alle möglichen Fehlerstellen (also Nähte) des Bauteils gelistet sind) angibt, ob dort Fehler aufgetreten sind oder nicht. Nehmen wir an, eine Stoßstange hätte 3 Nähte. Das Ganze sähe dann folgendermaßen aus:

Ich hab mal eine Funktion geschrieben, für ein bestimmtes Bauteil die Summe aller Fehler für dieses Bauteil gültigen Fehlerorte ausgibt:

create or replace function fehler(PRODUKT int) returns setof text as $$
declare
        r1      record;
        r2      record;
        sql     text;
        g       text;
begin
        sql = 'select ' || $1 || ' as produkt';
        g = '';
        for r1 in select o.id, o.ort from fehlerorte o left join bauteil_fehlerorte b on b.fehlerort_id=o.id where b.bauteil_id = $1 order by 1 l
                return next r1.ort;
                sql = sql || ', sum(case when ort_id = ' || r1.id || ' then 1 else 0 end) as "' || r1.ort || '" ';
        end loop;
        sql = sql || 'from fehler where bauteil_id = ' || $1 || ' group by produkt;';
        raise notice '%', sql;
        execute sql;
end;
$$ language plpgsql;

Das ist so noch nicht fertig. Es baut einen String mit einem SQL-Befehl auf, den es als letztes ‘raise notice’ in voller Länge ausgibt (und dann auch ausführt, aber ohne ein Resultat zu vermelden) Du kannst das in psql probieren und mit C&P mal ausführen. Es erzeugt, aufgerufen mit der ID eines Bauteiles, dynamisch ein SQL, welches eine Table mit den Orten, wo Fehler auftreten können als Spaltennamen zurück.

Mit Testdaten:

test=*# select * from fehler(2);
NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1"
NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2"
NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3"
NOTICE:  select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3" from fehler where bauteil_id = 2 group by produkt;
 fehler
--------
 Naht 1
 Naht 2
 Naht 3
(3 rows)

-- nun via C&P

test=*# create table prod2 as select 2 as produkt, sum(case when ort_id = 1 then 1 else 0 end) as "Naht 1" , sum(case when ort_id = 2 then 1 else 0 end) as "Naht 2" , sum(case when ort_id = 3 then 1 else 0 end) as "Naht 3" from fehler where bauteil_id = 2 group by produkt;


test=*# select * from prod2 ;
 produkt | Naht 1 | Naht 2 | Naht 3
---------+--------+--------+--------
       2 |      1 |      2 |      0
(1 row)

Als ‘Bonus’ siehst Du sogar die Anzahl der Fehler pro Fehlerstelle.

Die Funktion liefert auf alle Fälle unterschiedliche Tabellen, je nachdem, was definert wurde, und benennt auch die Spalten nach den Fehlerquellen.

Andreas

Hallo Andreas,

kanns heute leider nicht testen, trotzdem schon mal vielen Dank für die Hilfe, echt Klasse :slight_smile: Sobald ich dazu kommen, werd ich deine Lösung mal erweitern und schauen, ob das für mich hinhaut.

Grüße,
Bierstuebl