simple Postgres UDF with case statement not working-CodePudding

when I run the following code:

create or replace function roundfind(dates date) returns varchar as $$



begin 

select
case 
    when dates between '2020-06-08' and '2020-11-14' and dates is not null then return 'Round 1'
    when dates between '2020-11-15' and '2021-02-17' and dates is not null then return 'Round 2'
    when dates between '2021-02-18' and '2021-04-28' and dates is not null then return 'Round 3'
    when dates between '2021-04-29' and '2021-07-16' and dates is not null then return 'Round 4'
    when dates between '2021-07-16' and '2021-10-03' and dates is not null then return 'Round 5'
    when dates between '2021-10-04' and '2021-11-30' and dates is not null then return 'Round 6'
    when dates between '2021-12-01' and '2022-02-01' and dates is not null then return 'Round 7'
    when dates between '2021-02-02' and '2022-03-28' and dates is not null then return 'Round 8'
    when dates >= '2022-03-29' and dates is not null then return 'Round 9'
end 

end; $$

language PLPGSQL;


postgres.roundfind(date(19-5-2007)) -- function call

there is always some error, the most recent being: "language is not defined". Even though I have defined it clearly.

CodePudding user response：

If you use PL/pgSQL, you need to use RETURN QUERY SELECT ...

But you don't need PL/pgSQL to encapsulate a simple query, use language sql and get rid of the begin ... end

Also the then return should be then

create or replace function roundfind(dates date) returns varchar 
as $$
select
case 
    when dates between '2020-06-08' and '2020-11-14' and dates is not null then 'Round 1'
    when dates between '2020-11-15' and '2021-02-17' and dates is not null then 'Round 2'
    when dates between '2021-02-18' and '2021-04-28' and dates is not null then 'Round 3'
    when dates between '2021-04-29' and '2021-07-16' and dates is not null then 'Round 4'
    when dates between '2021-07-16' and '2021-10-03' and dates is not null then 'Round 5'
    when dates between '2021-10-04' and '2021-11-30' and dates is not null then 'Round 6'
    when dates between '2021-12-01' and '2022-02-01' and dates is not null then 'Round 7'
    when dates between '2021-02-02' and '2022-03-28' and dates is not null then 'Round 8'
    when dates >= '2022-03-29' and dates is not null then 'Round 9'
end;
$$
language sql;

Online example

Note that the and dates is not null conditions in your CASE expression are redundant as between will only return true if the value is not null.