Re: Aggregating over nodes in hierarchical trees - Mailing list pgsql-general

"McGehee, Robert" <Robert.McGehee@geodecapital.com> writes:

> SELECT n.node, sum(students) as students
> FROM tree_tbl t, node_tbl n
> WHERE t.course ~ '.*' || n.node || '.*'
> GROUP BY n.node;

I'd write this as

SELECT n.node, sum(students) AS students
FROM tree_tbl t
JOIN node_tbl n ON t.course ~ ('*.' || n.node || '.*')::lquery
GROUP BY n.node;