postgresql function to update each row based on IF ELSEIF conditional

Question

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

Abelisto · Accepted Answer · 2018-08-08 21:56:05Z

1

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

answered Aug 8, 2018 at 21:56

Abelisto

15.8k3 gold badges38 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

postgresql function to update each row based on IF ELSEIF conditional

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related