Create Function to update column

Asked

Viewed 739 times

-1

I need to update a column according to the contents of the other two columns, for example, I have two tables, the table suprimentos and the table estoque,

Table supplies
codigoSuprimento
capacidadeSuprimento

Stock Table
codigoSuprimento
capacidadeUsada
percentualUsado

Let’s say on the table suprimentos i own the following register:

codigoSuprimento | capacidadeSuprimento
       1         |         1000

And on the table estoque the following register:

codigoSuprimento | capacidadeUsada | percentualUsado
       1         |        300      |         X

My need is to create a Function so that the column percentualUsado is completed in accordance with capacidadeUsada on top of capacidadeSuprimento. Seria capacidadeUsada x 100 / capacidadeSuprimento. But I don’t know how to do it in a Function, put this content in parameters to be able to perform this calculation which is an unknown to me.

postgresql function parameters
  • 1

    Explain one thing to me, you could not do subselect for Stock table insertion, Example: INSERT INTO estoque (codigoSuprimento,capacidadeUsada,percentualUsado)
(1,300,(SELECT 300*100/(capacidadeSuprimento) FROM suprimento WHERE codigoSuprimento = 1)) this is just a small example, so you can better organize your reasoning line.

  • @Marcusitalo, thanks for the tip, but yesterday analyzing my need, I realized that there would be no need to know the percentage that the supply was used because knowing the used capacity already solves my problem, sometimes it is necessary to just blur a little of the problem to find that the answer is right in the face, but again thanks for the tip

1 answer

1


Assuming the following structure:

CREATE TABLE tb_suprimento
(
    cod INTEGER,
    capacidade INTEGER
);

CREATE TABLE tb_estoque
(
    cod_suprimento INTEGER,
    capacidade_usada INTEGER,
    percentual_usado INTEGER
);

Containing the following data:

INSERT INTO tb_suprimento ( cod, capacidade ) VALUES ( 1, 1000 );
INSERT INTO tb_suprimento ( cod, capacidade ) VALUES ( 2, 500 );
INSERT INTO tb_suprimento ( cod, capacidade ) VALUES ( 3, 300 );
INSERT INTO tb_suprimento ( cod, capacidade ) VALUES ( 4, 750 );
INSERT INTO tb_suprimento ( cod, capacidade ) VALUES ( 5, 400 );

INSERT INTO tb_estoque ( cod_suprimento, capacidade_usada ) VALUES ( 1, 300 );
INSERT INTO tb_estoque ( cod_suprimento, capacidade_usada ) VALUES ( 2, 100 );
INSERT INTO tb_estoque ( cod_suprimento, capacidade_usada ) VALUES ( 3, 120 );
INSERT INTO tb_estoque ( cod_suprimento, capacidade_usada ) VALUES ( 4, 50 );
INSERT INTO tb_estoque ( cod_suprimento, capacidade_usada ) VALUES ( 5, 400 );

Function:

CREATE OR REPLACE FUNCTION fc_atualizar_estoque( INTEGER )
  RETURNS void AS
$BODY$
BEGIN
    UPDATE
        tb_estoque
    SET
        percentual_usado = (100 * capacidade_usada) / tb_suprimento.capacidade 
    FROM
        tb_suprimento
    WHERE
        tb_suprimento.cod = $1 AND
        tb_estoque.cod_suprimento = $1;
END;$BODY$
LANGUAGE plpgsql;

Testing:

SELECT fc_atualizar_estoque(1);
SELECT fc_atualizar_estoque(2);
SELECT fc_atualizar_estoque(3);
SELECT fc_atualizar_estoque(4);
SELECT fc_atualizar_estoque(5);

With Trigger:

CREATE OR REPLACE FUNCTION fc_atualizar_estoque()
  RETURNS trigger AS
$BODY$
DECLARE
    cap INTEGER;
BEGIN
    SELECT capacidade FROM tb_suprimento WHERE cod = NEW.cod_suprimento INTO cap;
    NEW.percentual_usado = (100 * NEW.capacidade_usada) / cap;
    RETURN NEW;
END;$BODY$
LANGUAGE plpgsql;

CREATE TRIGGER tg_atualizar_estoque BEFORE INSERT OR UPDATE ON tb_estoque FOR EACH ROW EXECUTE PROCEDURE fc_atualizar_estoque();

  • I could activate this Function with a Trigger?

Browser other questions tagged postgresql function parameters

You are not signed in. Login or sign up in order to post.