Create autogenerated column from other table in PosgreSQL-CodePudding

I have two tables: postavka and detail. I need to add an autogenerated column to postavka which is generated from multiplying postavka.count on detail.price (detail selected by detail field in postavka).

I created script ALTER TABLE IF EXISTS public.postavka ADD COLUMN total_price double precision GENERATED ALWAYS AS (postavka.count * (select price from detail where (postavka.detail == detail.id)) STORED; but it is incorrect. How I must change this to be valid?

CodePudding user response：

GENERATED columns can only refer to other columns of the same table, so it is not relevant in your case.

You can do the job with a trigger function :

CREATE OR REPLACE FUNCTION before_insert_update_postavka() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  SELECT NEW.count * price
    INTO NEW.total_price
    FROM detail
   WHERE id = NEW.detail ;
  RETURN NEW ;
END ; $$
    
CREATE OR REPLACE TRIGGER before_insert_update_postavka BEFORE INSERT OR UPDATE ON postavka
FOR EACH ROW EXECUTE FUNCTION before_insert_update_postavka() ;