Coalesce is a great function. It enables you to
code up IF X IS NOT NULL THEN X ELSE Y END in one
easy function. There are coalesce functions for
most datatypes and you can write your own for any
other types.
This will do what you want.
... select into total_sales coalesce( sum(price), 0) ...
elein@varlena.com
On Thursday 16 January 2003 22:09, Jean-Christian Imbeault wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
----------------------------------------------------------------------------------------
elein@varlena.com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.