Обсуждение: PL/PGSQL question

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

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.