Created a variable type:

Create type ComprositionPrixod AS ( Id bigint, Name character varying, Count bigint, Price double precision );

Now I create a table:

 CREATE TABLE "Prixod" ( "id" serial NOT NULL, "Date" timestamp without time zone, "OrgPostav" character varying, "NameSklad" character varying, "Сomposition" ComprositionPrixod[], "Operator" character varying, "Total" double precision DEFAULT sum(), CONSTRAINT Prixod_pk PRIMARY KEY ("id") ) WITH ( OIDS=FALSE );

So here, how can I correctly refer to the Сomposition[] array and calculate the total amount of cash on arrival? This should be put in the Total Column.

    1 answer 1

    Do not use floating point numbers to store money! Only fixed-point numbers, i.e. numeric .

    First, how to count by array. For example, a subquery:

      select sum(u.price) from unnest(Сomposition) u

    Default value

    In the default subquery can not write. Well, it's okay, you can wrap the subquery in the store and it will be possible. But here it is impossible to refer to other fields from default . And here it is already. Default value is not appropriate.

    Trigger

    Judging by the names of the fields, you initially do not need a default , but a trigger. default filled only once - with insert , though only if this field has not been assigned a value. And to make an update by changing the array - and from the business point of view, the field will simply lie, it will not be updated with update . But triggers can always keep the field up to date, even when you try to enter a value yourself.

     CREATE OR REPLACE FUNCTION prixod_trigger_func() RETURNS trigger AS $TRIGGER$ BEGIN NEW."Total" = (select sum(u.price) from unnest(NEW."Сomposition") u); return NEW; END; $TRIGGER$ LANGUAGE plpgsql; CREATE TRIGGER prixod_trigger BEFORE INSERT OR UPDATE ON "Prixod" FOR EACH ROW EXECUTE PROCEDURE prixod_trigger_func();.

    But I don’t think you should use an array of composite types instead of a separate table. The id in the type will have to be monitored independently, FK will not hang if necessary, refer to it from somewhere else. And to the trigger all the same what to consider, can go to another table.

    • My error did not write the fields, the id of the type (the unique value of the product) will be synchronized with the raw materials in the warehouse, programmatically. But the total amount, I would consider it automatic, taking into account always relevant information, it would be cool. - Oma
    • NEW. "Total" = (select sum (u.price) from unnest (NEW. "Сomposition") u); return NEW; Immediately it does not occur exactly the count, the fact that I want? It will work this way: sum (u.price * u.count) - Oma
    • Well, where do I know what is stored there and how exactly the sum should be calculated? Yes, sum(u.price * u.count) can also be written. - Shallow