當我運行以下代碼時：
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
總是有一些錯誤，最近的是：“語言未定義”。盡管我已經明確地定義了它。
uj5u.com熱心網友回復：
如果你使用 PL/pgSQL，你需要使用RETURN QUERY SELECT ...
但是你不需要 PL/pgSQL 來封裝一個簡單的查詢，使用language sql和擺脫begin ... end
也then return應該是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;
在線示例
請注意，and dates is not null您的 CASE運算式中的條件是多余的，因為只有當值不為空between時才會回傳。true
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454912.html
標籤：PostgreSQL 案子 用户定义函数
下一篇：使用函式的引數來呼叫