PostgreSQL: Loop Until a Condition is True

Question

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

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

I want a query which takes as input (for instance) 4, and will return the values of 2 and 1. The process being that the query matches the id, and if cond==False, will look at the parent (id = 2). Since cond = False in the second row, the "parent" id will be selected (1). Looking at the first row now, since cond=True, the LOOP ends and returns 1 and 2.

I know that the query

SELECT parent FROM example WHERE id = 4;

will produce the parent id 2.

So my futile attempt at creating a LOOP:

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

First, this produces an error ("syntax error at or near 'while'"). Second, I don't know how to update the "id" after each iteration.

In a programming language like Python, I might use a variable initialized to 4 and then update it with each iteration...not sure how to do the equivalent in Postgres.

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

mu is too short · Accepted Answer · 2013-10-04 18:23:00Z

13

Your thinking is wrong for SQL. Don't think in terms of loops and conditions and variables; instead, think about how to describe the data you want. The tricky part is that you want the query to refer to its own results and that's what recursive CTEs are for:

The optional RECURSIVE modifier changes WITH from a mere syntactic convenience into a feature that accomplishes things not otherwise possible in standard SQL. Using RECURSIVE, a WITH query can refer to its own output.

You're 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

That will give you this:

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

and then you can put that back together in a path that would be more linked-listy (or whatever is appropriate in your client language) outside the database. You don't have to include parent of course but including it will help you fix up the "pointers".

edited Oct 4, 2013 at 18:23

answered Aug 7, 2012 at 7:07

mu is too short

436k71 gold badges863 silver badges822 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David C Over a year ago

Thanks. I wasn't quite sure how to approach the problem. Much appreciated.

Collectives™ on Stack Overflow

PostgreSQL: Loop Until a Condition is True

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related