redshift does not create view with no schema binding with recursive cte inside

user_8436086 0
Viewed 802

what's the problem

i am trying to create a view in aws redshift with recursive cte inside it and with no schema binding option, but i recieve error like there is no such tables that i create a view over.

[0A000] ERROR: All the relation names inside should be qualified when creating VIEW WITH NO SCHEMA BINDING.

does anybody know any workaround? i need the view to be with no schema binding and i would really love to have it

how to reproduct the error

create table some_stuff as select 1 as id;

create view stuff_recursive as (
    WITH RECURSIVE
    cte (id, inc) as (
        select id, 0 as inc from some_stuff
        UNION ALL
        select d.id, inc + 1 from some_stuff as d, cte
        where d.id = cte.id
        and inc < 5
        )
    select * From cte
)
WITH NO SCHEMA BINDING
;

additional info

my current redshift version

PostgreSQL 8.0.2 on i686-pc-linux-gnu, 
compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3),
Redshift 1.0.34928
amazon-redshift sql-view common-table-expression sql
1 Answers

The tables in your query must all be qualified with the schema.

create table MYSCHEMA.some_stuff as select 1 as id;

create view stuff_recursive as (
    WITH RECURSIVE
    cte (id, inc) as (
        select id, 0 as inc from MYSCHEMA.some_stuff
        UNION ALL
        select d.id, inc + 1 from MYSCHEMA.some_stuff as d, cte
        where d.id = cte.id
        and inc < 5
        )
    select * From cte
)
WITH NO SCHEMA BINDING
;
user_187858860
Related