Thread: 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
IF ( condition here)
-- to do
ELSE
-- to
END IF
-- to do
ELSE
-- to
END IF
On 5/1/07, novnov <novnovice@gmail.com> 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")
--
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.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
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