CODEWITHSUNDEEP
sqlpostgresqlunionsql-view
Avner Levy
Avner Levy

Reputation: 6741

Choosing the view query at runtime (postgres database)

I want to create a view which will choose between two possible selects based on a session variable (set_config) on runtime.
Today I do it by a "union all" between two selects in the following manner: 

create view my_view as (
select * from X where cast(current_setting('first_select') as int)=1 and ...;
union all
select * from Y where cast(current_setting('first_select') as int)=0 and ...;
)

The problem is that Postgres optimizer takes bad decisions when the target is a union. So when I run for example: 

select * from my_view where id in (select id from Z where field='value')

It decides to do a full scan on table X although it has an index on "id".
Is there another way to define such a view without using a "union" clause?

Upvotes: 0

Views: 153

Answers (1)

wildplasser
wildplasser

Reputation: 44250

Just OR them together into the WHERE-clause. The optimiser will find the invariant conditions.

CREATE VIEW my_view AS (
SELECT * FROM X WHERE 
        ( cast(current_setting('first_select') as int)=1 AND <condition1> )
 OR     ( cast(current_setting('first_select') as int)=0 AND <condition2> )
        ...
        )
        ;

Upvotes: 1

Related Questions