Re: PL/PGSQL beginning is hard....

> i am a beginner at SQL and PL/pgsql.... and thus have some surely
> already known problems...
> 
> i have set up some tables, and wanted to play around with inbuild
> functions, and set up the following function:
> 
> CREATE FUNCTION balance (int4) RETURNS int4 AS '
>     DECLARE
>        compte ALIAS FOR $1;
>        actplus accounts.num%TYPE;
>        actminus accounts.num%TYPE;
>        actres accounts.num%TYPE;
>        BEGIN    
>        SELECT SUM(amount) INTO actplus FROM journal WHERE plus=compte;
>        select sum(amount) INTO actminus from journal where minus=compte;
>        actres := actplus - actminus;
>        RETURN actres;
>          END;
>           ' LANGUAGE 'plpgsql';
> 
> 
> Now this works fine, until it hits one of the cases where either of the
> selects returns an empty result (meaning that no line conforming to the
> contraint could be found) in this case even if the other select returns
> a value, the whole function does return nothing....
> 
> what did i wrong, or what do i have to change, to assume the value 0 if
> no hit was found to the select?

Probably this would do it:select coalesce(sum(amount),0) ...


> BTW i am wondering if the same thing could have been achieved with sole
> SQL, and if yes, how....

You might be able to do this with subselects..(select coalesce(sum(amount), 0) from ... ) - (select coalesce...)

So, maybe something like this, if you were say going over a table which
had the compte values:select (select coalesce(sum(amount), 0) from journal where plus=compte)      -(select
coalesce(sum(amount),0) from journal where minus=compte)from table_with_compte_values;