PL/PGSQL question

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

Responses

Browse pgsql-general by date

  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..