PostgreSQL trigger-rekursywnie Opcje

[splatch]

Witam serdecznie. Mam tabelkę, którą można uprościć do 3 pól.

[SQL] pobierz, plaintext 
CREATE TABLE project
(
  project_id serial NOT NULL,
  project_title varchar(25),
  parent_id int8,
  project_hours int8 DEFAULT 0,
  project_child_hours int8 DEFAULT 0
)  WITHOUT OIDS;
[SQL] pobierz, plaintext 

Problem polega na tym by automatycznie zmieniać wartość pola project_child_hours.
Kolumna project_hours zawiera szacowany czas wykonania zadania/projektu. Jeśli projekt ma dzieci (w tym przypadku węzeł Implementacja) to wartość wyświetlana przy edycji oraz podglądzie tego zadania będzie się równać sumie czasu potrzebnego na wykonanie podzadań - Warstwy prezentacji oraz Logiki biznesowej.

Przykład drzewa:

Kod

Projekt głowny                            [project_hours: 16, project_child_hours: 16]
`- Opracowanie struktory bazy danych      [project_hours: 4,  project_child_hours: 0]
`- Implementacja                          [project_hours: 12, project_child_hours: 12]
   `- Warstwa prezentacji                  [project_hours: 6,  project_child_hours: 0]
   `- Logika biznesowa                     [project_hours: 6,  project_child_hours: 0]

Stworzyłem procedurę która działa tak jak powinna - robi update elementu nadrzędnego dodając lub odejmując czas do kolumny project_child_hours. Problem w tym, że nie powoduje ona rekurencji (nie jest już wywoływana dla elementu nadrzędnego). Przykład: zmieniam czas w zadaniu Logika biznesowa na 8. Procedura robi update zadania Implementacja i dodaje do kolumny project_child_hours 2. Na tym etapie wszystko się zatrzymuje.
Chciałbym wykonać procedurę w taki sposób by poszła ona wyżej i zmieniła wartość project_child_hours dla Projektu głownego na 18 (dodała niżej dodane godziny).
W końcu postanowiłem napisać regułę, lecz nie wiem czemu zapętła się i w końcu postgres ją kiluje.

Proszę o podpowiedzi jak wykonać procedurę tak, by zadziałała w górę drzewa aż do elementu nadrzędnego.

Procedura wyzwalana (wyraźniejszy kod http://phpfi.com/91942):

Kod

CREATE OR REPLACE FUNCTION UPDATE_PROJECT_CHILD_HOURS() RETURNS trigger AS $PROJECT_CHILD_HOURS$
DECLARE
    hours     int8 := 0;
    parent_id int8 := 0;
BEGIN
    IF (TG_OP = 'INSERT') THEN -- isnert - dodajemy
  hours     = NEW.project_hours;
  parent_id = NEW.parent_id;
    ELSIF (TG_OP = 'UPDATE') THEN -- update, trzeba rozpoznac czy godziny sie zwiekszyly czy zmniejszyly
  IF (NEW.project_hours > OLD.project_hours) THEN
     hours = NEW.project_hours - OLD.project_hours;
  ELSIF (NEW.project_hours < OLD.project_hours) THEN
     hours = NEW.project_hours - OLD.project_hours;
     --RAISE EXCEPTION 'Liczba godzin %', hours;
  END IF;
  parent_id = NEW.parent_id;
    ELSIF (TG_OP = 'DELETE') THEN -- usuwamy projekt - zatem odejmujemy od parenta jego godziny
  hours     = -OLD.project_chours;
  parent_id = OLD.parent_id;
    END IF;

    IF( parent_id > 0 AND hours <> 0 ) THEN -- jesli zmieniaja sie godziny - robimy update
  UPDATE project SET project_child_hours = project_child_hours + hours WHERE project_id = parent_id;
    END IF;

    IF(TG_OP <> 'DELETE') THEN -- dodawanie/usuwanie - jest NEW
  RETURN NEW;
    ELSE -- usuwanie - nie ma NEW
  RETURN OLD;
    END IF;
END;
$PROJECT_CHILD_HOURS$ Language plpgsql;

Reguła (wyraźniejszy kod http://phpfi.com/91943):

Kod

CREATE OR REPLACE RULE UPDATE_PROJECT_CHILD_HOURS_FOR_ROW AS
    ON UPDATE TO project WHERE (NEW.parent_id > 0 AND NEW.project_hours <> OLD.project_hours) DO
  UPDATE project SET project_child_hours = project_child_hours+(NEW.project_hours - OLD.project_hours)
  WHERE parent_id > 0 AND project_id = NEW.parent_id;

Rozwiązanie problemu
W końcu udało mi się rozwiązać problem - wystarczyło wyjść na dwór zaczerpnąć świerzego powietrza. (IMG:http://forum.php.pl/style_emoticons/default/winksmiley.jpg)
Oto dwie funkcje, które rozwiązują powstałe zagadnienie:

Kod

CREATE OR REPLACE FUNCTION UPDATE_HOURS_ON_TREE(int8,int8) RETURNS INT2 AS $$
DECLARE
    myvar int8;
BEGIN
    PERFORM parent_id FROM project WHERE project_id = $1;
    IF FOUND THEN
  SELECT INTO myvar parent_id FROM project WHERE project_id = $1;
    ELSE
  RETURN 0;
    END IF;

    UPDATE project SET project_child_hours = project_child_hours + $2 WHERE project_id = $1;
    IF( myvar > 0 ) THEN
  PERFORM project_id FROM project WHERE project_id = myvar;
  IF FOUND THEN
     RAISE NOTICE 'EXECUTE PERFORM';
     PERFORM UPDATE_HOURS_ON_TREE(myvar,$2);
  END IF;
    END IF;
    RETURN 1;
END;
$$ Language plpgsql;

CREATE OR REPLACE FUNCTION update_project_child_hours()
  RETURNS "trigger" AS
$BODY$
DECLARE
    hours     int8 := 0;
    parent_id int8 := 0;
    parent___ int8 := 0;
BEGIN
    IF (TG_OP = 'INSERT') THEN -- isnert - dodajemy
  hours     = NEW.project_hours;
  parent_id = NEW.parent_id;
    ELSIF (TG_OP = 'UPDATE') THEN -- update, trzeba rozpoznac czy godziny sie zwiekszyly czy zmniejszyly
  hours = NEW.project_hours - OLD.project_hours;
  parent_id = NEW.parent_id;
    ELSIF (TG_OP = 'DELETE') THEN -- usuwamy projekt - zatem odejmujemy od parenta jego godziny
  hours     = -OLD.project_hours;
  parent_id = OLD.parent_id;
    END IF;

    IF( parent_id > 0 AND hours <> 0 ) THEN -- jesli zmieniaja sie godziny - robimy update
  PERFORM UPDATE_HOURS_ON_TREE(parent_id,hours);
    END IF;

    IF(TG_OP <> 'DELETE') THEN -- dodawanie/usuwanie - jest NEW
  RETURN NEW;
    ELSE -- usuwanie - nie ma NEW
  RETURN OLD;
    END IF;
END;
$BODY$
LANGUAGE 'plpgsql';

Wiem, że rozwiązanie nie jest najładniejsze (dublujące się zapytania), ale działa. :]

Ten post edytował splatch 15.12.2005, 16:52:11