Re: feature request ?

On Jun 24, 2004, at 1:49 PM, sad wrote:

> On Thursday 24 June 2004 09:32, Michael Glaesemann wrote:
>> Creating a new control structure to do handle this seems odd. However,
>> one could easily have the same effect using a nested if. Using the
>> pl/pgsql ELSIF construct, it's pretty straightforward.
>>
>> IF foo IS NULL
>>     THEN ...
>> ELSIF foo
>>     THEN ...
>> ELSE ...
>> END IF;
>
> here the foo expression will be executed twice

You're right, in that you couldn't use this in a CASE expression in 
pure SQL, but it would work in a pl/pgsql function, which would execute 
the expression once when it is called. Here's a very simple example:

test=# create or replace function foo_3val(boolean)    returns text    language plpgsql as '    declare        foo
aliasfor $1;    begin        if foo is null            then return ''foo is null'';        elsif foo            then
return''foo is true'';        else            return ''foo is false'';        end if;    end;    ';
 
CREATE FUNCTION
test=# create table foo_vals (foo_id serial unique not null, foo_val 
boolean);
NOTICE:  CREATE TABLE will create implicit sequence 
"foo_vals_foo_id_seq" for "serial" column "foo_vals.foo_id"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 
"foo_vals_foo_id_key" for table "foo_vals"
CREATE TABLE
test=# insert into foo_vals (foo_val) values (true);
INSERT 5076542 1
test=# insert into foo_vals (foo_val) values (false);
INSERT 5076543 1
test=# insert into foo_vals(foo_id) values(default);
INSERT 5076544 1
test=# select * from foo_vals; foo_id | foo_val
--------+---------      1 | t      2 | f      3 |
(3 rows)

test=# select foo_id, foo_3val(foo_val) from foo_vals; foo_id |   foo_3val
--------+--------------      1 | foo is true      2 | foo is false      3 | foo is null
(3 rows)

Michael Glaesemann
grzm myrealbox com