Обсуждение: something simple but I can't

Hi guys,
I trying to return a 0.00 from a function it there are no records found else 
return the amount.

create or replace function danmeans_getpayments(text)
returns numeric as 
$BODY$
declare invoice_num ALIAS FOR $1; _paidamt numeric;

BEGIN select sum(aropen_paid) into _paidamt FROM public.aropen where 
aropen_applyto is not null and (aropen_applyto = $1) ;
       IF (FOUND) THEN         RETURN _paidamt ;END IF;     RETURN 0.00 ;       
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE COST 100

But all I get is either a blank or the amount paid.  What am I doing wrong???
Johnf

On 2011-01-29, John Fabiani <johnf@jfcomputer.com> wrote:
> Hi guys,
> I trying to return a 0.00 from a function it there are no records found else 
> return the amount.


>   select sum(aropen_paid) into _paidamt FROM public.aropen where 
> aropen_applyto is not null and (aropen_applyto = $1) ;
>
>         IF (FOUND) THEN
>           RETURN _paidamt ;
>     END IF;
>     
>       RETURN 0.00 ;


> But all I get is either a blank or the amount paid.  What am I doing wrong???
> Johnf
how many rows does the query return when no rows match the where?It returns 1  that looks like ( NULL ).it return 1
row,which is more than zero thus FOUND is TRUE.
 

you can fix your function  by changing the IF to
IF _paidamt IS NOT NULL 


but if you change the sum to 
 coalesce(sum(aropen_paid),0.00) 
you can do the task more simply like this:
create or replace function danmeans_getpayments(text)returns numeric as $BODY$  select coalesce(sum(aropen_paid),0.00)
FROMpublic.aropen where  aropen_applyto is not null and (aropen_applyto = $1) ;$BODY$  LANGUAGE 'sql' ;
 

-- 
⚂⚃ 100% natural

On Friday, January 28, 2011 07:46:37 pm Jasen Betts wrote:
> On 2011-01-29, John Fabiani <johnf@jfcomputer.com> wrote:
> > Hi guys,
> > I trying to return a 0.00 from a function it there are no records found
> > else return the amount.
> > 
> >   select sum(aropen_paid) into _paidamt FROM public.aropen where
> > 
> > aropen_applyto is not null and (aropen_applyto = $1) ;
> > 
> >         IF (FOUND) THEN
> >         
> >           RETURN _paidamt ;
> >     
> >     END IF;
> >     
> >       RETURN 0.00 ;
> > 
> > But all I get is either a blank or the amount paid.  What am I doing
> > wrong??? Johnf
> 
>  how many rows does the query return when no rows match the where?
>  It returns 1  that looks like ( NULL ).
>  it return 1 row, which is more than zero thus FOUND is TRUE.
> 
> you can fix your function  by changing the IF to
> 
>  IF _paidamt IS NOT NULL
> 
> 
> but if you change the sum to
> 
>   coalesce(sum(aropen_paid),0.00)
> 
> you can do the task more simply like this:
> 
>  create or replace function danmeans_getpayments(text)
>  returns numeric as
>  $BODY$
>    select coalesce(sum(aropen_paid),0.00) FROM public.aropen where
>   aropen_applyto is not null and (aropen_applyto = $1) ;
>  $BODY$
>    LANGUAGE 'sql' ;
Thanks to all who replied.  All of your suggestions would work and it should 
have been obvious but it wasn't.

Johnf