PostgreSQL: loop until condition is met

Question

I am trying to write a query that loops through a database, starting from a given value, until the condition is true. For example, suppose I have the following entries in the TABLE example:

id, parent, cond 1, , True 2, 1 , False 3, 1 , False 4, 2 , False ... ... ... 

I want a query that takes 4 as input (for example) and returns the values ​​2 and 1. The process is that the query matches the identifier, and if cond == False, it will look at the parent (id = 2). Since cond = False in the second line, the "parent" identifier (1) will be selected. Looking at the first line now, since cond = True, LOOP ends and returns 1 and 2.

I know the request

 SELECT parent FROM example WHERE id = 4; 

will create parent id 2.

So my futile attempt to create LOOP:

 WHILE (SELECT cond FROM example) = False LOOP SELECT parent FROM example WHERE id = 4 END LOOP; 

Firstly, this leads to an error ("syntax error in or near", and "). Secondly, I do not know how to update the" id "after each iteration.

In a programming language such as Python, I can use a variable initialized to 4 and then update it with each iteration ... not sure how to make an equivalent in Postgres.

Let me know if you have any questions or require more information. Thanks!

Answer 1

Your thinking is wrong for SQL. Do not think about loops, conditions, and variables; instead, think about how to describe the data you need. The hard part is that you want the query to reference its own results and that recursive CTEs are designed to:

The optional RECURSIVE modifier changes WITH from simple syntactic convenience to a function that implements things in the standard SQL module that are otherwise not possible. Using RECURSIVE , a WITH query can reference its own output.

You are looking for something like this:

 with recursive path as ( select id, parent from T where id = 4 union all select t.id, t.parent from T t join path p on t.id = p.parent ) select id, parent from path 

This will give you the following:

  id | parent ----+-------- 4 | 2 2 | 1 1 | 

and then you can put this back in a path that will be more linked-listy (or something appropriate in your client language) outside the database. Of course, you do not need to include parent , but including this will help you fix the "pointers".