PostgreSQL自定义函数报错set-returning functions are not allowed in WHERE如何解决

错误产生原因 #

json_array_elements属于集合返回函数（SRF），执行后会输出多行结果。PostgreSQL语法规范明确禁止在WHERE子句的IN条件列表中直接使用集合返回函数，必须将这类函数放到FROM子句中作为临时结果集引用后再使用。
另外原代码还存在一处隐性问题：json_array_elements返回的是json类型的值，直接强制转换为uuid会因为保留了JSON字符串自带的引号导致转换失败，需要先提取为文本类型再做转换。

修复后完整代码 #

CREATE OR REPLACE FUNCTION public.add_subjects(
    std_ids uuid,
    subjects_coll json)
    RETURNS TABLE(ids integer, student_subjects_guid uuid, student_ids integer, subject_ids integer, edited timestamp without time zone, deletes timestamp without time zone) 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000

AS $BODY$
declare 
    std_id integer;
begin
    select id
    into std_id 
    from students
    where guid = std_ids;
    
    if (std_id is not null) then
        RETURN QUERY
        update student_subjects 
        set deleted = now() 
        where student_id = std_id 
        AND subject_id not in (
            select id from subjects
            where guid in (
                -- 将集合返回函数放到FROM子句中，先转文本再转UUID
                select elem::uuid 
                from json_array_elements_text(subjects_coll -> 'sub_arr') as elem
            )
        )
        RETURNING 
            id as ids, 
            guid as student_subjects_guid, -- 修正字段别名与返回定义对齐
            student_id as student_ids,
            subject_id as subject_ids,
            modified as edited, 
            deleted as deletes;
    end if; 
end;
$BODY$;

ALTER FUNCTION public.add_subjects(uuid, json)
    OWNER TO postgres;

调用示例 #

SELECT * FROM add_subjects(
    'e1ea3e2a-9521-410e-9d76-0627e1ee9e2d',
    '{"sub_arr":["f0952a26-49ad-467f-96ad-6566a19a8b46","eb9a7050-3ea7-428d-b5af-0b7622fae316"]}'
);

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