dlyaDenisa

CREATE TABLE availability_details(
    num_warehouse INT NOT NULL CHECK ( num_warehouse>0 ),
    id_detail INT NOT NULL CHECK ( id_detail>0 ),
    unit_detail VARCHAR(3) NOT NULL DEFAULT 'шт',
    quantity_in_warehouse INT NOT NULL CHECK ( quantity_in_warehouse>=0 ),
    date_last_oper DATE NOT NULL,
    PRIMARY KEY (num_warehouse,id_detail)
);
CREATE TABLE details(
    id_detail SERIAL NOT NULL PRIMARY KEY CHECK ( id_detail>0 ),
    type_of_detail TEXT NOT NULL,
    name_of_detail TEXT NOT NULL,
    unit_detail VARCHAR(3) NOT NULL DEFAULT 'шт',
    price_unit_detail DECIMAL NOT NULL CHECK ( price_unit_detail>0 )
);
CREATE TABLE shipment_accounting(
    num_warehouse INT NOT NULL CHECK ( num_warehouse>0 ),
    num_of_ship_doc INT NOT NULL CHECK ( num_of_ship_doc>0 ),
    id_customer INT NOT NULL CHECK ( id_customer>0 ),
    id_ready_detail INT NOT NULL CHECK ( id_ready_detail>0 ),
    unit_detail VARCHAR(3) NOT NULL DEFAULT 'шт',
    quantity INT NOT NULL CHECK ( quantity>=0 ),
    date_of_ship_acc DATE NOT NULL,
    PRIMARY KEY (num_warehouse,num_of_ship_doc),
    CONSTRAINT fkdu_num_ware_house_id_detail FOREIGN KEY(num_warehouse,id_ready_detail)
        REFERENCES availability_details(num_warehouse,id_detail) ON DELETE CASCADE ON UPDATE CASCADE DEFERRABLE INITIALLY DEFERRED,
    CONSTRAINT fk_id_detail FOREIGN KEY (id_ready_detail) REFERENCES details(id_detail) DEFERRABLE INITIALLY DEFERRED
);

//триггеры и их создание
CREATE OR REPLACE FUNCTION trigger_before_del_id_detail() RETURNS
    trigger AS'
    BEGIN
    DELETE FROM shipment_accounting WHERE id_ready_detail=old.id_detail;
    DELETE FROM availability_details WHERE id_detail=old.id_detail;
    RETURN old;
    END;
' LANGUAGE plpgsql;
CREATE TRIGGER tr_id_detail_before
BEFORE DELETE ON details FOR EACH ROW
EXECUTE PROCEDURE trigger_before_del_id_detail();

CREATE OR REPLACE FUNCTION trigger_before_upd_id_detail() RETURNS
    trigger AS'
    BEGIN
    UPDATE shipment_accounting
    SET id_ready_detail=new.id_detail
    WHERE id_ready_detail=old.id_detail;
    UPDATE availability_details
    SET id_detail=new.id_detail
    WHERE id_detail=old.id_detail;
    RETURN new;
    END;
' LANGUAGE plpgsql;
CREATE TRIGGER tr_id_detail_before_upd
BEFORE UPDATE ON details FOR EACH ROW
EXECUTE PROCEDURE trigger_before_upd_id_detail();

//заполнение таблиц, заполняй внимательнее не забудь проверить!!!
INSERT INTO details(type_of_detail, name_of_detail, price_unit_detail)
VALUES ('собственное','доска',508),
       ('собственное','профнастил',1135),
       ('покупное','цемент',208),
       ('собственное','металлочерепица',1364),
       ('покупное','шифер',557);
INSERT INTO availability_details(num_warehouse, id_detail, quantity_in_warehouse, date_last_oper)
VALUES (7,1,200,DATE '2021-12-04'),
       (6,4,300,DATE '2021-02-04'),
       (8,5,100,DATE '2021-11-02'),
       (9,3,250,DATE '2021-11-15'),
       (10,4,500,DATE '2021-12-05'),
       (7,2,306,DATE '2021-12-04'),
       (9,5,250,DATE '2021-12-14');
INSERT INTO shipment_accounting(num_warehouse, num_of_ship_doc, id_customer, id_ready_detail, quantity, date_of_ship_acc)
VALUES (9,1,1,3,200, DATE'2021-11-04'),
       (7,2,2,1,122, DATE'2021-12-05'),
       (8,3,3,5,200, DATE'2021-11-011'),
       (6,4,1,4,200, DATE'2021-10-04'),
       (10,5,1,4,200, DATE'2021-12-04'),
       (7,6,2,2,122, DATE'2021-12-05'),
       (9,7,1,5,200, DATE'2021-11-04');

//процедура
CREATE PROCEDURE get_quantity_orders_and_details(IN id_cust INT, INOUT quantity_orders INT, INOUT quantity_details INT)
AS $$
    BEGIN
        quantity_orders:=(SELECT COUNT(id_customer) FROM  shipment_accounting WHERE id_customer=id_cust);
        quantity_details:=(SELECT SUM(quantity) FROM shipment_accounting WHERE id_customer=id_cust);
    END;
$$ LANGUAGE plpgsql;
CALL get_quantity_orders_and_details(1,0,0);