pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedIIF..
5+ messages / 3 participants
[nested] [flat]
* IIF..
@ 1999-03-29 10:49 Eduardo Noeda <enoeda@virtualsw.es>
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 19:20 Jackson, DeJuan <djackson@cpsgroup.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 21:34 Clark Evans <clark.evans@manhattanproject.com>
parent: Eduardo Noeda <enoeda@virtualsw.es>
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-30 00:01 Clark Evans <clark.evans@manhattanproject.com>
parent: Jackson, DeJuan <djackson@cpsgroup.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