Received: from smtp0.mindspring.com (smtp0.mindspring.com [207.69.200.30]) by hub.org (8.9.2/8.9.1) with ESMTP id QAA41071 for ; Mon, 29 Mar 1999 16:38:05 -0500 (EST) (envelope-from clark.evans@manhattanproject.com) Received: from manhattanproject.com (pool-209-138-0-147.nwrk.grid.net [209.138.0.147]) by smtp0.mindspring.com (8.8.5/8.8.5) with ESMTP id QAA13130; Mon, 29 Mar 1999 16:37:59 -0500 (EST) Sender: clark@mindspring.com Message-ID: <36FFF1D0.FB1986CA@manhattanproject.com> Date: Mon, 29 Mar 1999 21:34:08 +0000 From: Clark Evans X-Mailer: Mozilla 4.51 [en] (X11; I; Linux 2.0.36 i686) X-Accept-Language: en MIME-Version: 1.0 To: Eduardo Noeda CC: pgsql-sql@postgreSQL.org Subject: Re: [SQL] IIF.. References: <199903291249190210.00CEB3B9@mail.virtualsw.es> Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit 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' );