pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
IIF..
5+ messages / 3 participants
[nested] [flat]

* IIF..
@ 1999-03-29 10:49 Eduardo Noeda <enoeda@virtualsw.es>
  1999-03-29 21:34 ` Re: [SQL] IIF.. Clark Evans <clark.evans@manhattanproject.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Eduardo Noeda @ 1999-03-29 10:49 UTC (permalink / raw)
  To: pgsql-sql

Hi!

I'm new in the list (and in PostgreSQL too). I'd like to know if it's posible to perform a function like SQL Server's "iif" in a SELECT query. Some like:

SELECT iif (Number=1, "First", "Other") AS Description
FROM Table;

I don't know if I've explained properly...
--
Eduardo Noeda  --  Virtual Software
enoeda@virtualsw.es




^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] IIF..
  1999-03-29 10:49 IIF.. Eduardo Noeda <enoeda@virtualsw.es>
@ 1999-03-29 21:34 ` Clark Evans <clark.evans@manhattanproject.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Clark Evans @ 1999-03-29 21:34 UTC (permalink / raw)
  To: Eduardo Noeda <enoeda@virtualsw.es>; +Cc: 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' );



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: [SQL] IIF..
@ 1999-03-29 19:20 Jackson, DeJuan <djackson@cpsgroup.com>
  1999-03-30 00:01 ` Re: [SQL] IIF.. Clark Evans <clark.evans@manhattanproject.com>
  0 siblings, 1 reply; 5+ messages in thread

From: Jackson, DeJuan @ 1999-03-29 19:20 UTC (permalink / raw)
  To: Eduardo Noeda <enoeda@virtualsw.es>; pgsql-sql

It would be:
  SELECT (CASE Number 
           WHEN 1 THEN 'First' 
           ELSE 'Other' 
          END) AS Description
    FROM Table

	-DEJ

> -----Original Message-----
> Hi!
> 
> I'm new in the list (and in PostgreSQL too). I'd like to know 
> if it's posible to perform a function like SQL Server's "iif" 
> in a SELECT query. Some like:
> 
> SELECT iif (Number=1, "First", "Other") AS Description
> FROM Table;
> 
> I don't know if I've explained properly...
> --
> Eduardo Noeda  --  Virtual Software
> enoeda@virtualsw.es



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* Re: [SQL] IIF..
  1999-03-29 19:20 RE: [SQL] IIF.. Jackson, DeJuan <djackson@cpsgroup.com>
@ 1999-03-30 00:01 ` Clark Evans <clark.evans@manhattanproject.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Clark Evans @ 1999-03-30 00:01 UTC (permalink / raw)
  To: Jackson, DeJuan <djackson@cpsgroup.com>; +Cc: Eduardo Noeda <enoeda@virtualsw.es>; pgsql-sql

"Jackson, DeJuan" wrote:
> It would be:
>   SELECT (CASE Number
>            WHEN 1 THEN 'First'
>            ELSE 'Other'
>           END) AS Description
>     FROM Table

Way cool.  A new trick for me.  
This is similar to DECODE, I assume.

However, I tried this test on a snapshot
about a week old, and this is the result:

> Welcome to the POSTGRESQL interactive sql monitor:
>   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> 
>    type \? for help on slash commands
>    type \q to quit
>    type \g or terminate with semicolon to execute query
>  You are currently connected to the database: clark
> 
> clark=> create table test (a text, b int4 );
> CREATE
> clark=> insert into test values ( 'one', 1);
> INSERT 18634 1
> clark=> insert into test values ( 'two', 2);
> INSERT 18635 1
> clark=> insert into test values ( null, null);
> INSERT 18636 
> clark=> select ( case b when 1 then 'first' else 'other' end ) as xx from test;
> ERROR:  copyObject: don't know how to copy 704
> clark=> 
> 

Did this work in earlier versions?  If so, then perhaps this
should be added to a regression test if it isn't already.

Best,

Clark



^ permalink  raw  reply  [nested|flat] 5+ messages in thread

* RE: [SQL] IIF..
@ 1999-03-30 00:31 Jackson, DeJuan <djackson@cpsgroup.com>
  0 siblings, 0 replies; 5+ messages in thread

From: Jackson, DeJuan @ 1999-03-30 00:31 UTC (permalink / raw)
  To: Clark Evans <clark.evans@manhattanproject.com>; jwieck@debis.com; +Cc: Eduardo Noeda <enoeda@virtualsw.es>; pgsql-sql

Try it on current snapshot. Any comment Jan?
	-DEJ
> -----Original Message-----
> From: Clark Evans [mailto:clark.evans@manhattanproject.com]
> Sent: Monday, March 29, 1999 6:01 PM
> To: Jackson, DeJuan
> Cc: Eduardo Noeda; pgsql-sql@postgreSQL.org
> Subject: Re: [SQL] IIF..
> 
> 
> "Jackson, DeJuan" wrote:
> > It would be:
> >   SELECT (CASE Number
> >            WHEN 1 THEN 'First'
> >            ELSE 'Other'
> >           END) AS Description
> >     FROM Table
> 
> Way cool.  A new trick for me.  
> This is similar to DECODE, I assume.
> 
> However, I tried this test on a snapshot
> about a week old, and this is the result:
> 
> > Welcome to the POSTGRESQL interactive sql monitor:
> >   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> > 
> >    type \? for help on slash commands
> >    type \q to quit
> >    type \g or terminate with semicolon to execute query
> >  You are currently connected to the database: clark
> > 
> > clark=> create table test (a text, b int4 );
> > CREATE
> > clark=> insert into test values ( 'one', 1);
> > INSERT 18634 1
> > clark=> insert into test values ( 'two', 2);
> > INSERT 18635 1
> > clark=> insert into test values ( null, null);
> > INSERT 18636 
> > clark=> select ( case b when 1 then 'first' else 'other' 
> end ) as xx from test;
> > ERROR:  copyObject: don't know how to copy 704
> > clark=> 
> > 
> 
> Did this work in earlier versions?  If so, then perhaps this
> should be added to a regression test if it isn't already.
> 
> Best,
> 
> Clark
> 



^ permalink  raw  reply  [nested|flat] 5+ messages in thread


end of thread, other threads:[~1999-03-30 00:31 UTC | newest]

Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-03-29 10:49 IIF.. Eduardo Noeda <enoeda@virtualsw.es>
1999-03-29 21:34 ` Clark Evans <clark.evans@manhattanproject.com>
1999-03-29 19:20 RE: [SQL] IIF.. Jackson, DeJuan <djackson@cpsgroup.com>
1999-03-30 00:01 ` Clark Evans <clark.evans@manhattanproject.com>
1999-03-30 00:31 RE: [SQL] IIF.. Jackson, DeJuan <djackson@cpsgroup.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox