How to compare two last updated entities from two different table Postgresql?

Question

I have 2 tables named as price and price_history. price_history table is automatically filled by triggers from price table just before updating the price table. I would like see the change at the new table named as change and fill it by triggers again just before the price table will be updated.

Function:

create or Replace function test()
returns trigger
as
$$
begin

update change set change_= SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 - SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;
return new;

end;
$$
language plpgsql;

Trigger:

CREATE TRIGGER testtrig
BEFORE UPDATE
ON price
FOR EACH ROW 
EXECUTE PROCEDURE test();

I get error while using the querry --> SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1 - SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1 ;

Can you help me please? Thanks

Answer 1

You are missing some parenthesis around your select queries.

But i don't understand completely what you try to achieve, i think you should at least also transfer the produdct_id, because for now you get a price from a unknown product.

create or Replace function test() returns trigger as $$ begin

update change 
set change_= (SELECT price_ FROM price ORDER BY(last_update) DESC LIMIT 1) - (SELECT price_ FROM price_history ORDER BY(last_update) DESC LIMIT 1) ; 
return new;

end; $$ language plpgsql;




CREATE TRIGGER testtrig BEFORE UPDATE ON price 
FOR EACH ROW 
  EXECUTE PROCEDURE test();