postgres recursive query on the same table

Question

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)

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

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

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

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;

Answer 1

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

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.

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;