Conditional function not working as intended postgresql

Question

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$
;
~~~

What it onhold switched from (true to false), what if from (false to true). dbfiddle.uk/… Actually the code works: If only does not fill the date column if new and old on_hold column is true — S-Man
– S-Man, Commented Aug 16, 2019 at 12:13
@S-Man If the on hold is true then it's not expected to fill the date. So on your 2nd case where on_hold turns to true, it's not expected. — Luffydude
– Luffydude, Commented Aug 27, 2019 at 9:40
In the fiddle I showed, that your code works imho. So, it is not clear, what needs to be changed or what is your problem. — S-Man
– S-Man, Commented Aug 27, 2019 at 15:31
@S-Man thanks for replying but UPDATE mytable SET status=50, on_hold = true WHERE id=2; -- false becomes true -> WORKS, EXPECTED? -- it should not work, the date should stay blank because the on_hold is true — Luffydude
– Luffydude, Commented Aug 27, 2019 at 15:37
Ok: false -> false: Nothing happens. true -> true: Nothing happens. true -> false: Nothing happens. Only false -> true: Date should be written. Right? — S-Man
– S-Man, Commented Aug 27, 2019 at 15:41

S-Man · Accepted Answer · 2019-08-27 16:09:35Z

1

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;
$$;

answered Aug 27, 2019 at 16:09

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

Conditional function not working as intended postgresql

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