Conditionals inside a for loop-CodePudding

Inside a STORED PROCEDURE, I'm trying to change the selection of my for loop, depending on the variable, JOINS AND VARIABLES change and retrieve different data, but I can't put an if inside the for loop.

V_NEW_LOGIC BOOLEAN;
BEGIN

V_NEW_LOGIC = false;
 FOR rec IN(
    IF(V_NEW_LOGIC) THEN
        SELECT T1.var_old AS VAR
        FROM tables
        [JOIN T1 TABLE#1]
    ELSE
        SELECT T2.var_new as VAR
        FROM tables
        [JOIN T2 TABLE#2]
    END IF;
 ) LOOP
   /************/
   OTHER ACTIONS
   /************/
END LOOP;

Is there a way to achieve it, for code readability I don't want to opt for temporary tables or materialized views, I had thought of a function that returns a list, but I couldn't find a way to incorporate it inside (FOR IN)

Any suggestion?

CodePudding user response：

As long as the datatype is SQL compatible, you can bring it into the SQL predicates. So we need to map the boolean to an integer

V_NEW_LOGIC BOOLEAN;
v_bool_as_int int;
BEGIN

V_NEW_LOGIC = false;
v_bool_as_int := case when V_NEW_LOGIC then 1 else 0 end;
 FOR rec IN(
        SELECT *
        FROM tables
        WHERE ( v_bool_as_int = 1 and conditions#1] )
        OR (( v_bool_as_int = 0 and conditions#2] )
 ) LOOP
   /************\
   OTHER ACTIONS
   /************\
END LOOP;

CodePudding user response：

You could declare two cursors (from your If command) and use the condition expression to open and fetch one or another. Fetches could be set into one variable if selected columns from cursors are of the same number and types or into two variables if that is not the case. Something like this:

Declare
    Cursor c_1 IS
        SELECT T1.var_old AS VAR
        FROM tables
        [JOIN T1 TABLE#1];
        
    Cursor c_2 IS
        SELECT T2.var_new as VAR
        FROM tables
        [JOIN T2 TABLE#2];
        
    rec     c_1%ROWTYPE;
--  rec2    c_2%ROWTYPE     --  just if selected cursors' columns are of different number and/or type(s)
Begin
    If your_condition_expression is True Then
        OPEN c_1;
    Else
        OPEN c_2;
    End If;
    
    Loop
        If your_condition_expression is True Then
            FETCH c_1 Into rec;
            EXIT WHEN c_1%NOTFOUND;
        Else
            FETCH c_2 Into rec;
        --  FETCH c_2 Into rec2;    -- or this fetch just for the case that selected columns are of different number and/or type(s)
            EXIT WHEN c_2%NOTFOUND;
        End If;
        
        /************/
        OTHER ACTIONS
        /************/
    End Loop;
        
    /*****************/
    SOME OTHER ACTIONS
    /****************/
End

Regards...