From: | Jean-Christian Imbeault <jc(at)mega-bucks(dot)co(dot)jp> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | PL/PGSQL question |
Date: | 2003-01-17 06:09:32 |
Message-ID: | 3E279E1C.9060309@mega-bucks.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2003-01-17 06:20:48 | Re: PL/PGSQL question |
Previous Message | trainee12 | 2003-01-17 03:29:30 | Re: varchar and spaces problem.. |