PL/pgSQL中查询自定义数据类型数组对象返回null的问题排查

先创建自定义数据类型compfoo：

CREATE TYPE compfoo AS (f1 int, f2 text);

接着创建表foo，包含fooid和fooname两列（对应compfoo的字段），并插入记录(1, 'aa')、(2, 'bb')、(3, 'cc')。

随后定义了如下PL/pgSQL函数：

create or replace function foo_query()
returns text
language plpgsql
as $$
    declare
      r compfoo;
      arr compfoo [];
      footemp compfoo;
      result text;
    begin
      for r in
        select * from foo where fooid = 1 OR fooid = 2
        loop
        arr := array_append(arr, r);
      end loop;
      foreach footemp in array arr
        loop
        select footemp.f1 into result where footemp.f1 = 1;
      end loop;
    return result;
    end;
$$

函数逻辑为先查询foo表将结果存入compfoo类型数组arr，再遍历数组查询元素的字段。但函数未报错却返回null，请问问题出在哪里？

问题出在遍历数组时的select ... into语句：

• 数组中的元素顺序为(1, 'aa')、(2, 'bb')，第一次遍历到f1=1的元素时，result被赋值为'1'；
• 第二次遍历到f1=2的元素时，select footemp.f1 into result where footemp.f1 = 1因条件不匹配没有返回任何行，而PL/pgSQL中select into语句无匹配行时，会将目标变量result重置为null，最终函数返回这个被覆盖后的null。

推荐两种修正方案：

1. 找到目标元素后直接跳出循环，避免后续操作覆盖结果：

create or replace function foo_query()
returns text
language plpgsql
as $$
    declare
      r compfoo;
      arr compfoo [];
      footemp compfoo;
      result text;
    begin
      for r in
        select * from foo where fooid = 1 OR fooid = 2
        loop
        arr := array_append(arr, r);
      end loop;
      foreach footemp in array arr
        loop
          if footemp.f1 = 1 then
            result := footemp.f1::text;
            exit; -- 找到目标后终止循环
          end if;
        end loop;
    return result;
    end;
$$

2. 简化实现逻辑，无需数组和循环，直接查询目标值：

create or replace function foo_query()
returns text
language sql
as $$
    select fooid::text from foo where fooid = 1;
$$

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