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!