Search code examples
sqldatabasepostgresqlpgadmin-4

Create autogenerated column from other table in PosgreSQL

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?

enter image description here

Solution

  • 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() ;