[PostgreSQL] - trigger/funkcja dla wybranych wierszy

Hej,

Potrzebuję utworzyć parę trigger/funkcja, która spowoduje to, że w momencie zmiany jednej wartości jednej kolumny, w innej zapisze się wynik pewnego działania. Na ten moment dotarłem do miejsca, gdzie jeśli zmienię jedną wartość tej kolumny, to cała inna kolumna zostaje zmieniona.

Trigger:

CREATE TRIGGER update_day
AFTER UPDATE
ON test
FOR EACH ROW
EXECUTE update_days();

Funkcja:

CREATE OR REPLACE FUNCTION update_days()
RETURNS trigger AS
$BODY$
BEGIN
IF NEW.start_date <> OLD.start_date THEN
UPDATE test SET days=end_date::date - new.start_date::date;
ELSEIF NEW.end_date <> OLD.end_date THEN
UPDATE test SET days=new.end_date::date - start_date::date;
END IF;

RETURN NEW;
END
$BODY$

LANGUAGE plpgsql

I teraz jak zrobię:

UPDATE test SET start_date='2019-01-01' WHERE id=1;

To w tym momencie kolumna days zostaje w całości nadpisana nowymi wyliczonymi wartościami. A docelowo chodzi o to, aby tylko jeden rekord został zmodyfikowany (z id=1).

Przecież w definicji robisz updatea na całej tabeli, to co się dziwisz :)

Pewni chodzi Ci o zmodyfikowanie bieżącej wartości wiersza, tj. zamiast update:

new.days=new.end_date::date - start_date::date;

yarel napisał(a):

Przecież w definicji robisz updatea na całej tabeli, to co się dziwisz :)

Pewni chodzi Ci o zmodyfikowanie bieżącej wartości wiersza, tj. zamiast update:

new.days=new.end_date::date - start_date::date;

Czyli zamiast:

UPDATE test SET days=end_date::date - new.start_date::date;
UPDATE test SET days=new.end_date::date - start_date::date;

Powinno być:

new.days=end_date::date - new.start_date::date;
new.days=new.end_date::date - start_date::date;

Zgadza się?

Tylko jęsli modyfikujesz NEW, to musisz mieć triggera BEFORE, w AFTER, to już "after the birds" :)

Marcin.Miga napisał(a):

Tylko jęsli modyfikujesz NEW, to musisz mieć triggera BEFORE, w AFTER, to już "after the birds" :)

Zmodyfikowałem całość i w efekcie dostałem error:

ERROR: column "end_date" does not exist
LINE 1: SELECT end_date::date - NEW.start_date::date

QUERY: SELECT end_date::date - NEW.start_date::date
CONTEXT: PL/pgSQL function update_days() line 4 at assignment
********** Błąd **********

ERROR: column "end_date" does not exist
Stan SQL: 42703
Kontekst: PL/pgSQL function update_days() line 4 at assignment

Taki off-top pierwszy raz dowiedziałem się, że postgres czegoś nie ma, względem mssql, mam na myśli Computed Columns, co by tu idealnie pasowało. Ma być w wersji 12, chociaż raz nie mam powodów do zazdrości ;)

@Panczo: ale można to zrobić bez widoków....
Ech, wprowadziłem w błąd. CREATE RULE niejawnie tworzy widok...

Nie wiem o jakich widokach mówisz, nie miałem tez na myśli CREATE RULE, chodzi mi o kolumnę wyliczeniową, czyli bez triggera, rule itd., zakładamy tabele:

create table dbo.tabela (
 ID int IDENTITY (1,1) NOT NULL  
  , start_date datetime
  , end_date datetime
  , days AS datediff(d,start_date,end_date) PERSISTED,
 
    CONSTRAINT PK_tabela_ID 
        PRIMARY KEY CLUSTERED (ID)
)

wkładamy dane:

insert into tabela (start_date,end_date) values ('2018-02-02','2018-06-20'),('2018-02-02',null)

i w rezultacie mamy:

ID,start_date,end_date,days
1,2018-02-02 00:00:00.000,2018-06-20 00:00:00.000,138
2,2018-02-02 00:00:00.000,NULL,NULL

@Panczo: faktycznie, coś takiego idealnie pasowałoby do tego, co chcę osiągnąć. Aż dziwne, że w innych silnikach coś takiego jest, a tutaj nie...Niemniej jednak, to nadal nie działa u mnie, więc szukam dalej pomysłów...

Bez żadnych IFow.

New.days = new.end_date-new.start_date;

Marcin.Miga napisał(a):

Bez żadnych IFow.

New.days = new.end_date-new.start_date;

Pięknie dziękuję! Chwilę wcześniej znalazłem dokładnie takie samo rozwiązanie...działa elegancko, zgodnie z założeniami! Zastanawiam się tylko, dlaczego IFy nie chciały tego ogarnąć, zrobiłem jakiś błąd w logice?

@Lucas83: zamiast modyfikować wiersz, który wstawiałeś ty robiłeś update:

UPDATE test SET days=end_date::date - new.start_date::date;

Który jako, że jest unbounded zmieniał wartość we wszystkich wierszach w tabeli.

Lucas83 napisał(a):

Marcin.Miga napisał(a):

Bez żadnych IFow.

New.days = new.end_date-new.start_date;

Pięknie dziękuję! Chwilę wcześniej znalazłem dokładnie takie samo rozwiązanie...działa elegancko, zgodnie z założeniami! Zastanawiam się tylko, dlaczego IFy nie chciały tego ogarnąć, zrobiłem jakiś błąd w logice?

To nie IF były przyczyną błędów, a brak NEW lub OLD. Samo days jest nieznane w triggerze (bo nie pokazujesz z której tabeli/obiektu - nie masz tam np. SELECT)