CODEWITHSUNDEEP
postgresqlstored-procedurespsql
john
john

Reputation: 1075

postgresql function to update each row based on IF ELSEIF conditional

I am trying to create a postgresql function that will be executed by a cron job every 30 minutes. This function will execute an if else conditional and update a column based on the current time and other column values.

I keep getting a syntax issue at the beginning of the IF statement and cant understand why:

ERROR:  syntax error at or near "scheduled_active_start"
LINE 7:      IF scheduled_active_start <= executionTime AND schedule...
                ^
********** Error **********

ERROR: syntax error at or near "scheduled_active_start"
SQL state: 42601
Character: 144

Here is the postgresql function I am trying to create:

CREATE OR REPLACE FUNCTION updatePostActivity()   RETURNS void AS $$

DECLARE
    executionTime timestamp = Now();
BEGIN
    UPDATE  post
        IF scheduled_active_start <= executionTime AND scheduled_active_end > executionTime AND is_active != true THEN
             SET is_active = true;
        ELSEIF  scheduled_active_end <= executionTime AND is_active != false THEN 
            SET is_active = false;
        END IF;
END;
$$   
LANGUAGE plpgsql VOLATILE

Upvotes: 0

Views: 738

Answers (1)

Abelisto
Abelisto

Reputation: 15624

Syntax of the UPDATE statement clearly stays that there is nothing between UPDATE and SET keywords except the table definition. The IF statement is absent in the SQL at all (as I know). Use CASE statement instead:

UPDATE post SET
  is_active = CASE 
    WHEN scheduled_active_start <= executionTime AND scheduled_active_end > executionTime AND is_active != true THEN true
    WHEN scheduled_active_end <= executionTime AND is_active != false THEN false
  END
WHERE -- Filter to avoid updating whole table
  (scheduled_active_start <= executionTime AND scheduled_active_end > executionTime AND is_active != true) OR
  (scheduled_active_end <= executionTime AND is_active != false);

PS: is_active != true equal to not is_active and is_active != false equal to just is_active

Upvotes: 1

Related Questions