0

I'm trying to do a function with two in parameters year and code from a table to return the count of the given code and year. The table is something like this:

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

I was able to return the count with the function below but when I give values to year and code in which there is no count it returns NULL but zero should be returned, how can I do that?:

CREATE OR REPLACE FUNCTION yearly_orders
    (year INT, order_cat orders.code%type) RETURNS INT AS $$
    
DECLARE
    total INT DEFAULT 0;
    
BEGIN
    SELECT COUNT(orders.code) INTO total
    FROM orders
    WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = year::INT AND orders.code = order_cat
    GROUP BY year, orders.code;
    RETURN total;
END;
$$LANGUAGE plpgsql;

The data is something like:

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);
2
  • If you run the query outside the function, do you get the same result? Apr 29, 2021 at 6:30
  • 1
    What Am I doing wrong? 1) Use reserved words as object names 2) Interfere parameters names and variables names 3) Select excess (unused) columns 4) Use unneeded ordering 5) Select a bunch of values into scalar variable 6) Specify plpgsql language whereas SQL seems to be enough 7) Use intermediate variable for the value to be returned which is excess 8) Do not check input values for their corretcness but use defauklt values which makes no sense. Maybe something else...
    – Akina
    Apr 29, 2021 at 7:42

2 Answers 2

2

This would work:

CREATE OR REPLACE FUNCTION yearly_orders(_year int, _order_cat int)
  RETURNS int
  LANGUAGE sql STABLE AS
$func$
SELECT count(*)::int
FROM   orders o
WHERE  o.status = 'Requested'
AND    o.creation >= to_timestamp(_year::text, 'YYYY')
AND    o.creation <  to_timestamp((_year + 1)::text, 'YYYY')
AND    o.code = _order_cat;
$func$;

The conversion from integer to timestamp really depends on what you pass exactly and what that's supposed to mean. You might want to_timestamp(_year::text, 'YY') instead ...

Consider the list in Akina's comment.
Start reading the manual about about SQL functions.
Have a look at PL/pgSQL functions after that.

Overview:

5
  • Thank you very much for your help. The only thing is that if I call the function with a year in which there is no count for that code it returns NULL instead of Zero. Maybe if I do if count = NULL then count =0 END if; or something like that ? thanks.
    – tucomax
    Apr 29, 2021 at 10:29
  • @tucomax: That should not occur. count() never returns NULL (unlike some other aggregate functions). There must be something else that's not in your question ... (But count = NULL never makes sense as NULL never equals anything.) Apr 29, 2021 at 10:35
  • thank you very much. I'm ok with the answer it worked pretty well. I was meaning IS NULL in my previous comment sorry, my mistake.
    – tucomax
    Apr 29, 2021 at 10:48
  • @ErwinBrandstetter - you have SELECT count(*)::int - why do you cast a COUNT(*) to an INT? Surely, a COUNT can never be anything but an INT?
    – Vérace
    Apr 29, 2021 at 11:12
  • @Vérace: count() returns bigint. Might make more sense to return bigint to begin with, but the OP seems to want int. Apr 29, 2021 at 11:20
1

Seeing as the question has been reopened, I'll post my solution too.

'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 Your 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, 2394);

Returns

retyear | retcode | retcout
------: | ------: | ------:
   2019 |    2394 |       3

db<>fiddle

The db<>fiddle to try this out can be found here.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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