PostgreSQL自定义函数循环存查询结果入数组报错，求正确实现方法

嘿，我来帮你搞定这个问题～你的函数思路没问题，但在数组赋值的环节踩了坑，咱们一步步修正：

原函数的问题 #

你原来的代码里，每次循环用SELECT city FROM t1 INTO result，这会把单个字符串直接赋值给数组类型的result变量——不仅会覆盖之前循环的结果，还会因为类型不匹配触发错误。而且用FOREACH循环加WITH子句的写法有点冗余，PostgreSQL有更简洁高效的实现方式。

方法1：修正循环版本（PL/pgSQL） #

如果坚持用循环实现，需要初始化空数组，并用array_append来逐个追加元素，而不是直接赋值：

CREATE FUNCTION citiesById(integer[]) RETURNS text[] AS $$
DECLARE
  element int;
  result text[] := '{}'::text[]; -- 先初始化一个空数组
BEGIN
  FOREACH element IN ARRAY $1 LOOP
    -- 用array_append把新城市追加到数组末尾，再赋值回result
    SELECT array_append(result, city) INTO result
    FROM cities WHERE id = element;
  END LOOP;
  RETURN result;
END $$ LANGUAGE plpgsql;

方法2：更高效的集合操作（推荐，SQL函数） #

PostgreSQL天生擅长集合运算，用SQL函数实现比PL/pgSQL循环高效得多，还能严格保留输入数组的顺序：

CREATE FUNCTION citiesById(integer[]) RETURNS text[] AS $$
  SELECT array_agg(c.city ORDER BY t.idx)
  FROM unnest($1) WITH ORDINALITY AS t(id, idx)
  JOIN cities c ON c.id = t.id;
$$ LANGUAGE sql STABLE;

补充：处理不存在的ID #

如果输入的ID可能在cities表中不存在，上面的写法会自动跳过这些ID。如果想在对应位置保留null（比如输入[1,999,3]，999不存在时返回['London', null, 'Moscow']），可以改成LEFT JOIN：

CREATE FUNCTION citiesById(integer[]) RETURNS text[] AS $$
  SELECT array_agg(c.city ORDER BY t.idx)
  FROM unnest($1) WITH ORDINALITY AS t(id, idx)
  LEFT JOIN cities c ON c.id = t.id;
$$ LANGUAGE sql STABLE;

内容的提问来源于stack exchange，提问作者ekhodzitsky