PostgreSQL Question posted in
The official documentation can be found here.

Optimize case statement with a single select in PostgreSQL PL/pgSQL script

I’m using this statement to retrieve the boolean value of a column in order to set a session_variable with that value:
This is the statement:

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;

Next, I put set session variables with those variables:

PERFORM session_variable.set('COSWIN_CTX.ENTITY_CONDITION',existCondition0);
PERFORM session_variable.set('COSWIN_CTX.ZONE_CONDITION', existCondition1);
PERFORM session_variable.set('COSWIN_CTX.FUNCTION_CONDITION', existCondition2);
PERFORM session_variable.set('COSWIN_CTX.STORE_CONDITION', existCondition3);
PERFORM session_variable.set('COSWIN_CTX.CONTRACT_CONDITION', existCondition4);

I wonder how can I optimize this script, like using just one Select, but nothing comes in my mind. What do you think about it?

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    If you really want to return booleans, not integers, you can use the aggregate function bool_or():

    SELECT BOOL_OR(cwgv_table = 0) AS b0
     , BOOL_OR(cwgv_table = 1) AS b1
     , BOOL_OR(cwgv_table = 2) AS b2
     , BOOL_OR(cwgv_table = 3) AS b3
     , BOOL_OR(cwgv_table = 4) AS b4
FROM group_views  
WHERE cwgv_group = 1;

    This function also handles NULLs, that will result in a false

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search