Question 89697 in PostgreSQL Question posted in
The official documentation can be found here.

How Do I use an Else If case in a Function Block – Postgresql

I am trying to use a function to update a column on a table by creating a function that will do it.

I am trying to create a customer tier based on purchases made by each customer. I know the following code will update my column when I run the command by itself:

update email_campaign_detailed 
set customer_tier = 'standard' 
where num_dvd_rented <=20;

However I want to use a function to do this so I can input the data and have it auto-rank.
this is the code I’m using that isn’t working:

Create or replace function customer_tier(num_dvds_rented bigint)
Returns character varying (50)
Language plpgsql
As
$$
Declare
Begin
case
when num_dvd_rented <=20 then update email_campaign_detailed  set customer_tier ='standard'
when num_dvd_rented >= 21 and <= 39 then update email_campaign_detailed  set customer_tier ='silver'
else update email_campaign_detailed  set customer_tier ='gold'
End case;
end;
$$

I get a syntax error at the second "when" statement.
I cannot for the life of me figure out what I need to do.

Any help or insight is deeply appreciated.

Thanks!

Tags:

2

Answers


  1. 0

    You don’t need a stored procedure when using the case-expression. Because case is an expression, you can just use it in your sql-statement (like any other expression):

    update email_campaign_detailed
set
    customer_tier = case
        when num_dvd_rented <= 20 then 'standard'
        when num_dvd_rented >= 21 and <= 39 then 'silver'
        else 'gold'
    end
where num_dvd_rented <= 20;

    ==== EDIT ===

    Your stored procedure doesn’t work because you can’t have an expression (an update statement) within your when branches.
    I suppose you want to use the num_dvds_rented parameter in the stored procedure for your where statement?

    create or replace function customer_tier(num_dvds_rented bigint)
    language plpgsql
As
$$
declare
begin
    update email_campaign_detailed
    set
        customer_tier = case
            when num_dvd_rented <= 20 then 'standard'
            when num_dvd_rented >= 21 and <= 39 then 'silver'
            else 'gold'
        end
    where num_dvd_rented <= num_dvds_rented;
end;
$$

    Also, your stored procedure doesn’t really return anything — so you don’t need the returns... in the declaration.

    Now, if your goal is to do something like this:

    update email_campaign_detailed
set customer_tier = customer_tier(num_dvd_rented)
where num_dvd_rented <= 20;

    then your procedure might look like this:

    create or replace function customer_tier(num_dvds_rented bigint)
    returns character varying (50)
    language plpgsql
As
$$
declare
begin
    return case
       when num_dvds_rented <= 20 then 'standard'
       when num_dvds_rented >= 21 and <= 39 then 'silver'
       else 'gold'
    end;
end;
$$
    Login or Signup to reply.
  2. 0

    Proof you can have expressions in the CASE branches. You just need to terminate then with a ;.

    CREATE OR REPLACE FUNCTION public.case_test()
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
    int_val integer;
BEGIN
CASE WHEN  1 = 1 THEN
    select 1 into int_val;
ELSE
    select 3 into int_val;
END CASE;
RAISE NOTICE 'Val is %', int_val;
END;
$function$
;

select case_test();
NOTICE:  Val is 1
 case_test 
-----------
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search