Re: Recursive Parent-Child Function Bottom Up

From: Rob Sargent <robjsargent(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Recursive Parent-Child Function Bottom Up
Date: 2021-07-26 15:43:03
Message-ID: 5fd63f2e-4fc0-fff6-fbd3-ed340894e33c@gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On 7/26/21 9:19 AM, Avi Weinberg wrote:
>
> Hi,
>
> I would like to populate the children_ids column with all the ids of
> the children recursively (+ grandchildren etc.)
>
> If I do it top-bottom I will end up doing extra work since there is no
> need to go all levels down if I can just compute my IMMEDIATE children
> "children_ids" and just concatenate all their lists.
>
> I looked at
> https://stackoverflow.com/questions/41376655/how-can-i-traverse-a-tree-bottom-up-to-calculate-a-weighted-average-of-node-va
> <https://stackoverflow.com/questions/41376655/how-can-i-traverse-a-tree-bottom-up-to-calculate-a-weighted-average-of-node-va>
>
> But was not able to get it to work on my case.
>
> Your assistance is most welcome!
>
> create table tree(id int primary key, parent int, children_ids text);
>
> insert into tree (id, parent) values
>
> (273, 0),
>
> (274,      273),
>
> (275,      273),
>
> (277,      273),
>
> (278,      277),
>
> (280,      275),
>
> (281,      280),
>
> (282,      281),
>
> (283,      282),
>
> (284,      282),
>
> (285,      282),
>
> (286,      282),
>
> (287,      282),
>
> (288,      282),
>
> (289,      282),
>
> (290,      281),
>
> (291,      290),
>
> (292,      290),
>
> (293,      290),
>
> (294,      290),
>
> (295,      290);
>
> IMPORTANT - This email and any attachments is intended for the above
> named addressee(s), and may contain information which is confidential
> or privileged. If you are not the intended recipient, please inform
> the sender immediately and delete this email: you should not copy or
> use this e-mail for any purpose nor disclose its contents to any person.
I'm sorry, what output format are you looking for?  Perhaps an array of
all descendants for each founder? (founder int, descendants int[]).   I
recommend a null for no-known-parent rather than zero.  Or just founder
int, descendant int? With or without sub-trees?

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Rob Sargent 2021-07-26 15:46:43 Re: Recursive Parent-Child Function Bottom Up
Previous Message Marcin Barczynski 2021-07-26 15:41:44 Re: Why does VACUUM FULL pg_class sometimes wait for ShareLock on another transaction after getting AccessExclusiveLock on pg_class?