pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
null values to be replaced by a default value
3+ messages / 3 participants
[nested] [flat]

* null values to be replaced by a default value
@ 1999-04-20 15:31  Nuchanard Chiannilkulchai <nuch@valigene.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Nuchanard Chiannilkulchai @ 1999-04-20 15:31 UTC (permalink / raw)
  To: pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* RE: [SQL] null values to be replaced by a default value
@ 1999-04-20 17:13  Michael J Davis <michael.j.davis@tvguide.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Michael J Davis @ 1999-04-20 17:13 UTC (permalink / raw)
  To: pgsql-sql

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

	



^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: [SQL] null values to be replaced by a default value
@ 1999-04-20 17:20  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Nuchanard Chiannilkulchai <nuch@valigene.com>
  0 siblings, 0 replies; 3+ messages in thread

From: Tom Lane @ 1999-04-20 17:20 UTC (permalink / raw)
  To: Nuchanard Chiannilkulchai <nuch@valigene.com>; +Cc: pgsql-sql

Nuchanard Chiannilkulchai <nuch@valigene.com> writes:
>     To return one value not null, i've created a function :

You can't really do that with functions right now --- any null
input to a function causes the result to be null.  (There's
been discussion on the hackers list about improving this,
but it won't happen before 6.6 at the earliest.)

Fortunately, the standard COALESCE operator does what you want
already: COALESCE(a,b) produces the result you were looking for,
and you won't need to make a new copy for every data type either.

Basically, COALESCE takes any number of arguments and returns the
first one that's not NULL.  It's a shorthand for a CASE expression,
(CASE WHEN a IS NOT NULL THEN a WHEN b IS NOT NULL THEN b ...)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~1999-04-20 17:20 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-04-20 15:31 null values to be replaced by a default value Nuchanard Chiannilkulchai <nuch@valigene.com>
1999-04-20 17:20 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-04-20 17:13 RE: [SQL] null values to be replaced by a default value Michael J Davis <michael.j.davis@tvguide.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox