Function without parameters, and calling a function inside

Question

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.

Answer 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

• nbk: 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