Received: from erm1.u-strasbg.fr (erm1.u-strasbg.fr [130.79.74.61]) by mail.postgresql.org (8.11.1/8.11.1) with SMTP id eA2Negl58496 for ; Thu, 2 Nov 2000 18:40:42 -0500 (EST) (envelope-from bboett@erm1.u-strasbg.fr) Received: (qmail 22612 invoked by uid 501); 2 Nov 2000 23:58:23 -0000 Date: Fri, 3 Nov 2000 00:58:23 +0100 From: Bruno Boettcher To: pgsql-sql@postgresql.org Subject: PL/PGSQL beginning is hard.... Message-ID: <20001103005823.G14044@erm1.u-strasbg.fr> Reply-To: bboett@erm1.u-strasbg.fr Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0.1i X-Archive-Number: 200011/17 X-Sequence-Number: 48 hello, 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? BTW i am wondering if the same thing could have been achieved with sole SQL, and if yes, how.... -- ciao bboett ============================================================== bboett@earthling.net http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett =============================================================== the total amount of intelligence on earth is constant. human population is growing....