Postgresql trigger update whole table after delete

Question

I have a table "measurements" which looks like this (with empty cells in the last col)

ser_nr | meas_ser | meas_value | last_meas
-------+----------+------------+-----------
  A1   |    1     |    12      |  (False)
  A1   |    2     |    5.5     |  (False)
  A1   |    3     |    5.12    |  (False)
  A1   |    4     |    5.01    |  (True)
  B1   |    1     |    2       |  (False)
  B1   |    2     |    2.03    |  (True)

http://sqlfiddle.com/#!15/bcd6b (Link for create table etc.)

I need this to query "select ... where last_meas=True"

I wrote a trigger for "after insert" and this works for the newly inserted rows. But what can I do to update the whole table?

For example: I want to delete the A1 row with meas_value "5.01". There has to be a function which executes for the whole remaining table and updates the last_meas column (in this example the third row should be True now). I can trigger a function using "after delete for each row" but using this method i can only access all deleted rows.

I found a workaround by generating a view using

SELECT * FROM measurements AS m LEFT OUTER JOIN (SELECT m.ser_nr, 
    max(m.meas_ser) AS meas_ser, TRUE AS last_meas FROM measurements AS 
    m GROUP BY m.ser_nr ) AS last_ms USING (ser_nr, meas_ser);

but this only generates "True" and not "False".

Is there a more easy way doing this?

Answer 1

You can use this simpler / cheaper query for your view or CTE or simply a subquery:

SELECT ser_nr, meas_ser, meas_value
     , row_number() OVER (PARTITION BY ser_nr ORDER BY meas_ser DESC) = 1 AS last_meas
FROM   measurements;

SQL Fiddle.

There are no corner case problems with duplicate or NULL values here while you have that PK constraint you added in the fiddle:

ALTER TABLE measurements ADD CONSTRAINT measurements_pk PRIMARY KEY (ser_nr,meas_ser);

The associated index will also help to make it fast if you only need a small selection of ser_no from a big table.

And yes, this dynamic approach has so much less potential for headache (consistency in the face of concurrent write access!) than trying to keep rows in your base table current.