Updating only last updated row using a function in PSQL

Question

Im working on a function that will calculate working hours from arrival and departure times.

My function looks like this:

CREATE FUNCTION Sati_rada() 
  RETURNS TRIGGER 
as $Update$ 
BEGIN
    update radni_sati 
        set sati_rada=(select (current_date + (odlazak-dolazak)) 
                       from radni_sati 
                       where datum=current_date 
                       and dolazak IS NOT NULL 
                       LIMIT 1)
  WHERE datum=current_date 
    AND odlazak IS NOT NULL 
    AND DOLAZAK IS NOT NULL; 
  RETURN NULL; 
END $Update$ 
language plpgsql;

My trigger looks like this:

CREATE TRIGGER Sati_rada_trigger
AFTER UPDATE OF odlazak ON radni_sati 
FOR EACH ROW EXECUTE PROCEDURE Sati_rada();

The problem starts here when I want to use function on a last row that I manually updated.

For example (I translated names of columns from my native language):

SELECT id_radni_sati as id,ime as name,dolazak as arrival, 
odlazak as departure, sati_rada as "hours of work" FROM radni_sati
WHERE datum='31/08/2018';

  id   |  name   |       arrival       |      departure      |    hours of work
-------+---------+---------------------+---------------------+---------------------
 22282 | Nevenko | 2018-08-31 07:00:00 |                     |
 22284 | Marko   | 2018-08-31 11:41:52 | 2018-08-31 12:01:45 | 2018-08-31 00:19:53

If i manully try to change departure of one row:

UPDATE radni_sati SET odlazak='31/08/2018 07:05:00' where id_radni_sati=22282;

This happends:

postgres=# select id_radni_sati as id,ime as name,dolazak as arrival,
odlazak as departure, sati_rada as "hours of work" from radni_sati 
where datum='31/08/2018';
  id   |  name   |       arrival       |      departure      |    hours of work
-------+---------+---------------------+---------------------+---------------------
 22282 | Nevenko | 2018-08-31 07:00:00 | 2018-08-31 07:05:00 | 2018-08-31 00:05:00
 22284 | Marko   | 2018-08-31 11:41:52 | 2018-08-31 12:01:45 | 2018-08-31 00:05:00
(2 rows)

My question is: How to calculate work hours on single row in database (the one that needs an update) every time column odlazak is updated?

Answer 1

The issue is with your update statement in your trigger function. It should be using the updated row's id to make sure it only updates the correct record.

Here's the setup:

CREATE TABLE t (id INTEGER, arrival TIMESTAMP, departure TIMESTAMP, hours_of_work TIME);

CREATE OR REPLACE FUNCTION public.trig()
        RETURNS TRIGGER AS $$
BEGIN
        UPDATE t
        SET hours_of_work = NEW.departure - NEW.arrival
        WHERE id = NEW.id;

        RETURN NULL;
END
        $$ LANGUAGE plpgsql;


CREATE TRIGGER t_trig
AFTER UPDATE OF departure ON public.t 
FOR EACH ROW EXECUTE PROCEDURE public.trig();

INSERT INTO t VALUES (1, NOW(), NOW() + INTERVAL '1 hour', (NOW() + INTERVAL '1 hour') - NOW());
INSERT INTO t VALUES (2, NOW(), NOW() + INTERVAL '1 hour');

Table looks like this:

Now an update:

UPDATE t SET departure = NOW() + INTERVAL '2 hours' WHERE id = 2;

And the table is now:

---

So in your case you should change

update radni_sati 
    set sati_rada=(select (current_date + (odlazak-dolazak)) 
                   from radni_sati 
                   where datum=current_date 
                   and dolazak IS NOT NULL 
                   LIMIT 1)   WHERE datum=current_date 
AND odlazak IS NOT NULL 
AND DOLAZAK IS NOT NULL;

to

UPDATE radni_sati
SET sati_rada = current_date + (NEW.odlazak - NEW.dolazak)
WHERE id_radni_sati = NEW.id_radni_sati;