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