On 2011-01-29, John Fabiani <jo...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
