PL/pgSQL über zwei Tabellen mit Auswertung

Anwendungen & Erweiterungen Trigger und Funktionen
1

Hi Leute,

vielleicht kann mir jemand helfen, da ich irgendwie kein vergleichbares Problem bzw. Lösung gefunden habe:

2 Ausgangstabellen:

  • hardware (Spalten: hw_id, hw_text, …)
  • hardwarelog (Spalten: hwl_id, hw_id, hw_status, …)

Ich bräuchte nun eine PL/pgSQL-Funktionen die über eine Schleife die Tabelle “hardware” durchläuft und für jede Zeile die entsprechenden “hardwarelog”-Zeilen ausliest und die Spalte hw_status prüft und auswertet (siehe Quellcodeauszug). Also besitzt jede Hardware mehrere Hardwarelogs die jeweils einen “hw_status” besitzen.

Beispiel:
Hardware (ID xx) besitzt 6 Hardwarelogs davon haben 3 den hw_status 1 und 2 den hw_status 3.

Zur Veranschauung habe ich mal etwas Code gepostet
_FOR hw_row IN SELECT * FROM hardware ORDER BY hw_id LOOP
lager := 0;
afa := 0;
defekt := 0;
nicht_auffindbar := 0;

– Iteration durch Tabelle ‘hardwarelog’

FOR hwl_row IN SELECT * FROM hardwarelog WHERE hardwarelog.hw_id = hw_row.hw_id ORDER BY hw_id LOOP
IF hwl_row.hw_status = 0 THEN
lager := lager + 1;
ELSIF hwl_row.hw_status = 1 THEN
afa := afa + 1;
ELSIF hwl_row.hw_status = 4 THEN
defekt := defekt + 1;
ELSIF hwl_row.hw_status = 5 THEN
nicht_auffindbar := nicht_auffindbar + 1;
END IF;
END LOOP;
END LOOP;
_
Mein Problem ist jetzt, wie bekomme ich das Ganze (also jede Zeile) in ein View bzw. Tabelle? Oder soll ich es anderes angehen? - RETURN NEXT?

Die Ausgabe sollte aber folgende Struktur für jede Zeile aufweisen:
hw_id || hw_text || lager || afa || defekt || nicht_auffindbar

In den Variablen lager, afa, defekt und nicht_auffindbar steht also die Anzahl der vorkommenen hardwarelogs für die hardware.

Ich sage mal danke… :rolleyes:

2

Wie findet Ihr diese Lösung?

-- Function: hardware_search()

-- DROP FUNCTION hardware_search();

CREATE OR REPLACE FUNCTION hardware_search(OUT hw_id int, OUT hw_text text, OUT lager int, OUT afa int, OUT defekt int, OUT nicht_auffindbar int)
  RETURNS SETOF record AS 
$BODY$
DECLARE 
   hw_row RECORD;
   hwl_row RECORD;
BEGIN
   -- 
   -- Iteration durch Tabelle 'hardware'
   --
   FOR hw_row IN SELECT * FROM hardware ORDER BY hw_id LOOP
      lager := 0;
      afa := 0;
      defekt := 0;
      nicht_auffindbar := 0;

      hw_id := hw_row.hw_id;
      hw_text := hw_row.hw_text;
      -- 
      -- Iteration durch Tabelle 'hardwarelog'
      --
      FOR hwl_row IN SELECT hw_status FROM hardwarelog WHERE hardwarelog.hw_id = hw_row.hw_id LOOP
         IF hwl_row.hw_status = 0 THEN
            lager := lager + 1;
         ELSIF hwl_row.hw_status = 1 THEN
            afa := afa + 1;
         ELSIF hwl_row.hw_status = 4 THEN
            defekt := defekt + 1;
         ELSIF hwl_row.hw_status = 5 THEN
            nicht_auffindbar := nicht_auffindbar + 1;
         END IF;   
      END LOOP;     
      RETURN NEXT;
   END LOOP;
   RETURN;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION hardware_search() OWNER TO xnet;
3

Du denkst zu kompliziert, all das ist mit plain SQL schon lösbar. Faul wie ich bin hab ich das mal kurz illustriert:

test=*# select * from hardware;
 hw_id |   info
-------+----------
     1 | rechner1
     2 | rechner2
(2 rows)

Time: 0.941 ms
test=*# select * from hardwarelog;
 hwl_id | hd_id | status
--------+-------+--------
      1 |     1 |      0
      2 |     1 |      1
      3 |     1 |      2
      4 |     1 |      3
      5 |     2 |      1
(5 rows)

Time: 0.962 ms
test=*# select h.hw_id, h.info, l.hwl_id, l.status, sum(case when l.status=0 then 1 else 0 end) as lager, sum(case when l.status=1 then 1 else 0 end) as afa  from hardware h left join hardwarelog l on (h.hw_id=l.hd_id) group by h.hw_id, h.info, l.hwl_id, l.status order by hw_id, hwl_id;
 hw_id |   info   | hwl_id | status | lager | afa
-------+----------+--------+--------+-------+-----
     1 | rechner1 |      1 |      0 |     1 |   0
     1 | rechner1 |      2 |      1 |     0 |   1
     1 | rechner1 |      3 |      2 |     0 |   0
     1 | rechner1 |      4 |      3 |     0 |   0
     2 | rechner2 |      5 |      1 |     0 |   1
(5 rows)

Der ‘Trick’ ist hier, mal wieder, das sum(case when … ) und die damit erzeugten Spalten.


Andreas