Untitled

create function bql_get_entity_with_children(INOUT refcursor, p_e_id integer DEFAULT NULL::integer) returns refcursor
    language plpgsql
as
$$
BEGIN
    OPEN $1 FOR
        WITH RECURSIVE
            decesdants AS (SELECT entity_id         AS e_id,
                                  array [entity_id] as all_parent_ids
                           FROM bql_entity_relationship
                           WHERE ancestor_id = p_e_id
                             and type = 'INCLUDE'

                           UNION ALL

                           SELECT r.entity_id as e_id,
                                  d.all_parent_ids || r.entity_id
                           FROM bql_entity_relationship r
                                    JOIN decesdants d ON r.ancestor_id = d.e_id
                               and r.type = 'INCLUDE'
                               and r.entity_id <> ALL (d.all_parent_ids)),
            all_entities AS (SELECT p_e_id AS e_id
                             UNION
                             SELECT e_id
                             FROM decesdants),
            ordered_data AS (SELECT d.e_id,
                                    d.key,
                                    d.value,
                                    d.start_date,
                                    d.end_date,
                                    COALESCE(d.end_date, LEAD(d.start_date)
                                                         OVER (PARTITION BY d.e_id, d.key ORDER BY d.start_date)) AS effective_end_date
                             FROM bql_entity_data d
                                      JOIN all_entities e ON d.e_id = e.e_id),
            time_points AS (SELECT DISTINCT e_id, start_date AS time_point
                            FROM ordered_data),
            filled_data AS (SELECT t.e_id,
                                   t.time_point,
                                   o.key,
                                   o.value
                            FROM time_points t
                                     LEFT JOIN ordered_data o
                                               ON t.e_id = o.e_id
                                                   AND t.time_point >= o.start_date
                                                   AND
                                                  (o.effective_end_date IS NULL OR t.time_point <= o.effective_end_date)),
            entity_info AS (SELECT e.id,
                                   e.entity_type_ma,
                                   e.layer_id AS layerId,
                                   e.cur_geometry_wgs84,
                                   e.start_date,
                                   e.end_date
                            FROM bql_entity e
                                     JOIN all_entities a ON e.id = a.e_id)

        SELECT e.id,
               e.entity_type_ma                      as "entityTypeMa",
               e.layerId,
               e.cur_geometry_wgs84                  as "curGeometryWgs84",
               e.start_date                          as "startDate",
               e.end_date                            as "endDate",
               jsonb_agg(jsonb_build_object('timePoint', f.time_point,
                                            'attributes', COALESCE(f.attributes, '{}'))
                         ORDER BY f.time_point DESC) AS "dataHistory"
        FROM entity_info e
                 LEFT JOIN (SELECT e_id,
                                   time_point,
                                   (
                                               jsonb_object_agg(key, COALESCE(value, ''))
                                               FILTER (WHERE key <> 'doanhNghiepThueLai')
                                       )
                                       ||
                                   (
                                       CASE
                                           WHEN COUNT(*) FILTER (WHERE key = 'doanhNghiepThueLai') > 0 THEN
                                               jsonb_build_object(
                                                       'doanhNghiepThueLai',
                                                       jsonb_agg(COALESCE(value, ''))
                                                       FILTER (WHERE key = 'doanhNghiepThueLai')
                                               )
                                           ELSE '{}'::jsonb
                                           END
                                       ) AS attributes
                            FROM filled_data
                            GROUP BY e_id, time_point) f ON e.id = f.e_id
        GROUP BY e.id, e.entity_type_ma, e.layerId, e.cur_geometry_wgs84, e.start_date, e.end_date;
--         ORDER BY e.id DESC;
END;
$$;

alter function bql_get_entity_with_children(inout refcursor, integer) owner to "cholimex-vinh-loc";