postgresql - 错误：在 WHERE 数组中不允许设置返回函数

Question

这是从参数中获取 sub_arr 并将 sub_arr 转换为 UUID 并找到其 id 的函数。并添加了student_subjects表中id不存在的时间戳

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 (
                 cast(json_array_elements(subjects_coll -> 
                'sub_arr') as uuid)))

                RETURNING id as ids, guid as student_subjects_guids,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;

传递主题 id 和主题 guid 的函数参数

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

错误

ERROR:  set-returning functions are not allowed in WHERE
LINE 2: ...t in (select id from subjects where guid in (cast(json_array...
                                                             ^

标签： postgresql

Answer 1

subjects_coll将参数的类型更改为jsonb，则可以使用 JSON 包含运算符@>。此外，它可以帮助将NOT IN变成NOT EXISTS：

UPDATE student_subjects
SET deleted = now()
WHERE student_id = std_id
  AND NOT EXISTS (SELECT i FROM subjects
                  WHERE student_subjacts.subject_id = id
                    AND subjects_coll
                        @> jsonb_build_object(
                              'sub_arr',
                              jsonb_build_array(guid)
                           )
                 )
RETURNING id as ids,
          guid as student_subjects_guids,
          student_id as student_ids,
          subject_id as subject_ids,
          modified as edited,
          deleted as deletes;