PostgreSQL: Nested CASE conditional expression in function?

Question

How to use nested CASE conditional expression in function using PostgreSQL?

For example the following function is created to calculate two numbers:

create or replace function fun(n integer) returns void as
$body$
declare
   a int :=10;
   b int :=5;
   addition int :=0;
   subs int :=0;

begin
   select n,
   case when n=1 then
   addition:=a+b;
   raise info '%',addition;

   case when n=2 then
   subs:=a-b;
   raise info '%',subs;

   end
end;
$body$
language plpgsql;

--Calling function

select fun(1);

Clodoaldo Neto · Accepted Answer · 2014-03-31 11:45:46Z

1

You can't have a command inside the case. Use the case as a parameter to raise.

create or replace function fun(n integer) returns void as
$body$
declare
    a int := 10;
    b int := 5;

begin
    raise info '%', case n
        when 1 then a + b
        else a - b
        end
    ;
end;
$body$
language plpgsql;

select fun(1);
INFO:  15
 fun 
-----

(1 row)

select fun(2);
INFO:  5
 fun 
-----

answered Mar 31, 2014 at 11:45

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Nested CASE conditional expression in function?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related