在PL/pgSQL函数中调用返回数组的函数时始终返回NULL的问题

问题背景 #

实现了SQL函数public.string_parse_alias_filename，接收text类型参数，返回text[]数组，单独传入:foo:bar格式参数时可正常返回对应数组；但在PL/pgSQL函数public.tablelist_id_from_name中调用该函数并赋值给数组变量tfile时，传入:myalias:myfilename始终得到NULL。

相关函数代码：

待调用的SQL函数 #

CREATE OR REPLACE FUNCTION public.string_parse_alias_filename(
_input text DEFAULT NULL::text)
RETURNS text[]
LANGUAGE 'sql'

-- given ':myalias:myfilename'  returns ('myalias','myfilename')

AS $BODY$
select * from REGEXP_MATCHES(_input,'^:([[:alnum:]]+):([[:alnum:]]+)(?:$|\.[[:alnum:]]+)?$')
$BODY$;

调用它的PL/pgSQL函数 #

CREATE OR REPLACE FUNCTION public.tablelist_id_from_name(
_myname text)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
tfile text[];
BEGIN
select   string_parse_alias_filename(_myname) INTO tfile; --always returns NULL
END
$BODY$;

问题原因 #

REGEXP_MATCHES函数默认返回行集（表），每行是一个text[]数组。单独调用SQL函数时，PostgreSQL会隐式将单行结果转换为单个数组值；但在PL/pgSQL的SELECT ... INTO语句中，这种隐式转换不生效，无法将行集直接赋值给数组变量，导致变量为NULL。

解决方案 #

有两种可行的解决方式：

方式1：修改原SQL函数，返回单个数组值 #

将原函数中返回行集的逻辑改为直接返回单个数组，确保函数输出是单个text[]值而非行集：

CREATE OR REPLACE FUNCTION public.string_parse_alias_filename(
_input text DEFAULT NULL::text)
RETURNS text[]
LANGUAGE 'sql'
-- given ':myalias:myfilename'  returns ('myalias','myfilename')
AS $BODY$
-- 直接提取正则匹配结果的数组
SELECT (REGEXP_MATCHES(_input,'^:([[:alnum:]]+):([[:alnum:]]+)(?:$|\.[[:alnum:]]+)?$'))[1:2];
-- 或者用array()包裹行集转换为单个数组
-- SELECT array(SELECT * FROM REGEXP_MATCHES(_input,'^:([[:alnum:]]+):([[:alnum:]]+)(?:$|\.[[:alnum:]]+)?$'));
$BODY$;

方式2：在PL/pgSQL函数中处理行集 #

如果不想修改原SQL函数，可在调用时将行集转换为单个数组变量：

CREATE OR REPLACE FUNCTION public.tablelist_id_from_name(
_myname text)
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
tfile text[];
BEGIN
-- 方法A：使用array_agg聚合行集为数组
SELECT array_agg(elem) INTO tfile FROM string_parse_alias_filename(_myname) AS elem;

-- 方法B：直接取单行的数组值（适用于正则匹配只会返回一行的场景）
-- SELECT elem INTO tfile FROM string_parse_alias_filename(_myname) AS elem;
END
$BODY$;

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