pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsomething simple but I can't
3+ messages / 2 participants
[nested] [flat]
* something simple but I can't
@ 2011-01-29 01:16 John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 3+ messages in thread
From: John Fabiani @ 2011-01-29 01:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: something simple but I can't
@ 2011-01-29 03:46 Jasen Betts <jasen@xnet.co.nz>
parent: John Fabiani <johnf@jfcomputer.com>
0 siblings, 1 reply; 3+ messages in thread
From: Jasen Betts @ 2011-01-29 03:46 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: something simple but I can't
@ 2011-01-29 15:14 John Fabiani <johnf@jfcomputer.com>
parent: Jasen Betts <jasen@xnet.co.nz>
0 siblings, 0 replies; 3+ messages in thread
From: John Fabiani @ 2011-01-29 15:14 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2011-01-29 15:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-01-29 01:16 something simple but I can't John Fabiani <johnf@jfcomputer.com>
2011-01-29 03:46 ` Jasen Betts <jasen@xnet.co.nz>
2011-01-29 15:14 ` John Fabiani <johnf@jfcomputer.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox