postgres在同一个表上递归查询

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)

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

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

我想要做的是构建一个图形,它将显示从文档生成的所有子项,通过他的URL。示例select * from document_urls其中doc_id = 1

doc_id url_id
1 2
1 3

doc_id url_id 1 2 1 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

如果我用url_id = 3或2选择所有文档,我将从document_urls中找到select *,其中url_id = 2或url_id = 3

doc_id url_id
1 2
1 3
2 3

doc_id url_id 1 2 1 3 2 3

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

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

here is my recursive query now

这是我的递归查询

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;

1 个解决方案

#1

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

我认为你想把你的document_id = 1移动到查询的下半部分。

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.

但是要小心这样做,因为递归查询不会将约束注入到with语句中。换句话说,它实际上将seq扫描整个表,递归地构建每个可能性并过滤掉你需要的那些。

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

在实践中你最好使用sql函数,例如:

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;

#1