CODEWITHSUNDEEP
sqlpostgresqlfunction
Luffydude
Luffydude

Reputation: 772

Conditional function not working as intended postgresql

So I got this table on postgresql that people regularly update and the below function.

table

id integer

status integer

date date

on_hold boolean

What this function is supposed to do is to fill out the date column automatically whenever the status becomes 50 and also if it was null

Problem is that i do not want the date column to be filled when the on_hold boolean column is true.

I've tried setting up the function just by typing on_hold = true but then it somehow says it doesn't exist. When i use old. or new. it doesn't pass any error but it still updates the date.

How to get to the intended result which is to not update the date when on_hold is true

CREATE OR REPLACE FUNCTION table_update()
 RETURNS trigger
 LANGUAGE plpgsql
AS $function$

BEGIN               
if new.status = 50 
and new.date is null 
and (new.on_hold = false or old.on_hold = false) 
then new.date = now() + interval '90' day ;
end if;
 RETURN NEW;                                                                    
END;


$function$
;
~~~

Upvotes: 0

Views: 88

Answers (1)

S-Man
S-Man

Reputation: 23676

Just change the condition to

and old.on_hold != true -- != true include false *AND NULL*

and maybe (if you do not want to change anything if status is 50 and becomes 50 again) add:

(new.status = 50 and old.status != 50)

Full function:

demo:db<>fiddle

CREATE OR REPLACE FUNCTION table_update()
    RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN               
    if (new.status = 50 and old.status != 50)
           and new.the_date is null 
           and old.on_hold != true 
    then 
        new.the_date = now() + interval '90' day;
    end if;

    RETURN NEW;                                                                    
END;
$$;

Upvotes: 1

Related Questions