leaflet.pub
a tool for shared writing and social publishing
awarm.space
1CREATE OR REPLACE FUNCTION public.get_facts_with_depth(root uuid, max_depth integer)
2 RETURNS TABLE("like" facts)
3 LANGUAGE sql
4AS $function$WITH RECURSIVE all_facts as (
5 -- Base case: start with root level (depth 0)
6 select
7 *,
8 0 as depth
9 from
10 facts
11 where
12 entity = root
13
14 union
15
16 -- Recursive case: join with previous level and increment depth
17 select
18 f.*,
19 f1.depth + 1 as depth
20 from
21 facts f
22 inner join all_facts f1 on (
23 uuid(f1.data ->> 'value') = f.entity
24 )
25 where
26 (f1.data ->> 'type' in ('reference', 'ordered-reference', 'spatial-reference'))
27 and f1.depth < max_depth -- Add depth limit parameter
28 )
29select
30 id, entity, attribute, data, created_at, updated_at, version
31from
32 all_facts;$function$
33;
34
35CREATE OR REPLACE FUNCTION public.get_facts_for_roots(roots uuid[], max_depth integer)
36 RETURNS TABLE(root_id uuid, id uuid, entity uuid, attribute text, data jsonb, created_at timestamp without time zone, updated_at timestamp without time zone, version bigint)
37 LANGUAGE sql
38AS $function$
39 SELECT
40 root_id,
41 f.*
42 FROM unnest(roots) AS root_id
43 CROSS JOIN LATERAL get_facts_with_depth(root_id, max_depth) f;
44$function$
45;