Re: Needed function IF(expr, expr, expr)
От | Greg Stark |
---|---|
Тема | Re: Needed function IF(expr, expr, expr) |
Дата | |
Msg-id | 87wucl7ml0.fsf@stark.dyndns.tv обсуждение исходный текст |
Ответ на | Needed function IF(expr, expr, expr) ("Marek Lewczuk" <newsy@lewczuk.com>) |
Ответы |
Re: Needed function IF(expr, expr, expr)
|
Список | pgsql-general |
"Marek Lewczuk" <newsy@lewczuk.com> writes: > Hello, > I'm moving out from MySQL to PostgreSQL and there are some function > which are not supported in PG so I'm trying to write my own functions. > Currently I have big problem with function IF(), below the description > of this function from MySQL manual. > > Anybody can help me with this ?? I think that PLPGSQL language can be > used or maybe other (plPerl) etc. Well I can't really make heads or tails of the part of the manual discussion NULL handling. It's possible to emulate this with straight SQL functions. SQL functions have the advantage that they can be inlined directly into your query and potentially optimized to use indexes etc. I don't think 7.3 will do so, but 7.4 definitely will. I doubt case expressions leave much room for optimizations though. In 7.3 you would have to define an argument for every data type you want to be able to handle. From the sounds of the manual just integer and varchar (or text) ought to be enough. Perhaps you would want a floating point version and/or a numeric version. For 7.4 you'll be able to define it something like this: (I'm not sure why "anyelement" works but "any" doesn't work.) slo=> create or replace function iff(boolean,"anyelement","anyelement") returns "anyelement" language sql strict immutableas 'select case when $1 then $2 else $3 end' ; CREATE FUNCTION slo=> select iff(true,1,2); iff ----- 1 (1 row) slo=> select iff(false,1,2); iff ----- 2 (1 row) That still doesn't let you use an integer argument for the first argument. To define it for integers you would have to do something like: slo=> create or replace function iff(integer,"anyelement","anyelement") returns "anyelement" language sql strict immutableas 'select case when $1<>0 then $2 else $3 end' ; CREATE FUNCTION slo=> select iff(1,1,2); iff ----- 1 (1 row) slo=> select iff(0,1,2); iff ----- 2 (1 row) Postgres will never automatically convert from a float to an integer throwing away precision like you describe, so you would have to do something like this to support passing a float as a true/false value: slo=> create or replace function iff(float,"anyelement","anyelement") returns "anyelement" language sql strict immutableas 'select case when $1::integer<>0 then $2 else $3 end' ; CREATE FUNCTION slo=> select iff(0.1,1,2); iff ----- 2 (1 row) I would suggest only defining the forms you actually need. If your code always uses logical expressions for the first argument and never passes integer or floating point values then don't bother with the second two. -- greg
В списке pgsql-general по дате отправления: