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
---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
