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

Função sem parâmetros e chamando uma função dentro

  • 772

Eu sou novo com o PostgreSQL e me sinto bastante sobrecarregado ..

O que estou tentando alcançar é algo assim:

Resultado desejado

ano descrever por cento
2019 palavra palavra palavra 0,10
2018 Palavra palavra triste 0,80
2017 Palavra palavra vazia 0,15
etc palavra palavra palavra 0,41

Onde porcentagem é função_A dividida por função_B** (função_A / função_B).

já tenho as funções

  • function_A tem dois parâmetros: ano (extraído de status_date) e código. Retorna o número de pedidos definidos pelos parâmetros anteriores.
  • function_B tem um parâmetro: ano (extraído de status_date). Retorna o número total de pedidos por ano.

Função

Isto é o que, erroneamente, fiz até agora - incluindo o código da resposta original do nbk :

A função não deve ter nenhum parâmetro.

CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc text, percentage bigint) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip text DEFAULT 0;
    percent bigint DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM created_dt) as numeric),
                order_desc as text,
                ROUND(function_A(inyear,incode))::decimal / function_B(inyear) as percent
        FROM orders o
        WHERE EXTRACT(YEAR FROM o.creation) = inyear
        AND o.order_code = incode
        AND o.descrip 
        GROUP BY EXTRACT(year FROM o.creation), o.code
        ORDER BY retyear ASC;
END;
$$
LANGUAGE plpgsql;

Para chamar a função, espero usar:

SELECT * from summary_code()

Meus dados são mais ou menos assim:

Tabela 'encomendas'

CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    descript CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

Registros

INSERT INTO orders 
(id, code, service_id, descript, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I'm tired','2019-06-16 11:12','2019-06-18 14:08',1);

Desculpe se isso soa um pouco atrevido, mas não sei por onde começar.

postgresql

1 respostas

  1. Best Answer
    CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    descript CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

    INSERT INTO orders 
(id, code, service_id, descript, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested not today','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled blue squit','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested blue kit','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested pony','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested sauron','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested I''m tired','2019-06-16 11:12','2019-06-18 14:08',1);

    CREATE FUNCTION function_A(in numeric, in int,out noorder int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1 AND "code" = $2 $$
    LANGUAGE SQL;

    CREATE FUNCTION function_B(in numeric,out noorderyear int)
    AS $$ SELECT COUNT(*) FROM orders WHERE EXTRACT(YEAR FROM  "status_date") = $1  $$
    LANGUAGE SQL;

    CREATE OR REPLACE FUNCTION summary_orders()
RETURNS TABLE (retyear numeric, order_desc TEXT, percentage numeric) AS $$
DECLARE
    retyear numeric DEFAULT 0;
    descrip TeXT DEFAULT 0;
    percent numeric DEFAULT 0;
BEGIN
    RETURN
        QUERY SELECT CAST(EXTRACT(year FROM status_date) as numeric) teayear,
                MIN(descript) as descrip,
                ROUND(Max(function_A(CAST(EXTRACT(year FROM status_date) as numeric),code))::decimal / function_B(CAST(EXTRACT(year FROM status_date) as numeric)),2) as percent
        FROM orders o
        GROUP BY teayear
        ORDER BY teayear ASC;
END;
$$
LANGUAGE plpgsql;

    SELECT * FROM summary_orders()

    reano | ordem_desc | percentagem
------: | :------------------- | ---------:
   2018 | Squit azul cumprida | 1,00
   2019 | Kit azul solicitado | 0,60

    db<>fique aqui

    • 0

relate perguntas