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 | ... |
'2034-01-01'
should return7
. A timestamp of'1980-01-01'
should returnnull
.