I wrote the following plpgsql function. The problem I have is that if no
rows are found my function returns NULL whereas it should be returning 0.
Where have I erred?
create or replace function zoqoo_total_sales(integer) returns integer as '
declare total_sales integer := 0;
begin
select into total_sales sum(price)
from invoice_li, invoices WHERE
shop_id=$1 AND not invoice_li.cancelled
AND shipped AND invoices.id=invoice_id
AND not invoices.cancelled AND payment_rcvd;
IF NOT FOUND THEN
RETURN 0;
END IF;
RETURN total_sales;
end;
' language 'plpgsql' with (iscachable);
JC=# select zoqoo_total_sales(1);
zoqoo_total_sales
-------------------
(1 row)
Thanks,
Jc
