jsonize.sql

CREATE OR REPLACE FUNCTION get_children(genre_id integer)
RETURNS json AS $$
DECLARE
result json;
BEGIN
SELECT array_to_json(array_agg(row_to_json(t))) INTO result -- inject output into result variable
FROM ( -- same CTE as above
  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 id, name, ARRAY[]::INTEGER[] AS children FROM genres_materialized_path WHERE $1 = genres_materialized_path.path[array_upper(genres_materialized_path.path,1)] -- some column polish for a cleaner JSON
) t;
RETURN result;
END;
$$ LANGUAGE PLPGSQL;