There are 2 tables and a data type:
CREATE TABLE "Sklad" ( "Id" serial NOT NULL, "Name" character varying NOT NULL, "Price" numeric NOT NULL, "Uint" character varying NOT NULL, "Count" bigint, CONSTRAINT Sklad_pk PRIMARY KEY ("Id") ) WITH ( OIDS=FALSE ); CREATE TABLE "Rashod" ( "id" serial NOT NULL, "Date" timestamp without time zone NOT NULL, "NameSklad" character varying NOT NULL, "Composition" ComprositiontRashod[] NOT NULL, "Operator" character varying NOT NULL, "Total" double precision NOT NULL, CONSTRAINT Rashod_pk PRIMARY KEY ("id") ) WITH ( OIDS=FALSE ); Create type ComprositiontRashod AS ( Id bigint, Name character varying, Count bigint ); Here I try to write the trigger:
CREATE OR REPLACE FUNCTION rashod_trigger_func() RETURNS trigger AS $TRIGGER$ DECLARE Count bigint; Id bigint; BEGIN Count = (select ("Sklad".Count - u.Count) from "Sklad", unnest(NEW."Composition") u WHERE NEW.Id = "Sklad"."Id" GROUP BY u.Count ) ; UPDATE "Sklad" SET "Count"= Count WHERE "Sklad"."Id" = 3; return NEW; END; $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER rashod_trigger BEFORE INSERT OR UPDATE ON "Rashod" FOR EACH ROW EXECUTE PROCEDURE rashod_trigger_func(); It should in the table "Sklad" reduce the value of 'Count' by the number of units left. But it does not work ...
u.Id = "Sklad"because in double quotation marks in this case is the name of the table. How do you compare the column with the whole table - Mike