Início / dba / Perguntas / 291034
Accepted
user229251
Asked: 2021-05-06 05:04:01 +0800 CST

não retornando 0 se o valor for NULL

  • 772

Tudo funciona como pretendido, mas a função deve retornar 0 se o valor for NULL. Atualmente não retorna nada (veja problema no final do post).

Como eu poderia fazer para retornar 0 se nenhum pedido for encontrado?

Tabela 'encomendas' 
CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    status CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );
Registros 
INSERT INTO orders 
(id, code, service_id, status, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);
Declaração de teste 
SELECT EXTRACT(YEAR FROM creation) AS year, 
                        orders.code, 
                        COUNT(orders.code) 
                FROM orders
                WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = 2019 AND orders.code = 2394
                GROUP BY year, orders.code
                ORDER BY year ASC
Devoluções 
ano | código | contar
:--- | ---: | ----:
2019 | 2394 | 3

Função

CREATE OR REPLACE FUNCTION 
yearly_orders(inyear numeric DEFAULT 0, incode INT DEFAULT 0) 
RETURNS TABLE (retyear numeric, retcode bigint, retcout bigint) AS 
$$
DECLARE
    retcount int DEFAULT 0;
    retcode int DEFAULT 0;
    retyear numeric DEFAULT 0;
BEGIN
    RETURN QUERY SELECT CAST(EXTRACT(year FROM creation) as numeric), 
                        CAST(orders.code as bigint), 
                        COUNT(orders.code)
                FROM orders
                WHERE status = 'Requested' 
                AND EXTRACT(YEAR FROM creation) = inyear 
                AND orders.code = incode
                GROUP BY EXTRACT(year FROM creation), orders.code
                ORDER BY retyear ASC;

END;
$$
LANGUAGE plpgsql;

Executando Função

select * from yearly_orders(2019, 1194);

Retorna <-- PROBLEMA

reano | retcode | retcou
------: | ------: | ------:
        | |
DEVE DEVOLVER 
reano | retcode | retcou
------: | ------: | ------:
 0 | 0 | 0

db<>violino

O db<>fiddle para testar isso pode ser encontrado aqui .

O código foi feito pelo usuário:15356 ( Aqui )

postgresql

2 respostas

  1. Best Answer

    Você está querendo GROUP BYe a COUNTagregação retornar uma linha com um valor zero quando não houver linhas para retornar - GROUPing não funciona assim. Ele combina quais linhas teriam sido retornadas se GROUP BYnão estivesse presente, portanto, nenhuma linha nesse caso.

    Você pode adicionar um segundo SELECT, combinado com UNION ALL, que retorna uma linha contendo inyear, incode, 0se houver zero linhas correspondentes (contadas por subseleção). Se houver linhas esta parte não retornará nada, se não houver correspondências ela retornará uma linha indicando isso. Observe que isso pode ser bastante ineficiente.

    • 1

  2. Isso é realmente complicado, você pode enviar uma mensagem como no exemplo

    CREATE OR REPLACE FUNCTION 
yearly_orders(inyear numeric DEFAULT 0, incode INT DEFAULT 0) 
RETURNS TABLE (retyear numeric, retcode bigint, retcout bigint) AS 
$$
DECLARE
    retcount int DEFAULT 0;
    retcode int DEFAULT 0;
    retyear numeric DEFAULT 0;
    row_cnt int DEFAULT 0;
BEGIN
    RETURN QUERY SELECT CAST(EXTRACT(year FROM creation) as numeric) , 
                        CAST(orders.code as bigint) , 
                        COUNT(orders.code) 
                FROM orders
                WHERE status = 'Requested' 
                AND EXTRACT(YEAR FROM creation) = inyear 
                AND orders.code = incode
                GROUP BY EXTRACT(year FROM creation), orders.code
                ORDER BY retyear ASC;
    GET DIAGNOSTICS row_cnt = ROW_COUNT;

   IF row_cnt =  0 THEN
      RAISE SQLSTATE '45000'   -- 5 ASCII chars
      USING MESSAGE = format('No orders found for year %s.',inyear);
   END IF;

END;
$$
LANGUAGE plpgsql;

    select * from yearly_orders(2020, 2394);

    ERRO: Nenhum pedido encontrado para o ano de 2020.
CONTEXTO: função PL/pgSQL yearly_orders(numeric,integer) linha 20 em RAISE

    db<>fique aqui

    • 0

relate perguntas