By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent 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
);
CREATE TABLE
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);
INSERT 0 6
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
| year | code | count |
|---|---|---|
| 2019 | 2394 | 3 |
SELECT 1
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;
CREATE FUNCTION
select * from yearly_orders(2019, 2394);
| retyear | retcode | retcout |
|---|---|---|
| 2019 | 2394 | 3 |
SELECT 1