PL/PGSQL question 
Author Message
 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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster



Tue, 05 Jul 2005 14:12:14 GMT
 PL/PGSQL question

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Tue, 05 Jul 2005 14:21:08 GMT
 PL/PGSQL question

Quote:

> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Tue, 05 Jul 2005 14:24:25 GMT
 PL/PGSQL question
Quote:
> Date: Fri, 17 Jan 2003 15:09:32 +0900

> 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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate

message can get through to the mailing list cleanly



Tue, 05 Jul 2005 16:19:28 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. PL/pgSQL question

2. PL/pgSQL Question

3. PL/pgSQL Question

4. pl/pgsql Composite Parameter Question

5. Performance of c, pl/perl, pl/pgsql

6. pgsql/contrib/intarray/bench (bench.pl create_test.pl)

7. pgsql/src/pl/plperl Makefile.PL

8. pgsql/ ontrib/rtree_gist/rtree_gist.c rc/pl/pl ...

9. pgsql-server/src/pl/plpython error.expected pl ...

10. pgsql/src/pl/plpgsql/src Tag: REL7_2_STABLE pl ...

11. pgsql/src/pl/plpgsql/src Tag: REL7_1_STABLE pl ...

12. pgsql/src/pl/plpgsql/src Tag: REL7_1_STABLE pl ...


 
Powered by phpBB® Forum Software