Exercise involving Function in PostgreSQL

Question

Exercise involving Function in PostgreSQL

Navigation

#1 by (0 votes)

0

The question would be:

Having the following Bank:

Telefone (id, numero, operadora_id)
Cliente (id, cpf, nome, rg, sexo, uf)
Operadora (id, nome, cnpj)
Contrato(id, cliente_id, telefone_id, plano_id, data_contrato, valor_final)
Plano (id, valor, operadora_id, descricao)

You should do the following:

Uma função que receba o id de um cliente e de uma operadora, e 
verifique se o cliente já possui algum telefone da operadora, se 
sim, deve dar um desconto de 50% no seu contrato que possui o maior 
valor. Senão deve criar um contrato do cliente com essa operadora, 
usando o menor valor do plano desta operadora.

I have already been able to do a function that returns if the customer actually has a contract with the operator and if yes gives a 50% discount on the contract of higher value, however I could not make if that contract does not there is, create a new contract between this customer and the operator using a plan of this lower value operator.

CREATE OR REPLACE FUNCTION QUEST3A
(CLI_ID INTEGER, OPE_ID INTEGER) 
RETURNS VARCHAR
AS 
$$
DECLARE
  CLI_NOME VARCHAR;
  RESULTADO BOOL;
BEGIN
  SELECT CLI.NOME INTO CLI_NOME
  FROM CLIENTE CLI
  LEFT JOIN CONTRATO CON    ON CLI.ID = CON.CLIENTE_ID
  LEFT JOIN TELEFONE TEL    ON TEL.ID = CON.TELEFONE_ID
  LEFT JOIN PLANO PLA       ON PLA.ID = CON.PLANO_ID
  LEFT JOIN OPERADORA OPE ON OPE.ID = PLA.OPERADORA_ID
  WHERE CLI.ID = CLI_ID AND OPE.ID = OPE_ID;

  IF CLI_NOME IS NULL THEN
    /*if dont't exists enrollment*/
        RESULTADO := FALSE;

  ELSE
    /*if it exists*/
    RESULTADO := TRUE;
    UPDATE CONTRATO
    SET VALOR_FINAL = X.RESULTADO/2
    FROM    
    (
        SELECT CON.ID, MAX(CON.VALOR_FINAL) AS RESULTADO
        FROM CLIENTE CLI
        LEFT JOIN CONTRATO CON  ON CLI.ID = CON.CLIENTE_ID
        LEFT JOIN TELEFONE TEL  ON TEL.ID = CON.TELEFONE_ID
        LEFT JOIN PLANO PLA     ON PLA.ID = CON.PLANO_ID
        LEFT JOIN OPERADORA OPE ON OPE.ID = PLA.OPERADORA_ID
        WHERE CLI.ID = CLI_ID AND TEL.ID = OPE_ID
        GROUP BY CON.ID
        ORDER BY CON.VALOR_FINAL DESC
        LIMIT 1
    )X
    WHERE X.ID = CONTRATO.ID;
  END IF;

RETURN RESULTADO;
END;
$$
LANGUAGE PLPGSQL;

Thank you very much for helping me. Strong Embrace.

sql postgresql

asked by anonymous 11.06.2017 / 23:35

1 answer

How to finish in past activity how to make a php file into transparent image

score 0 · Answer 1

The answer I found so far would be this

 INSERT INTO CONTRATO(CLIENTE_ID, TELEFONE_ID, DATA_CONTRATO, PLANO_ID, VALOR_FINAL) 
        values
        (
            CLI_ID, 
            (
                SELECT TEL2.ID 
                FROM CONTRATO CON2
                INNER JOIN TELEFONE TEL2  ON CON2.TELEFONE_ID = TEL2.ID
                INNER JOIN CLIENTE CLI2 ON CON2.CLIENTE_ID = CLI2.ID
                INNER JOIN OPERADORA OPE2 ON TEL2.OPERADORA_ID = OPE2.ID
                WHERE CLI2.ID = CLI_ID
                LIMIT 1
            ),
            /*DATA_CONTRATO*/
            CURRENT_DATE,
            /*PLANO_ID*/
            (SELECT ID FROM PLANO WHERE OPERADORA_ID = OPE_ID ORDER BY VALOR ASC LIMIT 1),
            /*VALOR_FINAL*/
            (SELECT VALOR FROM PLANO WHERE OPERADORA_ID = OPE_ID ORDER BY VALOR ASC LIMIT 1)
        );

Which would be in the first block of the IF / ELSE within Function, however I believe it is not the most elegant way to resolve this issue.

Is there any other way? Thank you in advance.