TRIGGER i Zapytanie przestrzenne

Zerknij czy to rozwiązuje twój problem
Założyłem, że polu "geom" w tabeli dom masz jakiś obszar, a w palenisko jakieś punkty
Ponieważ nie wiem czy w tabeli dom kontrolujesz by nie było rekordów z nakładającymi się obszarami to dodałem wszędzie limit 1 by zwracał zawsze tylko jeden rekord (pomiń to jeśli tak nie jest)

UWAGA ST_WITHIN zwraca true jeśli punkt zawiera się dokładnie w obszarze, jeśli punkt jest na krawędzi obszaru to dostaniesz false

create or replace function f_palenisko_tr() RETURNS trigger AS
$body$BEGIN
        if tg_op = 'INSERT' then
           IF NEW.geom IS not NULL THEN
               new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) limit 1);
           END IF;
           RETURN NEW;
        elsif tg_op = 'UPDATE' then
            IF (old.geom is null and NEW.geom IS not NULL) or st_distance (old.geom,new.geom) >0 THEN
               new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) limit 1);
            END IF;
            RETURN NEW;
        END IF;
END;$body$
LANGUAGE plpgsql;

CREATE TRIGGER tr_palenisko BEFORE INSERT OR UPDATE ON palenisko FOR EACH ROW EXECUTE PROCEDURE f_palenisko_tr();

Musisz to dokładniej opisać co ma się pojawić w polu "palenisko.nr_dom" gdy zapytanie select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) znajdzie więcej niż jeden rekord. Z czego część rekordów będzie miała pole "dom.wiek" ustawione na "stary", a część na "młody"

Najlepiej opisz to na jakimś przykładzie

Ostatnio edytowany przez c_michal (2016-11-07 12:36:26)

Czy dobrze cię rozumiem?
jeśli masz 2 rekordy spełniające warunek ST_WITHIN(new.geom,dom.geom) z czego:
- pierwszy rekord ma pole dom.wiek = 'stary'
- drugi rekord ma pole dom.wiek = 'młody'
to trigger ma ma zwracać id dla pierszego rekordu

A jak zmienisz w pierszym rekordzie pole dom.wiek z wartości 'stary' na warosc 'mlody' to bedziesz miał 2 rekordy z wartoscią ' młody' i pewnie chcesz by podstawiać id z rekordu drugiego jako starszego w bazie?

najpierw mała uwaga do twojego posta
Linia "new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) ORDER BY dom.wiek DESC LIMIT 1)"
zawsze ci dopisze id domu nie zależnie czy dom jest "stary" czy "młody", (w pierwszej kolejności wstawi id domu "starego")

Z tego co napisałeś wynika, że jak ktoś w tabeli dom przestawi pole wiek z młody na stary to trigger ma wykonać update na tabeli palenisko i ustawić pole nr_domu na właśnie ten edytowany rekord z tabeli dom.

Aby to zrealizować musisz wykonać nowy trigger działający na tabeli dom po zmianie wartości rekordu (AFTER UPDATE), który wykona
UPDATE palenisko set nr_dom= new.id where  ST_WITHIN(palenisko.geom,new.geom);

Mając powyższy przykład poradzisz sobie z tym bez problemów.

Zerknij na to:
create or replace function f_palenisko_tr() RETURNS trigger AS
$body$BEGIN
         if tg_op = 'INSERT' then
            IF NEW.geom IS not NULL THEN
                new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) order by dom.wiek desc limit 1);
            END IF;
            RETURN NEW;
         elsif tg_op = 'UPDATE' then
                new.nr_dom = (select dom.id FROM dom WHERE ST_WITHIN(new.geom,dom.geom) order by dom.wiek desc limit 1);
             RETURN NEW;
         END IF;
END;$body$
LANGUAGE plpgsql;
CREATE TRIGGER tr_palenisko BEFORE INSERT OR UPDATE ON palenisko FOR EACH ROW EXECUTE PROCEDURE f_palenisko_tr();

create or replace function f_dom_tr() RETURNS trigger AS
$body$ BEGIN
       raise notice '%,%,%',tg_table_name,tg_when,tg_op;
       UPDATE palenisko set nr_dom = null--d.nr_dom
        from (select p.id,d.id as nr_dom,row_number() over (partition by p.id order by d.wiek desc) as lp
                from palenisko p
                left join dom d on ST_WITHIN(p.geom,d.geom)
             ) d
       where d.id=palenisko.id and d.lp=1
         and coalesce(palenisko.nr_dom,0) != coalesce(d.nr_dom,0);
       return new;
END;$body$
LANGUAGE plpgsql;
CREATE TRIGGER tr_dom AFTER INSERT OR UPDATE OR DELETE ON dom FOR EACH STATEMENT EXECUTE PROCEDURE f_dom_tr();

PS
założyłem, że tabele wyglądają tak jak niżej
create table palenisko(id serial primary key ,nr_dom int ,geom geometry);
create table dom(id serial primary key, wiek varchar(10) ,geom geometry);

CREATE INDEX palenisko_dom_ix ON public.palenisko(nr_dom);