Everything works as intended, but function should return 0 if value is NULL. Currently it returns nothing (see problem at the end of the post).
How could I make to return 0 if no order is found?
'orders' Table
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
);
Records
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);
Testing Statement
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
Returns
year | code | count :--- | ---: | ----: 2019 | 2394 | 3
Function
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;
Executing Function
select * from yearly_orders(2019, 1194);
Returns <– PROBLEM
retyear | retcode | retcout ------: | ------: | ------: | |
SHOULD RETURN
retyear | retcode | retcout ------: | ------: | ------: 0 | 0 | 0
db<>fiddle
The db<>fiddle to try this out can be found here.
The code was made by user:15356 (Here)
Best Answer
You are wanting
GROUP BY
and theCOUNT
aggregate to return a row with a zero value when there are otherwise no rows to return -GROUP
ing does not work like that. It combines what rows would have been returned ifGROUP BY
was not present, so no rows in that case.You could add a second
SELECT
, combined withUNION ALL
, that returns one row containinginyear, incode, 0
if there are zero matching rows (counted by sub-select). If there are rows this part returns nothing, if there are no matches it returns a row indicating that. Note that this may be quite inefficient.