category.sql

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
  )
  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;