0

I'm new with PostgreSQL and feeling quite overwhelmed..

What I'm trying to achieve is something like this:

Wanted outcome

year descrip percent
2019 Word word word 0.10
2018 Word word sad 0.80
2017 Word empty word 0.15
etc Word word word 0.41

Where percent is function_A divided by function_B** (function_A / function_B).

I already have the functions

  • function_A has two parameters : year (extracted from status_date) and code. Returns the number of orders defined by the previous parameters.
  • function_B has one parameter: year (extracted from status_date). Returns the total number of orders by year.

Function

This is what, wrongly, I did so far - including code from nbk's original answer:

The function shouldn't have any parameters.

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;

To call the function I expect to use:

SELECT * from summary_code()

My data looks something like this:

'orders' Table

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

Records

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

I'm sorry if this comes as a little cheeky, but I don't know where to start.

0
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()
retyear | order_desc           | percentage
------: | :------------------- | ---------:
   2018 | Fulfilled blue squit |       1.00
   2019 | Requested blue kit   |       0.60

db<>fiddle here

1
  • Description is not defined and you wanted result also yields no information what the text should be. so on that part you have to do some changes – nbk May 6 at 10:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.