postgres 递归查询同一张表 [英] postgres recursive query on the same table

问题描述

我现在花了将近一天的时间，似乎我做错了什么.好的，这是关系:document_urls( doc_id , url_id)

i spent almost a day on it now and it seems like i am doing something wrong. ok , here is the relation: document_urls( doc_id , url_id)

我想要做的是构建一个图表，该图表将显示通过他的网址从文档生成的所有子项.例子select * from document_urls where doc_id=1

what i want to do is to build a sorte of graph that will show all the children that has been generated from a document through on of his urls. example select * from document_urls where doc_id=1

doc_id url_id
1 2
1 3

doc_id url_id
1 2
1 3

如果我选择所有 url_id=3 或 2 的文档，我会找到select * from document_urls where url_id=2 or url_id=3

if i select all the document with url_id=3 or 2 i will find select * from document_urls where url_id=2 or url_id=3

doc_id url_id
1 2
1 3
2 3

doc_id url_id
1 2
1 3
2 3

现在我对文档 2 做同样的练习，因为我们涵盖了文档 1 的所有链接等等.

now i do the same exercise with document 2 since we covered all links of document 1 and so forth.

这是我的递归查询

WITH  RECURSIVE generate_links(document_id,url_id) as(  
    select document_id,url_id from document_urls where document_id=1 
UNION ALL
    select du.document_id,du.url_id from generate_links gl,document_urls du
    where gl.url_id=du.url_id 
)

SELECT * FROM generate_links GROUP BY url_id,document_id limit 10;

推荐答案

我认为您想将 where document_id=1 移动到查询的下半部分.

I take it you want to move your where document_id=1 into the lower part of the query.

但是，请注意这样做，因为递归查询不会将约束注入 with 语句.换句话说，它实际上会 seq 扫描您的整个表，递归构建所有可能性并过滤掉您需要的那些.

Be wary about doing so, however, because a recursive query does not inject the constraint into the with statement. In other words, it'll actually seq scan your whole table, recursively build every possibility and filter out those you need.

在实践中使用 sql 函数会更好，例如:

You'll be better off with an sql function in practice, i.e. something like this:

create or replace function gen_links(int) returns table (doc_id int, doc_url text) as $$
WITH  RECURSIVE generate_links(document_id,url_id) as(  
    select document_id,url_id from document_urls where document_id=$1
UNION ALL
    select du.document_id,du.url_id from generate_links gl,document_urls du
    where gl.url_id=du.url_id 
)

SELECT * FROM generate_links GROUP BY url_id,document_id;
$$ language sql stable;

这篇关于postgres 递归查询同一张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！