Unable to use IF statement in plpgsql

September 24, 2024

Here is the relevant part of my code (Please note, ignoredwp is a boolean function parameter):

where 
 
cp.imei in (select distinct IMEI from imei_clustered) 

and cp."generatedAt"::date between cs."campaignStartDate" and cs."campaignEndDate"


    IF ignoredwp=true THEN
        AND NOT EXISTS (
            SELECT 1 
            FROM filtering f 
            WHERE f."IMEI" = cp.imei 
              AND f.date = cp."generatedAt"::date
        );
    END if;

This throws an error: ERROR: syntax error at or near "IF"

>Solution :

IF is a PL/pgSQL command. You cannot use PL/pgSQL commands in the middle of SQL statements. Normally, the solution is to use a SQL CASE expression instead, but since you are trying to compose an SQL statement, you’ll have to use dynamic SQL:

DECLARE
   sql text;
BEGIN
   sql := $sql$SELECT ... WHERE ...$sql$;

   IF ignoredwp THEN
      sql := sql || $sql$ AND NOT EXISTS (
            SELECT 1 
            FROM filtering f 
            WHERE f."IMEI" = cp.imei 
              AND f.date = cp."generatedAt"::date
        )$sql$;
   END IF;

   EXECUTE sql;
END;

If you need to process the results from the query, use EXECUTE sql INTO var or loop with FOR var IN EXECUTE sql LOOP.