How to deal with a recursive aggregation in postgresql?

@darrion.kuhn 

To deal with a recursive aggregation in PostgreSQL, you can use a common table expression (CTE) with a recursive query. Here is an example of how you can use a recursive query to perform a recursive aggregation in PostgreSQL:

1. Start by defining a CTE with a recursive query that will generate the desired aggregation. For example, if you want to calculate the sum of a column recursively, you can use a query like this:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

WITH RECURSIVE recursive_agg AS (
    SELECT parent_id, child_id, value
    FROM your_table
    WHERE parent_id IS NULL

    UNION ALL

    SELECT t.parent_id, t.child_id, t.value
    FROM your_table t
    JOIN recursive_agg r ON t.parent_id = r.child_id
)
SELECT parent_id, SUM(value) AS total
FROM recursive_agg
GROUP BY parent_id;

1. In this query, the recursive CTE "recursive_agg" starts by selecting the rows where the parent_id is NULL (i.e., the root of the recursion). It then recursively adds rows where the parent_id of the current row matches the child_id of the previous row until all related rows are aggregated.
2. Finally, the main query groups the results by the parent_id and calculates the total sum of the values for each parent row.

By using a recursive query in a CTE, you can perform recursive aggregations in PostgreSQL efficiently and effectively.