Climbing the Parent / Child Relationship Database in Postgres

Question

We have the following sample table (actually taken from another example here in stackoverflow ...)

CREATE TABLE example ( id integer primary key, name char(200), parentid integer, value integer); 

And for a specific child we want to get the upper parent.

I know the connectfunc connectby function, but for getting parent children.

But, I'm interested in another direction, given the child, what is his main parent? What type of request would I try and use?

Friendly advice is welcome.

Answer 1

You can write a PL / PgSQL function to perform a recursion:

 CREATE LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION get_top_parent( child integer ) RETURNS integer as $$ DECLARE parent integer; last_parent integer; BEGIN last_parent := child; SELECT INTO parent parentid FROM example WHERE id = child; IF parent is NOT NULL THEN parent := get_top_parent(parent); ELSE parent := last_parent; END IF; RETURN parent; END $$ LANGUAGE plpgsql; 

This feature can be optimized. It will probably be slow if the depth is very high and the tables are large, so, as Jaegern said, it might be worth caching the hierarchy, perhaps using triggers, etc.

Answer 2

Look at the books by Joe Selco, SQL for Smarties and his book on Trees and Hierarchies . It has a section or two in SQL for Smarties on trees and hierarchies, or if you want to really enter it, then you can get another book. SQL for Smarties will also affect many other database designs and queries. Some really good things are there. It offers alternative ways to model trees that can work much better than the adjacency list model you use.

In one of its models, the question of “who is the main parent” becomes very trivial.

Answer 3

You can use the "ltree" contrib module.

Answer 4

With RECURSIVE from PostgreSQL 8.4 onwards?

Answer 5

In my experience, SQL is not very good at this query (recursion). I would suggest creating an additional table with id and top parent. When you add more children, you simply look at its parent top parent id and insert the corresponding row into the extra table.

You can also save the main parent id in your source table.