| 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: | Whole Thread | Raw Message | 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.. |