Re: PL/pgSQL graph enumeration function hangs

Thanks, but the join clause is there, it's just buried in the subqueries.

If there is a problem, it is probably that the loop never ends.

Or it could be that the answer is exponential, and I just have too many
rows in the source table and too deep a graph.

I figured out how to do it in the application with one call to the
database and a simple recursive method in a class, though, so I'm not
going to use a stored function in the DB.

Thanks again for responding.

Chas.

Tom Lane wrote:
> "Charles F. Munat" <chas@munat.com> writes:
>> Using pseudocode from Celko's "SQL for Smarties" book, I wrote the
>> following function that builds a path enumeration table. I hope to
>> trigger this function on the rare occasions that the organizations table
>> is updated. But when I run this function, it hangs.
>
> I think there might be something wrong with this query:
>
>>        INSERT INTO organizations_path_enum
>>            SELECT o1.parent_id, r1.child_id, (o1.depth + 1)
>>            FROM organizations_path_enum o1, relationships r1
>>                -- advance existing paths by one level
>>            WHERE EXISTS (SELECT * FROM organizations_path_enum AS o2
>> WHERE r1.parent_id = o2.child_id)
>>                -- insert only new rows into the table
>>            AND NOT EXISTS (SELECT * FROM organizations_path_enum AS o3
>> WHERE o1.parent_id = o3.parent_id AND r1.child_id = o3.child_id);
>
> I'm not totally clear on what this is supposed to accomplish, but
> it seems like there should be some join clause between o1 and r1.
>
>             regards, tom lane