使用PostgreSQL 16.2，我有两个表:
task_pool张桌子
create table task_pool (
task_id serial primary key
, order_id integer
, user_id integer
, expire_time timestamp
, status integer default 0
);
样本数据:
| task_id | order_id | user_id | expire_time | status |
|---|---|---|---|---|
| 50 | 80 | 1 | 2024-03-00 11:52:44 | -1(expired) |
| 51 | 81 | 1 | 2024-03-00 16:00:44 | -1 (expired) |
| 52 | 80 | 2 | 2024-04-19 12:31:22 | 0 (active) |
| 53 | 81 | 4 | 2024-03-00 16:52:44 | -1(expired) |
| 54 | 81 | 6 | 2024-03-00 16:51:44 | -1(expired) |
reactivate_task表this table is actually a temporary table using WITH AS in a longer query, but for simplicity here is code to create the table:
create table reactivate_task(
, order_id integer
, successfully_added
, total_amount_requested
, amount_to_reactivate integer
);
样本数据:
| order_id | successfully_added | total_amount_requested | amount_to_reactivate |
|---|---|---|---|
| 80 | 0 | 3 | 3 |
| 81 | 0 | 3 | 2 |
我try 做的是，对于reactivate_task中的每一行，更新x个最旧的过期任务，其中order_id是该行的amount_to_reactivate
因此，在上面的示例表中，查看reactivate_task表，3个最旧的、order_id为80的过期任务应该被"重新激活".另外，order_id为81的2个最旧的过期_任务应该被"重新激活".
表task_pool列出了预期结果:
| task_id | order_id | user_id | expire_time | status |
|---|---|---|---|---|
| 50 | 80 | 1 | 2024-03-21 12:00:00 | 0(active) --status set to 0 and expire_time to current_timestamp |
| 51 | 81 | 1 | 2024-03-21 12:00:00 | 0(active) ----status set to 0 and expire_time to current_timestamp |
| 52 | 80 | 2 | 2024-04-19 12:31:22 | 0 (active) --NOT updated (even though amount_to_reactive was 3 for order_id 80, there was only ONE expired task (task_id 50) that was expired. |
| 53 | 81 | 4 | 2024-03-19 16:52:44 | -1(expired) --NOT updated (amount_to_reactive was only 2 for order 81, so only the 2 rows with oldest expire_time were updated (task_id 50 & 54) |
| 54 | 81 | 6 | 2024-03-21 12:00:00 | 0 (active) ----status set to 0 and expire_time to current_timestamp |
这是一个查询一行的示例:
UPDATE task_pool
SET status=0,
expire_time = current_timestamp + (2 * interval '1 day')
WHERE task_id IN (
SELECT task_id
FROM task_pool
WHERE order_id = *--order_id from CURRENT ROW OF REACTIVATE_TASK--*
AND status=-1
ORDER BY expire_time
LIMIT *--amount_to_reactivate from current row of reactivate_task--*
但是使用这个查询，我必须遍历Postgr以外的reactivate_task行(我使用node和node—postgr，在results.rows[]上使用for循环)
在Postgres中，有没有一种方法可以在一个查询中做到这一点，而不是迭代每一行？