Update an attribute according to two other tables

Question

Update an attribute according to two other tables

Navigation

#1 by (0 votes)

1

Company Table

CREATE TABLE empresa (
    cnpj        char(18) PRIMARY KEY,
    nome        varchar(60) NOT NULL UNIQUE,
    receita     float NOT NULL,
    pais_sede   varchar(26) NOT NULL
);

Target table

CREATE TABLE destino(
    cidade varchar(60) PRIMARY KEY,
    estado char(2) NOT NULL,
    valor float NOT NULL,
    cnpj char(18) REFERENCES empresa(cnpj) ON DELETE CASCADE ON UPDATE CASCADE
)

Travel table

CREATE TABLE viagem(
    data date NOT NULL,
    hora time NOT NULL,
    placa char(8) REFERENCES carro(placa) ON DELETE NO ACTION ON UPDATE CASCADE,
    cidade char(60) REFERENCES destino(cidade) ON DELETE NO ACTION ON UPDATE CASCADE,
    nro_passageiros integer NOT NULL CHECK (nro_passageiros > 0),
    PRIMARY KEY (data, hora, placa, cidade)
)

I came across a problem where I need to update the recipe attribute of the Company table according to the number of people making a trip and its cost ( destination table attribute >). The company's revenue will be all travel revenue (price x qtde_passengers).

I thought of using a trigger where every time I insert, delete or update a trip I update the recipe field, but I could not develop the procedure . p>

sql postgresql trigger atributos plpgsql

asked by anonymous 30.11.2017 / 01:33

1 answer

Return int on a select using Dapper. Error while selecting date focused on bootstrap datepicker

score 0 · Accepted Answer

First of all, follow the schema with the SQL tables in the PostgreSQL dialect :

CREATE SCHEMA stackoverflow
  AUTHORIZATION postgres;

Company table + INSERT

CREATE TABLE stackoverflow.empresa
(
    cnpj character(18) NOT NULL,
    receita real NOT NULL,
    pais_sede character varying(26) NOT NULL,
    nome character varying(60),
    CONSTRAINT empresa_pkey PRIMARY KEY (cnpj)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stackoverflow.empresa
  OWNER TO postgres;


INSERT INTO stackoverflow.empresa(
        cnpj, receita, pais_sede, nome)
VALUES  ('00881753000153', 30001.53, 'BRASIL', 'A M S IMPRESSOS E EMBALAGENS LTDA'),
        ('19861350000170', 00001.70, 'CHILE', 'A PREFERIDA COMERCIAL LTDA'),
        ('19861350000413', 00004.13, 'BRASIL', 'A PREFERIDA COMERCIAL LTDA'),
        ('19861350000251', 00002.51, 'PARAGUAI', 'A PREFERIDA COMERCIAL LTDA'),
        ('19861350000332', 00003.32, 'BRASIL', 'A PREFERIDA COMERCIAL LTDA'),
        ('08145915000105', 50001.05, 'CHILE', 'A&R DO BRASIL ACESSORIOS AUTOMOTIVOS LTDA'),
        ('06130273000137', 30001.37, 'BRASIL', 'A.W.A. EQUIPAMENTOS HIDRAULICOS LTDA'),
        ('18310979000160', 90001.60, 'CHILE', 'ABC COMERCIO LTDA'),
        ('71038996000172', 60001.72, 'BRASIL', 'ABRANTES & ALEM LIMITADA'),
        ('19002831000120', 10001.20, 'HOLANDA', 'ABRASIVOS JUIZ DE FORA LTDA'),
        ('64336498000140', 80001.40, 'BRASIL', 'AC CONSULTORIA EMPRESARIAL LTDA EPP'),
        ('03845557000545', 70005.45, 'CHILE', 'ACCORD EXPRESS DISTRIBUICAO E LOGISTICA LTDA'),
        ('21329008000176', 80001.76, 'BRASIL', 'ACL COMERCIAL ELETRICA E ELETRONICA LTDA'),
        ('05410268000115', 80001.15, 'CANADA', 'ACOPAR LTDA'),
        ('09234570000110', 00001.10, 'BRASIL', 'ACOS ALIANCA LTDA'),
        ('04565028000127', 80001.27, 'CHILE', 'ACOS RAMOS LTDA'),
        ('02072108000115', 80001.15, 'BRASIL', 'ACOSIDER COMERCIO DE PROD');


SELECT count(r.pais_sede) AS "qtd", r.pais_sede
    FROM stackoverflow.empresa  r
GROUP BY r.pais_sede;

Carriage + INSERT table

CREATE TABLE stackoverflow.carro
(
    placa character varying(8) NOT NULL,
    CONSTRAINT carro_pkey PRIMARY KEY (placa)
)
WITH (
    OIDS=FALSE
);
ALTER TABLE stackoverflow.carro
  OWNER TO postgres;


INSERT INTO stackoverflow.carro(placa)
    VALUES ('ICE 2973'),('NNS 4646'),('BFQ 8663'),('ABC 1234'),('ICE 2973'),('PEC 2013');

Travel + INSERT Table

CREATE TABLE stackoverflow.viagem
(
    data date NOT NULL,
    hora timestamp without time zone NOT NULL,
    cidade character(60) NOT NULL,
    placa character varying(8) NOT NULL,
    nro_passageiro integer,
    CONSTRAINT viagem_pkey PRIMARY KEY (data, hora, placa, cidade),
    CONSTRAINT viagem_cidade_fkey FOREIGN KEY (cidade)
      REFERENCES stackoverflow.destino (cidade) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT viagem_placa_fkey FOREIGN KEY (placa)
      REFERENCES stackoverflow.carro (placa) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
    OIDS=FALSE
);
ALTER TABLE stackoverflow.viagem
  OWNER TO postgres;


INSERT INTO stackoverflow.viagem(
        data, hora, cidade, placa, nro_passageiro)
VALUES 
    ('2015-01-08','04:05:06','cidade 1', 'NNS 4646', 5),
    ('2015-01-08','04:05:06','cidade 2', 'ABC 1234', 5),
    ('2015-01-08','04:05:06','cidade 3', 'ABC 1234', 5),
    ('2015-01-08','04:05:06','cidade 4', 'PEC 2013', 1);

Target Table + INSERT

CREATE TABLE stackoverflow.destino
(
    cidade character varying(60) NOT NULL,
    estado character(2) NOT NULL,
    valor real NOT NULL,
    cnpj character(18),
    CONSTRAINT destino_pkey PRIMARY KEY (cidade),
    CONSTRAINT destino_cnpj_fkey FOREIGN KEY (cnpj)
      REFERENCES stackoverflow.empresa (cnpj) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE stackoverflow.destino
  OWNER TO postgres;

INSERT INTO stackoverflow.destino(
        cidade, estado, valor, cnpj)
VALUES 
    ( 'cidade 1', 'AM', 2000.0, '00881753000153'),
    ( 'cidade 2', 'BA', 2000.0, '00881753000153'),
    ( 'cidade 3', 'CE', 2000.0, '00881753000153'),
    ( 'cidade 4', 'CE', 1.53,    '00881753000153');


SELECT COUNT(d.cnpj) AS "qtd", SUM(d.valor) AS "receita", d.cnpj
FROM stackoverflow.destino d
GROUP BY d.cnpj;

SELECT COUNT(d.cnpj) AS "qtd", SUM(d.valor) AS "receita", d.cnpj 
FROM stackoverflow.destino d
INNER JOIN stackoverflow.empresa e ON (e.cnpj = d.cnpj)
GROUP BY d.cnpj;

The procedure as one of the possible responses would be as follows :

-- DROP FUNCTION stackoverflow."calcReceita"();

CREATE OR REPLACE FUNCTION stackoverflow."calcReceita"(p_cnpj CHARACTER)
  RETURNS real AS
$BODY$DECLARE
     v_receita stackoverflow.empresa.receita%TYPE;

    BEGIN
        /**
            SELECT SUM(d.valor) INTO v_receita 
            FROM stackoverflow.destino d
            WHERE cnpj = p_cnpj
            GROUP BY d.cnpj;
        */
            SELECT SUM(receita_produto) INTO v_receita 
            FROM  (
                SELECT (d.valor * v.nro_passageiro) AS "receita_produto"
                FROM stackoverflow.destino d
                INNER JOIN stackoverflow.viagem v USING (cidade)
                WHERE cnpj = p_cnpj
            ) AS tb_soma_receita;

        UPDATE stackoverflow.empresa
        SET receita=v_receita
        WHERE cnpj=p_cnpj;

     RETURN v_receita;
    END;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION stackoverflow."calcReceita"()
  OWNER TO postgres;

/** Aqui chama a procedure. */
SELECT stackoverflow."calcReceita"('00881753000153');

NOTE : Sample data is taken from the internet.

Reference :