Update an attribute according to two other tables

Question

Table Company

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 table revenue attribute Enterprise according to the amount of people who make a trip and its cost (table attribute fate). The company’s revenue will be all travel revenue (price x qty_passengers).

I thought I’d use a Trigger that every time I insert, delete or update a trip I update the field recipe, but I could not develop the Procedure.

Answer 1

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

CREATE SCHEMA stackoverflow
  AUTHORIZATION postgres;

Table Company + 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;

Table Car + INSERT

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');

Table Trip + INSERT

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);

Table Destination + 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;

To Procedure as one of the possible answers 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 are taken from the Internet.}

---

Reference:

_{[Chitij Chauhan - 2015], Copyright 2015 Packt Publishing, Postgresql Cookbook: Over 90 hands-on Recipes to effectively Manage, administer, and design Solutions using Postgresql}

_{[Ibrar Ahmed et al - 2015], Copyright 2015 Packt Publishing, Postgresql Developer’s Guide: Design, develop, and implement streamlined Databases with Postgresql}