Is it possible to have non-scalar correlated subqueries in the select? Similar to how generate_series can be used in select?
By non-scalar I mean returning more than 1 row (not really concerned with columns at the moment):
select
o1.user_id,
o1.order_id,
o1.order_date,
(select
o2.order_id
from orders o2
where o2.user_id = o1.user_id
and o2.order_date < o1.order_date
order by o2.order_date desc
limit 2) as last_2_orders
from orders o1
I was told that anything that can be expressed as a lateral join can be expressed as a correlated subquery. So I'm trying to find a correlated subquery that can express the following:
select
o1.user_id,
o1.order_id,
o1.order_date,
o3.last_2_orders
from orders o1
left join lateral (
select
o2.order_id as last_2_orders
from orders o2
where o2.user_id = o1.user_id
and o2.order_date < o1.order_date
order by o2.order_date desc
limit 2
) as o3 on true
EDIT: This kind of works but it doesn't satisfy the left outer requirement:
select
o1.user_id,
o1.order_id,
o1.order_date,
unnest(
array(
select o2.order_id
from orders o2
where o2.user_id = o1.user_id
and o2.order_date < o1.order_date
order by o2.order_date desc
limit 2
)
) as last_2_orders
from orders o1