Re: IF function?

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(at)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(at)csb-dev=> select iif(true,'yep','nope');
> iif
> -----
> yep
> (1 row)
>
> Time: 1.468 ms
> rkh(at)csb-dev=> select iif(false,'yep','nope');
> iif
> ------
> nope
> (1 row)
>
> rkh(at)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.