如何在PL/pgSQL中移除记录数组中的空元素？

问题根源 #

当目标表没有外键依赖时，array_agg() 会返回 NULL 而非空数组，这会导致你遍历数组时遇到空元素错误。

修复方案 #

修改函数，使用 coalesce() 将 NULL 替换为空数组，同时确保复合类型字段的类型匹配：

create or replace function get_fk(tblnm character varying) returns fkData[] language 'plpgsql'
as $BODY$
declare cons fkData[];
with fk as (
    select 
        conrelid::regclass::varchar as table_name,  -- 将oid转换为表名字符串，匹配复合类型字段
        conname as foreign_key, 
        pg_get_constraintdef(oid) as condef
    from pg_constraint
    where contype = 'f'
      and connamespace = 'public'::regnamespace
      and conname like concat('%',tblnm,'%')
    order by conrelid::regclass, contype DESC
)
select coalesce(array_agg(row(table_name, foreign_key, condef)::fkData), '{}'::fkData[]) into cons
from fk;
return cons;
end;
$BODY$;

简化版（SQL函数） #

如果不需要PL/pgSQL的额外逻辑，也可以用更简洁的SQL函数实现：

create or replace function get_fk(tblnm character varying) returns fkData[] language 'sql'
as $BODY$
with fk as (
    select 
        conrelid::regclass::varchar as table_name,
        conname as foreign_key, 
        pg_get_constraintdef(oid) as condef
    from pg_constraint
    where contype = 'f'
      and connamespace = 'public'::regnamespace
      and conname like concat('%',tblnm,'%')
    order by conrelid::regclass, contype DESC
)
select coalesce(array_agg(row(table_name, foreign_key, condef)::fkData), '{}'::fkData[])
from fk;
$BODY$;

关键说明 #

1. 类型匹配：将 conrelid（OID类型）转换为 regclass 再转 varchar，确保与复合类型 fkData 的第一个字段类型一致，避免隐式转换问题。
2. 空数组替换：coalesce(array_agg(...), '{}'::fkData[]) 确保当没有外键数据时，函数返回空数组而非 NULL，这样遍历数组时不会出现空元素。

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