How can I optimize case statement with similar condition in PostgreSQL?

Question

I'm currently using a select-case statement in SQL to set variables based on logical conditions. Here's an example of what I'm doing:

SELECT 
    CASE WHEN EXISTS(SELECT 1 FROM group_views WHERE cwgv_group = vGroup AND cwgv_table = 0) THEN '1' ELSE '0' END,
    CASE WHEN EXISTS(SELECT 1 FROM group_views WHERE cwgv_group = vGroup AND cwgv_table = 1) THEN '1' ELSE '0' END,
    CASE WHEN EXISTS(SELECT 1 FROM group_views WHERE cwgv_group = vGroup AND cwgv_table = 2) THEN '1' ELSE '0' END,
    CASE WHEN EXISTS(SELECT 1 FROM group_views WHERE cwgv_group = vGroup AND cwgv_table = 3) THEN '1' ELSE '0' END,
    CASE WHEN EXISTS(SELECT 1 FROM group_views WHERE cwgv_group = vGroup AND cwgv_table = 4) THEN '1' ELSE '0' END
INTO 
    existCondition0, existCondition1, existCondition2, existCondition3, existCondition4;

I'm wondering if there's a more efficient approach to achieve the same outcome, perhaps using a single select statement without redundancy. Can anyone suggest optimizations for this script?

Answer 1

Please try this. Query written using single select statement including CASE. Use MAX/MIN function to handle no data found condition.

select COALESCE(MAX(case when cwgv_table = 0 then 1 end), 0)
     , COALESCE(MAX(case when cwgv_table = 1 then 1 end), 0)
     , COALESCE(MAX(case when cwgv_table = 2 then 1 end), 0)
     , COALESCE(MAX(case when cwgv_table = 3 then 1 end), 0)
     , COALESCE(MAX(case when cwgv_table = 4 then 1 end), 0)
into existCondition0, existCondition1, existCondition2
   , existCondition3, existCondition4   
from group_views  
where cwgv_group = vGroup;

Please check this url: https://dbfiddle.uk/nppF7JMs