我想创建一个db函数，它将接受一个数字列表并返回一个数字列表。对于传递给函数的列表中的每个项，它应该检查一些条件并将其添加到响应列表中。然而，我不认为我所尝试的方法是正确的。我试着写的基本上是一些伪代码。
CREATE OR REPLACE FUNCTION map_numbers(numbers integer[])
returns integer[]
AS
$BODY$
DECLARE return_list integer[];
FOREACH field IN ARRAY numbers LOOP
CASE
WHEN field = 3 THEN -- add 43 (this was a random thought, but I am basically trying to map a few of the numbers to different values)
END
END LOOP;
RETURN QUERY SELECT * FROM return_list;
$BODY$
LANGUAGE sql VOLATILE
COST 100;
1条答案
按热度按时间3qpi33ja1#
你需要把这个放进一个
IF声明。要将值附加到数组，请使用||```CREATE OR REPLACE FUNCTION map_numbers(numbers integer[])
returns integer[]
AS
$BODY$
DECLARE
return_list integer[] := integer[];
BEGIN
FOREACH field IN ARRAY numbers LOOP
if field = 3 then
return_list := return_list || 43;
elsif field = 15 then
return_list := return_list || 42;
else
return_list := return_list || field;
end if;
END LOOP;
return return_list; --<< no SELECT required, just return the variable
END;
$BODY$
LANGUAGE plpgsql --<< you need PL/pgSQL, not SQL for the above
STABLE;
CREATE OR REPLACE FUNCTION map_numbers(numbers integer[])
returns integer[]
AS
$BODY$
select array_agg(field order by idx)
from (
select case
when field = 3 then 43
when field = 15 then 42
else field
end as field,
idx
from unnest(numbers) with ordinality as t(field, idx)
) x;
$BODY$
LANGUAGE sql
STABLE;