By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

Questions about my ifnull function

P: n/a
Having a requirement to change null into a certain value in a query I
created a couple versions of an ifnull function as follows:

create or replace function "ifnull" (text, text) returns text as '
begin
if $1 is null
then
return $2;
else
return $1;
end if;
end;' language 'plpgsql';

create or replace function "ifnull2" (text, text) returns text as '
select case when $1 is null then $2 else $1 end;
' language 'sql';

The functions work fine but I have some questions:

1. Did I overlook a better builtin function?

2. Is there a good reason to prefer one over the other (ifnull2 seems
marginally faster)?

3. I had planned to overload the function to work with other datatypes
- ifnull(int, int) etc. but found that although my functions specify
text they seem to work correctly with some other data types (like int
and numeric) but not with others (inet) as shown below. Why isn't an
error generated when the wrong data types are passed? Examples:

steve=# select ifnull(null,'foo');
ifnull
--------
foo

steve=# select ifnull(null,5::int);
ifnull
--------
5
steve=# select ifnull(3::int, 'foo');
ifnull
--------
3

steve=# select ifnull(null,'10.0.0.1'::inet);
ERROR: Function ifnull("unknown", inet) does not exist
Unable to identify a function that satisfies the given
argument types
You may need to add explicit typecasts

Cheers,
Steve
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 11 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
On Tue, 23 Sep 2003, Steve Crawford wrote:
Having a requirement to change null into a certain value in a query I
created a couple versions of an ifnull function as follows:

create or replace function "ifnull" (text, text) returns text as '
begin
if $1 is null
then
return $2;
else
return $1;
end if;
end;' language 'plpgsql';

create or replace function "ifnull2" (text, text) returns text as '
select case when $1 is null then $2 else $1 end;
' language 'sql';

The functions work fine but I have some questions:

1. Did I overlook a better builtin function?
coalesce

3. You get your function called sometimes without error for other data types
because of implicit casting to text type.

steve=# select ifnull(null,5::int);
ifnull
--------
5


I could have sworn int to text wasn't an implicit cast now. Damn memory.
--
Nigel J. Andrews
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 11 '05 #2

P: n/a
On Tue, Sep 23, 2003 at 12:58:03 -0700,
Steve Crawford <sc*******@pinpointresearch.com> wrote:
Having a requirement to change null into a certain value in a query I
created a couple versions of an ifnull function as follows:

1. Did I overlook a better builtin function?


COALESCE is the standard way to do this.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 11 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.