[SOLVED] PostgreSQL trigger function using "case when" and "else"

Issue

I made a code for update trigger. Whenever I update, I want to fill "price" column of table "new_book" with 1000 if "price" is blank, or 5% discount if "price" is not blank. My postsql code is

create function test()
returns trigger
as
$$
declare pr1 numeric ;
begin
case when old.price is null then pr1=1000 
else pr1=pr1*0.95 end
end;
return new;
end
$$
language plpgsql; 

but if i run this code, PGAdmin shows an error like that

ERROR:  syntax error at or near "else"
LINE 8: else pr1=pr1*0.95 end
        ^
SQL state: 42601
Character: 117

How can I solve this problem?

Solution

You want an IF statement. And if you want to change the value that is stored in the table, you need to modify the new record:

create function test()
  returns trigger
as
$$
begin
  if new.price is null then 
    new.new_book := 1000;
  else 
    new.new_book := new.price * 0.95
  end if;
  return new;
end
$$
language plpgsql; 

In order for the assignment to actually be persisted, you have to use a BEFORE trigger:

create trigger set_new_book_trigger
  before insert or update on the_table
  for each row 
  execute procedure test();

Answered By – a_horse_with_no_name

Answer Checked By – David Marino (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.