How to limit recursive view in PostgreSQL?

Question

PostgreSQL

Is it possible to somehow limit the recursive view in PostgreSQL in case it becomes infinite?
View code https://gist.github.com/kissarat/5d45c69a068192c17d36
Infinity occurs when the article hierarchy becomes cyclic when, for example, parent_id article(2) refers to id article(1), in which parent_id refers to id article(1)

Answer 1

In this case, the number of levels is limited (nesting) WHERE r.category_level < 8

CREATE OR REPLACE VIEW category AS
  WITH RECURSIVE r(id, title, lang, category_level, category_id, parent_id) AS (
    SELECT
      a.id,
      a.title,
      a.lang,
      0    AS category_level,
      a.id AS category_id,
      a.parent_id
    FROM article a
    UNION
    SELECT
      a.id,
      a.title,
      a.lang,
      r.category_level + 1 AS category_level,
      r.category_id,
      a.parent_id
    FROM article a
      JOIN r ON r.parent_id = a.id AND r.lang = a.lang
    WHERE r.category_level < 8
  )
  SELECT
    r.id,
    r.title,
    r.lang,
    r.parent_id,
    first_value(r.id) OVER (PARTITION BY category_id ORDER BY r.id) AS root_id,
    row_number() OVER (PARTITION BY category_id ORDER BY r.id) - 1 AS "level",
    r.category_level,
    r.category_id
  FROM r
    WHERE category_level > 0
  ORDER BY category_id DESC;

Answer 2

see the documentation , there is an example where loops are cut off