SELECT rows from table one by one, comparing one column values with values in an array

Question

I have an array of timestamps in a PLPGSQL code block: timestamp_array.

For each element of timestamp_array I want to get the element itself (timestamp_element) and some columns from a table (my_table) that also has a timestamp column, but I don't want to get the table timestamp column itself, but the timestamp array element. The condition is that I only want to get one table row for each array element, the nearest match (into the past) between the timestamp elements in the array and the table timestamp column.

What I want to achieve is something like:

FOR EACH timestamp_element IN ARRAY timestamp_array
LOOP
 EXECUTE $EXE$
 SELECT my_table.some_other_columns...
 FROM my_table
 WHERE my_table.timestamp <= 1ドル
 ORDER BY my_table.timestamp DESC
 LIMIT 1
 $EXE$
 USING timestamp_element
 INTO tmp_array;
 EXECUTE
 INSERT INTO temporary_table
 VALUES (1,ドル array_to_string(2,ドル ', '))
 USING timestamp_element, tmp_array;
END LOOP;

I don't know if the previous code is 100% correct, but it's only to try to explain better what I want to achieve. And obviously, the point would be to do the job with only one query, instead that one query for array element.

To put it as a practical case if I have a my_table:

timestamp	col_a	...
2020年02月13日 23:12:07	12	...
2020年03月27日 10:37:01	15	...
2020年06月14日 16:32:44	7	...
2020年06月14日 17:01:57	33	...

With an timestamp_array:

[
 2020年02月15日 12:00:00,
 2020年03月22日 00:00:00,
 2020年06月14日 17:00:00
]

I would want the following temporary_table without using one independent query for each array element:

timestamp	col_a	...
2020年02月15日 12:00:00	12	...
2020年03月22日 00:00:00	12	...
2020年06月14日 17:00:00	7	...

• Gerard H. Pille: What if there are no timestamps in the table that fall between two in your array?
• Héctor: Maybe I have not expressed well my self, but the query should search for the biggest avaliable table timestamp which is smaller or equal than the timestamp array element. The biggest table timestamp that meets table.timestamp <= array.element_timestamp criteria. So it hasn't to fall between. A timestamp element in the array like '2034年01月01日' should return 7. A timestamp of '1980年01月01日' should return null.

Answer 1

unnest() the array and run a LATERAL subquery on your table:

SELECT a.timestamp_element, t.col1, t.col2, ...
FROM unnest(timestamp_array) AS a(timestamp_element)
LEFT JOIN LATERAL (
 SELECT *
 FROM my_table t
 WHERE t.timestamp <= a.timestamp_element
 ORDER BY t.timestamp DESC
 LIMIT 1
 ) t ON true;

Pure SQL. You can nest it in a PL/pgSQL block, of course.
But you certainly don't need dynamic SQL with EXECUTE for this.

Maybe add another ORDER BY expression to break ties and get deterministic results if my_table.timestamp isn't UNIQUE.

If my_table is big, be sure to have an index on (timestamp) to make it fast.

LEFT JOIN .. ON true keeps all timestamp_element in the result, even if no earlier row is found in my_table.

See:

• What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
• Optimize GROUP BY query to retrieve latest row per user

• Gerard H. Pille: Will an index help when searching for a value lower than or equal?
• Erwin Brandstetter: @GerardH.Pille: Yes, in this constellation a plain btree index is perfect.
• Héctor: Thanks, it looks pretty much what I was really looking for. Tomorrow I will test it. But I'm a little bit confused about the dynSQL/execute part. Isn't that the tool to use when the full SQL statement is unknown until runtime, like in this case that the timestamp array is unkown? When dynSQL/execute is really needed then?
• Erwin Brandstetter: @Héctor: Dynamic SQL is really needed when syntax elements or identifiers are dynamic. Your array is a value. You could still use dynamic SQL to force a custom execution plan for every array element. But that only pays for extremely uneven data distributions.
• Héctor: Ah, ok. So, no need to use EXECUTE with 'dynamic' literals. And what if the literal comes directly from user input and it's unsafe? Would be preferable to use user_inp=quote_literal(user_inp) and then use it directly in plain SQL, or to use EXECUTE ... USING user_inp? I have read that EXECUTE...USING was newer and more peformant than quote_literal(), that was a little bit obsolette.