I have a table of user batches. I only want to select until my amount total reaches a certain amount.
id | user_id | balance | batch_id
----|---------|-------- |--------
1 | 1 | 2 | 1
2 | 2 | 15 | 2
3 | 1 | 8 | 3
4 | 1 | 5 | 4
5 | 2 | 7 | 5
6 | 1 | 1 | 6
7 | 2 | 5 | 7
Consider the following query:
SELECT * FROM tb_batch_user WHERE user_id = 1 ORDER BY batch_id asc
The result of query is:
id | user_id | balance | batch_id
----|---------|-------- |--------
1 | 1 | 2 | 1
3 | 1 | 8 | 3
4 | 1 | 5 | 4
6 | 1 | 1 | 6
I want to do a select on the table until the balance total is 6. Then only ids 1, 2 should be returned:
id | user_id | balance | batch_id
----|---------|-------- |--------
1 | 1 | 2 | 1
3 | 1 | 8 | 3
Another example with balance total 1. Then only ids 1 should be returned:
id | user_id | balance | batch_id
----|---------|-------- |--------
1 | 1 | 2 | 1
Example with balance total 11. Only ids 1,3,4 should be returned:
id | user_id | balance | batch_id
----|---------|-------- |--------
1 | 1 | 2 | 1
3 | 1 | 8 | 3
4 | 1 | 5 | 4
So, after that I need to lock those lines with FOR UPDATE ex:
SELECT * FROM tb_batch_user WHERE user_id = 1 ORDER BY batch_id asc FOR UPDATE
I tried with window function, but it doesn't allow lock (FOR UPDATE). Thanks for any help.
I am able to select. . . for update using window functions:
with inparms as (
select 1 as user_id, 6 as target
), rtotal as (
select t.id, i.target,
sum(t.balance) over (partition by t.user_id
order by t.id
rows between unbounded preceding
and 1 preceding) as runbalance
from tb_batch_user t
join inparms i
on i.user_id = t.user_id
)
select t.*
from rtotal r
join tb_batch_user t
on t.id = r.id
where coalesce(r.runbalance, 0) < r.target
for update of t;