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"
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
.