Обсуждение: IF function?

Does postgresql have a built in IF function that allows one to eval a
condition and return one or another value? Like:

IIF(mybooleanfield = true, "It's true", "It's not true")


--
View this message in context: http://www.nabble.com/IF-function--tf3673523.html#a10264910
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Mon, Apr 30, 2007 at 10:51:36PM -0700, novnov wrote:
>
> Does postgresql have a built in IF function that allows one to eval a
> condition and return one or another value? Like:
>
> IIF(mybooleanfield = true, "It's true", "It's not true")

It has CASE, as in

CASE foo WHEN true THEN 'It''s true' ELSE 'It''s not true' END;

Cheers,
D
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate

David Fetter wrote:
>
> On Mon, Apr 30, 2007 at 10:51:36PM -0700, novnov wrote:
>>
>> Does postgresql have a built in IF function that allows one to eval a
>> condition and return one or another value? Like:
>>
>> IIF(mybooleanfield = true, "It's true", "It's not true")
>
> It has CASE, as in
>
> CASE foo WHEN true THEN 'It''s true' ELSE 'It''s not true' END;
>
> Cheers,
> D
> --
> David Fetter <david@fetter.org> http://fetter.org/
> phone: +1 415 235 3778        AIM: dfetter666
>                               Skype: davidfetter
>

I didn't express myself clearly. Is there an IIF type function one can use
inline in a view?

SELECT UserName, IIF(mybooleanfield = true, "It's true", "It's not true") as
SayIt FROM tblUser

I don't think there is, and I don't 'need' it, just could be handy once in a
while.

--
View this message in context: http://www.nabble.com/IF-function--tf3673523.html#a10270049
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On 01/05/2007 15:38, novnov wrote:

> I didn't express myself clearly. Is there an IIF type function one can use
> inline in a view?
>
> SELECT UserName, IIF(mybooleanfield = true, "It's true", "It's not true") as
> SayIt FROM tblUser

That's what CASE is for:

select
   case
     when mybooleanfield then 'It''s true'
     else 'It''s not true'
   end as SayIt
from
   tblUser;

(...or something like that - look it up in the docs).

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
---------------------------------------------------------------

On May 1, 2007, at 12:57 AM, David Fetter wrote:

> On Mon, Apr 30, 2007 at 10:51:36PM -0700, novnov wrote:
>>
>> Does postgresql have a built in IF function that allows one to eval a
>> condition and return one or another value? Like:
>>
>> IIF(mybooleanfield = true, "It's true", "It's not true")
>
> It has CASE, as in
>
> CASE foo WHEN true THEN 'It''s true' ELSE 'It''s not true' END;
>

Or, for simple binary conditions like that:

CASE WHEN foo is TRUE
   THEN "it's true"
   ELSE "it's not true"
END

erik jones <erik@myemma.com>
software developer
615-296-0838
emma(r)

On Tue, 2007-05-01 at 07:38 -0700, novnov wrote:
> I didn't express myself clearly. Is there an IIF type function one can
> use
> inline in a view?
>
> SELECT UserName, IIF(mybooleanfield = true, "It's true", "It's not
> true") as
> SayIt FROM tblUser

I think people understood you. The part that may not be obvious to you
is that case can be used in the select list, as in:
SELECT UserName, CASE mybool WHEN TRUE THEN $$It's true$$ ELSE $$It's
not true$$ END AS veracity FROM sometable;

or even
SELECT UserName, $$It's$$ || CASE mybool WHEN TRUE THEN '' ELSE ' not'
  END || ' true' AS veracity FROM sometable;

Nearly anything you can express in a select statement can also be turned
into a view. (The only exception that comes to mind is that select
allows cols with the same name, but views don't.)

If you really want a function, that's not hard to write for the case of
consistent types:
        rkh@csb-dev=> create or replace function iif(boolean,text,text)
        returns text language sql as 'select case $1 when true then $2
        else $3 end';
        CREATE FUNCTION
        Time: 71.242 ms
        rkh@csb-dev=> select iif(true,'yep','nope');
         iif
        -----
         yep
        (1 row)

        Time: 1.468 ms
        rkh@csb-dev=> select iif(false,'yep','nope');
         iif
        ------
         nope
        (1 row)

        rkh@csb-dev=> select $$It's$$ || iif(true,'',' not') || ' true';
         ?column?
        -----------
         It's true
        (1 row)


The function is NOT declared strict: it can return null on null input. I
think that is the right behavior for the boolean, but not for the return
strings.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0

I see...well, that's excellent. Thanks to all who responded.


Reece Hart wrote:
>
> On Tue, 2007-05-01 at 07:38 -0700, novnov wrote:
>> I didn't express myself clearly. Is there an IIF type function one can
>> use
>> inline in a view?
>>
>> SELECT UserName, IIF(mybooleanfield = true, "It's true", "It's not
>> true") as
>> SayIt FROM tblUser
>
> I think people understood you. The part that may not be obvious to you
> is that case can be used in the select list, as in:
> SELECT UserName, CASE mybool WHEN TRUE THEN $$It's true$$ ELSE $$It's
> not true$$ END AS veracity FROM sometable;
>
> or even
> SELECT UserName, $$It's$$ || CASE mybool WHEN TRUE THEN '' ELSE ' not'
>   END || ' true' AS veracity FROM sometable;
>
> Nearly anything you can express in a select statement can also be turned
> into a view. (The only exception that comes to mind is that select
> allows cols with the same name, but views don't.)
>
> If you really want a function, that's not hard to write for the case of
> consistent types:
>         rkh@csb-dev=> create or replace function iif(boolean,text,text)
>         returns text language sql as 'select case $1 when true then $2
>         else $3 end';
>         CREATE FUNCTION
>         Time: 71.242 ms
>         rkh@csb-dev=> select iif(true,'yep','nope');
>          iif
>         -----
>          yep
>         (1 row)
>
>         Time: 1.468 ms
>         rkh@csb-dev=> select iif(false,'yep','nope');
>          iif
>         ------
>          nope
>         (1 row)
>
>         rkh@csb-dev=> select $$It's$$ || iif(true,'',' not') || ' true';
>          ?column?
>         -----------
>          It's true
>         (1 row)
>
>
> The function is NOT declared strict: it can return null on null input. I
> think that is the right behavior for the boolean, but not for the return
> strings.
>
> -Reece
>
> --
> Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
>

--
View this message in context: http://www.nabble.com/IF-function--tf3673523.html#a10277571
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

You must use
CASE var=val2 THEN [true statement] ELSE [false statement] END