There are tables:
CREATE TABLE newsklad ( "Name" character varying, "Uint" character varying, "Count" numeric, "CreateTime" timestamp without time zone, "Changed" boolean, "OneCID" character varying, CONSTRAINT "newsklad_Name_CreateTime_key" UNIQUE ("Name", "CreateTime") ) CREATE TABLE sklad ( "Name" character varying NOT NULL, "Uint" character varying NOT NULL, "Count" numeric, "CreateTime" timestamp without time zone NOT NULL, "Changed" boolean, "OneCID" character varying, CONSTRAINT "sklad_Name_CreateTime_key" UNIQUE ("Name", "CreateTime") ) CREATE TABLE "Transfer" ( "ID" serial NOT NULL, "Date" timestamp without time zone NOT NULL, "SkladPostav" character varying NOT NULL, "NameSklad" character varying NOT NULL, "Сomposition" comprositionttransfer[] NOT NULL, "Operator" character varying NOT NULL, "Total" numeric, "UpdateOper" text, "Changed" boolean, "OneCID" character varying, CONSTRAINT transfer_pk PRIMARY KEY ("ID") ) And the trigger:
CREATE OR REPLACE FUNCTION transfer_insert_trigger_func() RETURNS trigger AS $TRIGGER$ DECLARE rec ComprositiontTransfer; sqlSelectPrixodPostav varchar; sqlSelectPrixodPostav2 varchar; sqlInsertPrixodPostav varchar; sqlUpdatePrixodPostav varchar; count numeric; countlen numeric; countTovar numeric; CreateList timestamp without time zone; BEGIN sqlSelectPrixodPostav = 'Select count("Count") From ' || NEW."NameSklad" || ' WHERE ("Name" = $1) and ("CreateTime" = $2)'; sqlSelectPrixodPostav2 = 'Select "Count" From ' || NEW."NameSklad" || ' WHERE ("Name" = $1) and ("CreateTime" = $2) ORDER BY "Count" '; sqlUpdatePrixodPostav = 'UPDATE ' || NEW."NameSklad" || ' SET "Count" = $3, "Changed" = true WHERE ("Name" = $1) and ("CreateTime" = $2)'; sqlInsertPrixodPostav = 'INSERT INTO ' || NEW."NameSklad" || ' ("Name", "Uint", "Count", "CreateTime", "Changed") VALUES ($1,(Select "Units" From "Product" s WHERE (s."Name" = $1)) , $2, $3, true)'; /*execute sqlInsertPrixodPostav using 'Огурчик',5452;*/ FOR rec.Name, rec.Count, rec.CreateTime IN SELECT u.Name, u.Count, u.CreateTime FROM unnest(NEW."Сomposition") u GROUP BY u.Name, u.Count, u.CreateTime LOOP execute sqlSelectPrixodPostav into countlen using rec.Name, CreateList; execute sqlSelectPrixodPostav2 into countTovar using rec.Name, CreateList; if countlen = 0 then execute sqlInsertPrixodPostav using rec.Name, rec.Count , CreateList; else countTovar := countTovar + rec.Count; execute sqlUpdatePrixodPostav using rec.Name, CreateList, countTovar; end if; END LOOP; return NEW; END; $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER insert_transfer_trigger AFTER INSERT ON "Transfer" FOR EACH ROW EXECUTE PROCEDURE transfer_insert_trigger_func(); Fill the table with values
INSERT INTO sklad( "Name", "Uint", "Count", "CreateTime") VALUES ('Мука', 'кг', 40, '2016-10-10 10:10:10'); B create shipping report:
INSERT INTO "Transfer"( "Date", "SkladPostav", "NameSklad", "Сomposition", "Operator", "Total") VALUES ( '2016-10-10 10:10:20', 'sklad', 'newsklad','{"(\"Мука\",''2016-11-03 10:30:25'',1,\"asdasdas\",\" \")"}', 'Коровкина',252); Duck in the table newsklad appears only the name and quantity, without time, writes that NULL. I just can not understand why?