How to simplify procedure using IF statements inside where clause in PL/pgSQL-CodePudding

I have quite a few procedures that rely on performing different queries based on the value of certain config variables. For example it'll be something like

IF (check_unit='Y') THEN
  SELECT my_key, my_id
  INTO l_my_key, l_my_id
  FROM data
  WHERE my_num = NEW.my_num AND
        my_data = NEW.my_date AND
        my_unit = NEW.my_unit;

ELSE
  SELECT my_key, my_id
  INTO l_my_key, l_my_id
  FROM data
  WHERE my_num = NEW.my_num AND
        my_data = NEW.my_date;

This is repeated over and over in multiple procedures across the database. I think there must be an easy way to do an IF or other condition so that if my_unit = 'Y' we will do the query with the 2 AND otherwise we will just do the 1 AND command without having to be so explicit writing both full queries out.

I'm still getting used to PL/pgSQL so if there is an easy way to do this I apologize. Any help or direction would be greatly appreciated. If the way it is being done is the standard or accepted way then that's okay too.

CodePudding user response：

You can easily move the IF condition into a conditional expression in the WHERE to choose your clauses:

SELECT my_key, my_id
INTO l_my_key, l_my_id
FROM data
WHERE (CASE WHEN check_unit = 'Y'
  THEN my_num = NEW.my_num AND
       my_data = NEW.my_date AND
       my_unit = NEW.my_unit
  ELSE my_num = NEW.my_num AND
       my_data = NEW.my_date
END);

Then simplify

SELECT my_key, my_id
INTO l_my_key, l_my_id
FROM data
WHERE my_num = NEW.my_num AND
      my_data = NEW.my_date AND
      (CASE WHEN check_unit = 'Y' THEN my_unit = NEW.my_unit ELSE TRUE END);

and further

SELECT my_key, my_id
INTO l_my_key, l_my_id
FROM data
WHERE my_num = NEW.my_num AND
      my_data = NEW.my_date AND
      (check_unit <> 'Y' OR my_unit = NEW.my_unit);

CodePudding user response：

In SQL there is no IF, where is your logical work-horse. So something like:

select my_key, my_id
  into l_my_key, l_my_id
  from data
 where my_num = new.my_num 
   and my_data = new.my_date 
   and (   new.my_unit  <> 'Y' 
        or ( my_unit = new.my_unit and 
                new.my_unit  <> 'Y'
              ) 
          ) ;

However, please describe what you are actually trying to accomplish.

The above will only work within an insert or update row level trigger function. The pseudo row new exists only in row triggers and new.x will always be NULL on a delete trigger. Further, triggers do not return values (other than a row image or null) and cannot be called directly.