Received: from corppo1.uvsg.com (corppo1.uvsg.com [192.40.65.90]) by hub.org (8.9.3/8.9.1) with ESMTP id NAA63400 for ; Tue, 20 Apr 1999 13:17:44 -0400 (EDT) (envelope-from michael.j.davis@tvguide.com) Received: from prevuepo1.prevuenet.com (prevuepo1.uvsg.com [192.40.65.44]) by corppo1.uvsg.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2448.0) id JCBQ5YV1; Tue, 20 Apr 1999 12:17:06 -0500 Received: by prevuepo1.uvsg.com with Internet Mail Service (5.5.2232.9) id ; Tue, 20 Apr 1999 12:20:06 -0500 Message-ID: <93C04F1F5173D211A27900105AA8FCFC1453EE@lambic.prevuenet.com> From: Michael J Davis To: pgsql-sql@postgreSQL.org Subject: RE: [SQL] null values to be replaced by a default value Date: Tue, 20 Apr 1999 12:13:37 -0500 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2232.9) Content-Type: text/plain I would really like to be able to do the same thing!!! The following function seems to work okay: CREATE FUNCTION nz (int4) RETURNS int4 AS ' BEGIN IF ($1 IS NULL) THEN RETURN 0; ELSE RETURN $1; END IF; END; ' LANGUAGE 'plpgsql'; -----Original Message----- From: Nuchanard Chiannilkulchai [SMTP:nuch@valigene.com] Sent: Tuesday, April 20, 1999 9:32 AM To: pgsql-sql@postgreSQL.org Subject: [SQL] null values to be replaced by a default value Hi, To return one value not null, i've created a function : CREATE FUNCTION isnull (int4,int4 ) RETURNS int4 AS ' BEGIN IF ($1 IS NULL) THEN RETURN $2; ELSE RETURN $1; END IF; END; ' LANGUAGE 'plpgsql'; select isnull(my_int,0) as number, sample_id from my_table; but I got number |sample_id ------+--------- | 13 | 15 What should I do to have '0' in the column 'number' instead of NULL ? Thanks, nuch