Questions about my ifnull function

Поиск
Список
Период
Сортировка
От Steve Crawford
Тема Questions about my ifnull function
Дата
Msg-id 200309231258.03939.scrawford@pinpointresearch.com
обсуждение исходный текст
Ответы Re: Questions about my ifnull function  ("Nigel J. Andrews" <nandrews@investsystems.co.uk>)
Re: Questions about my ifnull function  (Bruno Wolff III <bruno@wolff.to>)
Список pgsql-general
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


В списке pgsql-general по дате отправления:

Предыдущее
От: Manfred Koizar
Дата:
Сообщение: Re: How to get the total number of rows returned by query
Следующее
От: "Nigel J. Andrews"
Дата:
Сообщение: Re: Questions about my ifnull function