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 alias for $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