PostgreSQL:循环直到条件为真 [英] PostgreSQL: Loop Until a Condition is True

问题描述

我正在尝试编写一个查询，该查询以指定的值从数据库开始循环"，直到条件为真为止.例如，假设我在TABLE示例中有以下条目:

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
... ... ...

我想要一个以4为输入的查询，例如，将返回2和1的值.该过程是查询与id匹配，如果cond == False，将查看父对象( id = 2).由于第二行中的cond = False，因此将选择父代" ID(1).现在看第一行，因为cond = True，所以LOOP结束并返回1和2.

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.

我知道该查询

SELECT parent FROM example WHERE id = 4;

将生成父ID 2.

所以我徒劳地尝试创建一个LOOP:

So my futile attempt at creating a LOOP:

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

首先，这会产生一个错误('while'或附近的语法错误").其次，我不知道每次迭代后如何更新"id".

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

在像Python这样的编程语言中，我可能会使用一个初始化为4的变量，然后在每次迭代时对其进行更新...不确定如何在Postgres中进行等效操作.

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!

推荐答案

您的想法对SQL是错误的.不要以循环，条件和变量来思考；相反，请考虑如何描述所需的数据.棘手的部分是您希望查询引用其自身的结果，这就是递归CTE 用于:

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:

可选的RECURSIVE修饰符将WITH从单纯的语法方便性更改为一项功能，该功能可以完成标准SQL所无法实现的功能.使用RECURSIVE，WITH查询可以引用其自身的输出.

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

这将为您提供:

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

，然后您可以将其放回数据库之外的链接列表(或客户端语言中合适的任何东西)中的路径.当然，您不必包括parent，但包括它可以帮助您修正指针".

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".

这篇关于PostgreSQL:循环直到条件为真的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！