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
Re: Questions about my ifnull function |
Список | 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 по дате отправления: