Function without parameters, and calling a function inside

All we need is an easy explanation of the problem, so here it is.

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

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

Wanted outcome

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

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

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0