Re: [SQL] IIF.. - Mailing list pgsql-sql

Eduardo Noeda wrote:
> function like SQL Server's "iif" in a SELECT query. 

Try this...

--------------------------------

--
-- Drop the iff function if it exists
--
DROP FUNCTION iff(bool,text,text);

--
-- Install procedural language, if it is not already
-- installed.  You may have to change the shared library
-- location to match your installation.
--
CREATE FUNCTION plpgsql_call_handler () RETURNS OPAQUE AS
'/opt/pgsql/lib/plpgsql.so' LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'  HANDLER plpgsql_call_handler  LANCOMPILER 'PL/pgSQL'; 
--
-- Create the IFF function
--
create function iff(bool,text,text) RETURNS text AS '  BEGIN     IF $1 = true THEN        RETURN $2;     ELSE
RETURN$3;     END IF;  END;
 
' LANGUAGE 'plpgsql';

--
-- Simple test plan.  
--

SELECT iff( 1 = 1, 'YEP', 'NOPE' );
SELECT iff( 1 = 0, 'YEP', 'NOPE' );

SELECT iff( 'x'::text = 'y'::text , 'YEP', 'NOPE' );
SELECT iff( 'x'::text = 'x'::text , 'YEP', 'NOPE' );