plpgsql - 将每个查询的结果推送到数组中

Question

我创建了函数：

CREATE FUNCTION citiesById(integer[]) RETURNS text[] AS
$$
DECLARE

element int;
result text[];

BEGIN
  FOREACH element IN ARRAY $1
   LOOP
     WITH t1 as (SELECT city FROM cities WHERE id = element)
     SELECT city FROM t1 INTO result;
   END LOOP;

RETURN result;

END

$$
LANGUAGE plpgsql;

我正在尝试在循环中执行查询并将每个查询的结果插入到数组中以获得类似['London', 'Paris', 'Moscow']. 但我收到一个错误：

有正确的方法吗？

标签： plpgsql

Answer 1

CREATE FUNCTION citiesById(integer[]) RETURNS text[] AS
$$
DECLARE

element int;
result text[];

BEGIN
  FOREACH element IN ARRAY $1
   LOOP
     result := array_append(result, (SELECT city FROM cities WHERE id = element)::text);
   END LOOP;

RETURN result;

END

$$
LANGUAGE plpgsql;