Search Postgresql Archives

Re: IF function?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux