To: pgsql-sql@postgresql.org
Path: not-for-mail
From: Jasen Betts <jasen@xnet.co.nz>
Newsgroups: gmane.comp.db.postgresql.sql
Subject: Re: something simple but I can't
Date: 29 Jan 2011 03:46:37 GMT
Organization: Dis (not Dat) Organisation
Lines: 44
Message-ID: <ii02it$dcm$1@reversiblemaps.ath.cx>
References: <201101281716.04534.johnf@jfcomputer.com>
NNTP-Posting-Host: localhost
Mime-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
User-Agent: slrn/pre1.0.0-18 (Linux)

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' ;

-- 
⚂⚃ 100% natural