postgresqlif-statementplpgsql

Unable to use IF statement in plpgsql


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.