When inserting or updating data I would like to be able to perform some math on two columns and have that entered as a value for a third column.
CREATE TABLE "public"."subscriptions" ( "id" int4 NOT NULL DEFAULT nextval('subscriptions_id_seq'::regclass), "item" varchar, "amount" float4, "yearly_recurrance" int2, "annual_cost" float4, "rank" int2, PRIMARY KEY ("id") );
INSERT INTO "public"."subscriptions" ("item", "amount", "yearly_recurrance", "rank") VALUES ('test', '19', '7', '0');
I have created a function and trigger that in my mind should take the amount and multiply it by the yearly_recurrance and enter the result in the annual_cost field.
CREATE OR REPLACE FUNCTION public.calc_cost() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN NEW.annual_cost = NEW.amount * NEW.yearly_recurrance; RETURN NEW; END; $function$
CREATE TRIGGER calc_cost BEFORE INSERT or UPDATE ON subscriptions FOR EACH STATEMENT EXECUTE PROCEDURE calc_cost();
It is not working out how I expect. Instead the values from the insert statement are put where they belong, but it is as though the function doesn’t run. Not getting any errors that I can see.
I should mention that I believe the trigger is working and calling the function. If I put garbage in the function I get errors. I think the problem is in the function. Given this is my first function however, I am not sure how to work with uncommitted data. Perhaps it’s not possible.