I have a recursive function that calls another function and unions on the results.
Forward_Link_Traversal
if
depth_count > 0 and
exists(
select child_service_item_id
from service_item_links
where parent_service_item_id = ANY(source_ids) and link_type = ANY(link_types) and company_id=cid and action_date <= end_time
)
then
return query
select out_function.child_id as child_service_item_id
from filter_latest_added_action(source_ids) out_function
union select * from forwards_link_traversal(
cid,
ARRAY(select in_function.child_id as child_service_item_id from filter_latest_added_action(source_ids) in_function),
link_types,
depth_count - 1
);
else
return query
select filter_function.child_id as child_service_item_id
from filter_latest_added_action(source_ids) filter_function;
end if;
Where forward_link_traversal is the recursive function and filter_latest_added_action generates the result.
filter_latest_added_action is as follows:
return query with cte as (
select *, first_value (action_key) over (partition by parent_service_item_id, child_service_item_id order by action_date desc) last_action_type
from service_item_links
where (parent_service_item_id = any(source_ids) or child_service_item_id = any(source_ids))
)
select child_service_item_id, parent_service_item_id
from cte
where last_action_type = 'added'
and link_type = ANY(link_types)
and company_id=cid
and action_date <= end_time
and (parent_service_item_id = any(source_ids) or child_service_item_id = any(source_ids))
group by cte.parent_service_item_id, child_service_item_id;
The table (service_item_links) that it selects from is basically a many to many relationship between service_items. These items can be anything, and service_item_links just describes which is the parent and which is the child.
Links between items can be added or removed. The goal of the recursive function is to find all child of the inputted parent_id that have the latest action of 'added'. The schema for this looks like this:
与恶龙缠斗过久,自身亦成为恶龙；凝视深渊过久,深渊将回以凝视…
