with-recursive.sql

WITH RECURSIVE genres_materialized_path AS (
  SELECT id, name, ARRAY[]::INTEGER[] AS path
  FROM genres WHERE parent_id IS NULL

  UNION ALL

  SELECT genres.id, genres.name, genres_materialized_path.path || genres.parent_id
  FROM genres, genres_materialized_path
  WHERE genres.parent_id = genres_materialized_path.id
) SELECT * FROM genres_materialized_path;