i have a challenge whose consist in filter a query not with a value that is not present in a table but a value that is retrieved by a function.

let's consider a table that contains all sales on database

id, description, category, price, col1 , ..... col n

i have function that retrieve me a table of similar sales from one (based on rules and business logic) . This function performs a query again on all records in the sales table and match validation in some fields.

similar_sales (sale_id integer) - > returns a integer[]

now i need to list all similar sales for each one present in sales table.

select s.id, similar_sales (s.id)
from sales s

but the similar_sales can be null and i am interested only return sales which contains at least one.

select id, similar
from (
    select s.id, similar_sales (s.id) as similar
    from sales s 
) q
where #similar > 1 (Pseudocode)
limit x

i can't do the limit in subquery because i don't know what sales have similar or not. I just wanted do a subquery for a set of small rows and not all entire table to get query performance gains (pagination strategy)

CodePudding user response：

you can try this :

select id, similar
from sales s
cross join lateral similar_sales (s.id) as similar
where not isempty(similar)
limit x