Timescaledb: CTE in continous view

[docteurklein]

Hi :)

I was trying to use a CTE as the source of a continuous view, and it seems like it is not supported, and can't find anything in the docs or issues.

create table events (
    aggregate_id uuid not null,
    type text not null,
    added_at timestamptz not null default clock_timestamp(),
    payload jsonb not null
);
select create_hypertable('events', 'added_at');

\set user_1 uuid_generate_v4()
\set user_2 uuid_generate_v4()

insert into events
    (aggregate_id ,  added_at                 ,  type                    ,  payload) values
    (:user_1      ,  now() + interval '1 day' ,  'user-created'          ,  '{"name": "john"}'),
    (:user_1      ,  now() + interval '2 day' ,  'user-changed-password' ,  '{"new_password": "1234"}'),
    (:user_2      ,  now() + interval '1 day' ,  'user-created'          ,  '{"name": "bob"}'),
    (:user_2      ,  now() + interval '3 day' ,  'user-banned'           ,  '{"name": "bob"}')
;

create view users with (timescaledb.continuous) as
with t as (
    select * from events
)
select 
    time_bucket('1 minute', added_at) bucket,
    payload->'name' as name
    from t
    group by bucket, name
;

Am I doing something wrong?

Relevant system information:

• OS: archlinux
• PostgreSQL version 11.5
• TimescaleDB version 1.5.1
• Installation method: [docker

[gayyappan]

https://docs.timescale.com/latest/api#continuous_aggregate-create_view
lists the accepted types of queries. CTEs are not supported.

[bboule]

re-opening issue adding this as a feature request.