PL/PGSQL question

Jean-Christian Imbeault <jc@mega-bucks.co.jp> writes:

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.

SUM() over no rows returns NULL, not zero, per the SQL spec.
(Yes, it's a stupid spec.)

Your IF NOT FOUND test can never succeed, because the select will always
return exactly one row no matter what. Try testing total_sales for NULL
instead.

regards, tom lane

Tom Lane wrote:

SUM() over no rows returns NULL, not zero, per the SQL spec.

I knew that, but didn't see the implications ... i.e. "IF FOUND" always
returns true ...

Try testing total_sales for NULL instead.

I was thinking of that but I declared it of type INTEGER. I thought it
made no sense to have an integer with a NULL value but it it works, I'm
happy :)

Thanks!

Jc

Date: Fri, 17 Jan 2003 15:09:32 +0900
From: Jean-Christian Imbeault <jc@mega-bucks.co.jp>

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.

IF NOT FOUND THEN
RETURN 0;
END IF;
RETURN total_sales;

I am not sure how close PL/pgSQL is to the "Persistent Stored Modules" (PSM)
of the SQL3 standard. If it is quite close it has inherited an insane feature
of PSM: "return" does *not* end the function, but only sets the return value.

Check whether the following code works:

IF NOT FOUND THEN
RETURN 0;
ELSE
RETURN total_sales;
END IF;

Christoph Dalitz

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.