Recursive CTEs

[HurricanKai]

Is your feature request related to a problem? Please describe.

I'm trying to build a table with a row every 10 minutes, to populate a table for analytics.

Not sure if there's another way to do this, I'll keep experimenting, but a recursive CTE seems fairly straightforward.
Here's the query I tried:

WITH RECURSIVE times AS
    (SELECT now() as t
     UNION ALL
     SELECT t - interval '1 hour'
     FROM times
     WHERE t > (now() - interval '90 days'))
SELECT * FROM times;

Describe the solution you'd like

I'd like this to be supported

Describe alternatives you've considered

I'll keep experimenting but I'm not sure what the alternatives are.

Additional context

No response

[chenzl25]

You can use generate_series instead.

SELECT now() - interval '90 days' + (t || ' hour')::interval AS t 
FROM generate_series(1, 90 * 24, 1) AS t;

[ly9chee]

Recursive CTEs may also resolve the querying of tree-structured data, such as departments. Our department table has more than 15 levels, and in order to solve this without Recursive CTE we attempt to build each level data independently and then union them all together to form the final table as the workaround. here's the sql:

CREATE MATERIALIZED VIEW dim_dept_level1 AS
SELECT
       id,
       parent_id,
       name,
       ARRAY [name] AS dept_path,
       1            AS level
FROM ods_department WHERE parent_id is null;

CREATE MATERIALIZED VIEW dim_dept_level2 AS
SELECT
       id,
       parent_id,
       name,
       p.dept_path || c.name AS dept_path,
       2                     AS level
FROM ods_department c JOIN dim_dept_level1 p ON c.parent_id = p.id;

...

CREATE MATERIALIZED VIEW dim_dept_level15 AS
SELECT
       id,
       parent_id,
       name,
       p.dept_path || c.name AS dept_path,
       15                    AS level
FROM ods_department c JOIN dim_dept_level14 p ON c.parent_id = p.id;

CREATE MATERIALIZED VIEW dim_dept AS
SELECT * FROM dim_dept_level1
UNION ALL
SELECT * FROM dim_dept_level2
UNION ALL
SELECT * FROM dim_dept_level3
...
UNION ALL
SELECT * FROM dim_dept_level15;

This workaround works well but it leads to writing more redundant code and cannot handle the situations where a new level is added in the department table. If a new level is added in, we need to create a new mv dim_dept_level16 and rebuild the mv dim_dept.

I think Recursive CTEs can solve this seamlessly, hope it will get supported soon!!

[chenzl25]

I think Recursive CTEs can solve this seamlessly, hope it will get supported soon!!

Feature Recursive CTE is on our roadmap. We are still investigating what is the best way to implement it in RisingWave.

[TennyZhuang]

Tracked in #15135.