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
