Início / dba / Perguntas / 290693
Accepted
tucomax
Asked: 2021-04-29 22:23:14 +0800 CST

Como fazer uma função no postgresql com dois parâmetros para retornar contagem da tabela de colunas?

  • 772

Estou tentando fazer uma função com dois nos parâmetros ano e código de uma tabela para retornar a contagem do código e ano fornecido. A tabela é algo assim:

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

Consegui retornar a contagem com a função abaixo mas quando dou valores para ano e código em que não existe contagem ele retorna NULL mas deve ser retornado zero, como posso fazer isso?:

CREATE OR REPLACE FUNCTION yearly_orders
    (year INT, order_cat orders.code%type) RETURNS INT AS $$
    
DECLARE
    total INT DEFAULT 0;
    
BEGIN
    SELECT COUNT(orders.code) INTO total
    FROM orders
    WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = year::INT AND orders.code = order_cat
    GROUP BY year, orders.code;
    RETURN total;
END;
$$LANGUAGE plpgsql;

Os dados são algo como:

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);
stored-procedures postgresql

2 respostas

  1. Best Answer

    Isso funcionaria:

    CREATE OR REPLACE FUNCTION yearly_orders(_year int, _order_cat int)
  RETURNS int
  LANGUAGE sql STABLE AS
$func$
SELECT count(*)::int
FROM   orders o
WHERE  o.status = 'Requested'
AND    o.creation >= to_timestamp(_year::text, 'YYYY')
AND    o.creation <  to_timestamp((_year + 1)::text, 'YYYY')
AND    o.code = _order_cat;
$func$;

    A conversão de integerpara timestamprealmente depende do que você passa exatamente e do que isso deveria significar. Você pode querer to_timestamp(_year::text, 'YY')em vez disso ...

    Considere a lista no comentário de Akina.
    Comece a ler o manual sobre as funções SQL .
    Dê uma olhada nas funções PL/pgSQL depois disso.

    Visão geral:

    • 2

  2. Visto que a questão foi reaberta, postarei minha solução também.

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

    Testando sua declaração

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

    Devoluções

    reano | retcode | retcou
------: | ------: | ------:
   2019 | 2394 | 3

    db<>violino

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

    • 1

relate perguntas