Returned type bigint does not match expected type integer in column 3

Question

Below is my table structure for sold_quantity (Migration File)

alter table public.invoice_item add column sold_quantity int4 default 1;

Below is the function for execution

CREATE OR REPLACE FUNCTION sold_quantity()
RETURNS TABLE(
 invoiceid BIGINT,
 itemid BIGINT,
 sum_sold_quantity INT)
AS $$
BEGIN
 RETURN QUERY SELECT
 invoice_id as invoiceid, item_id as itemid, sum(sold_quantity) as
 sum_sold_quantity
 FROM
 invoice_item
 WHERE
 status='sold'
 GROUP BY
 invoice_id, item_id;
END; $$

What is the wrong in my code, Please help me solve this Error

Returned type bigint does not match expected type integer in column 3

Answer 1

sum() returns a bigint, not necessarily the type of the column that is being summed.

If you are 100% sure your sum never exceeds the range for an integer, you can fix this using a cast in your query: sum(sold_quantity)::int as sum_sold_quantity

But it would be better to adjust the signature of the function:

CREATE OR REPLACE FUNCTION sold_quantity()
RETURNS TABLE(
 invoiceid BIGINT,
 itemid BIGINT,
 sum_sold_quantity BIGINT)