I'm working with session variables and I would want to create a view which does the following:
if (myVar=1)
select * from my_table where X
else
select * from my_table where Y
Where X and Y are different clauses.
Is it possible?
Can this be implemented with a regular select statement (or I need stored procedures)?
Try something like this:
SELECT 'A'
FROM tableA
WHERE current_setting(setting_name) = 'setting A'
UNION ALL
SELECT 'B'
FROM tableB
WHERE current_setting(setting_name) = 'setting B'
Details on postgresql session variables here.
UPD It will give the results of one of the SELECT. If current_setting(setting_name) equals to 'setting A' the first query will return the results, but the second wont.
For your example the query will look like:
SELECT 'A'
FROM tableA
WHERE myVar = 1
UNION ALL
SELECT 'B'
FROM tableB
WHERE myVar != 1
UPD Checked: postgres executes only one of the queries. EXPLAIN ANALYZE shows that the second query was planned but marked as (never executes).
WHERE clause. It will give you only one of the queries.EXPLAIN this query with different myVar. The plan will show what postgres will do.I usually use stored procedures or UDF (if your RDBMS supports them). As you cannot pass parameters to a view, a view is not suitable. However, you can achive something like that by creating a view like this
create view MyView as
select 1 as MyVar, * from A
union
select 2 as MyVar, * from B
and filter the column MyVar in your application
select * from MyView where MyVar = :MyVar
However, this usually involves a table scan of the whole view. Again, depending on the RDBMS you may be able to put an index on the column 'MyVar' and speed up this approach.
I recommend using a stored procedure, it makes your attempt more clear.
EXPLAIN ANALYZE and see how the query is being executed; you might find that Pg is applying the filter to both branches of the union rather than producing all the results and then scanning them.