Suppose there is a table that reflects different versions of the same object over time:
CREATE TABLE foo (
PRIMARY KEY (id, updated)
INSERT INTO foo (id, updated) VALUES
(1, '2000-01-01'), -- actual from 01 to 09 the number of
(1, '2000-01-10'), -- current with 10 to 19 number
(1, '2000-01-20'), -- actual from 20 to 24 count
(1, '2000-01-25'), -- actual 25 29 number
(1, '2000-01-30') -- current 30 to this day
The task is to find up to date :date_param line with :id_param.
Not a very desirable implementation, it is possible to consider such request:
SELECT * FROM foo WHERE id = :id_param AND date <= :date_param ORDER BY updated DESC LIMIT 1
Theoretically, this query should be performed on the basis of the index, without fetching and pre-sorting of the result. But looks a bit bulky. And if you need to extract the relevant records for several id values, the query becomes even more complex: I can't even imagine how (I think you can do with unnest or union).
The question is, is it possible to do this lookup more elegantly? With the ability to search multiple "actual" rows.