Received: from trends.net (root@clio.trends.ca [209.47.148.2]) by hub.org (8.9.3/8.9.3) with ESMTP id RAA01556; Mon, 14 Jun 1999 17:37:26 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [206.210.65.6]) by trends.net (8.8.8/8.8.8) with ESMTP id RAA02663; Mon, 14 Jun 1999 17:22:32 -0400 (EDT) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id RAA21116; Mon, 14 Jun 1999 17:20:40 -0400 (EDT) To: Michael J Davis cc: pgsql-general@postgresql.org, "'pgqsl-interfaces@postgreSQL.org'" , "'pgsql-sql@postgreSQL.org'" Subject: Re: [SQL] RE: [GENERAL] Two variable passed to PL/Function and on is NULL In-reply-to: Your message of Mon, 14 Jun 1999 14:03:48 -0500 <93C04F1F5173D211A27900105AA8FCFC1455CD@lambic.prevuenet.com> Date: Mon, 14 Jun 1999 17:20:38 -0400 Message-ID: <21113.929395238@sss.pgh.pa.us> From: Tom Lane Michael J Davis writes: > Create an nz(int4) or nvl(int4) function that returns 0 if $1 is null. > You could also create nz(text) that returns '' when $1 is null. Also, you can use the COALESCE() standard function to handle this sort of thing: COALESCE(a,b,...) basically returns the first non-null value in its argument list. So COALESCE(x,0) or COALESCE(x,'') would handle the above requirements. COALESCE is a shorthand form of a CASE expression, which allows for even more general if-then-else calculations. CASE is present but a tad buggy in 6.4 (IIRC, it fails if used in a SELECT that joins more than one table). There are no known problems with it in 6.5. regards, tom lane