Search for field in Postgresql recursive query-CodePudding

I need to make tree from self-linked table, for example:

CREATE TABLE tree(
id serial primary key,
parent int4,
text_field varchar(255)
);

Topics about recursive CTE's tells that recursive query stops executing recursion when there are no rows in recursive part of select statement. I need some kind of depth search, my query works wrong way:

WITH RECURSIVE tree_query as(
SELECT T0.id, T0.parent, T0.text_field
FROM tree T0
WHERE T0.parent is null --search from root
UNION
SELECT TT0.id, TT0.parent, TT0.text_field
FROM tree TT0
INNER JOIN tree_query t ON t.id = TT0.parent
WHERE TT0.text_field LIKE '%smth%'
)
SELECT * FROM tree_query;

The main reason it works wrong - it stops execution when I searching for something in level 3-infinity, because there are no relevant rows in recursion part of query and I only get a root.

CodePudding user response：

Search till found

WITH RECURSIVE tree_query as(
  SELECT T0.id, T0.parent, T0.text_field, (T0.text_field LIKE '%smth%') as found  
  FROM tree T0
  WHERE T0.parent is null --search from root
  UNION
  SELECT TT0.id, TT0.parent, TT0.text_field, (TT0.text_field LIKE '%smth%') 
  FROM tree TT0
  INNER JOIN tree_query t ON t.id = TT0.parent
  WHERE NOT t.found
)
SELECT * 
FROM tree_query
WHERE found;

The query will find the first matching node in every branch.