0

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

1
  • Error? What error? I don't see no error. Also, what you call a query is not; it doesn't follow any defined syntax rules. – mustaccio Jan 5 at 0:34
0

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();
2
  • Frankly, I would like to achieve price change of the item. Price table is always up to date. Old prices is sent to the price_history table. I was thinking that holding the change data on different table makes it faster in terms of database performance. Doesn't it? – ei0306 Jan 5 at 6:32
  • Your function query gets the last price entered in the database , which isn't propably the correct value . It is hard to tell what you are trying to do but you have the production your trigger so use that to get the last prices – nbk Jan 5 at 10:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.