Блог

Это функция для получения sub_arr из параметра и приведения sub_arr к UUID и поиска его идентификатора. И добавлена метка времени, где в таблице student_subjects был идентификатор, не существует

 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;
 

Аргумент функции, передающий идентификатор субъекта и идентификатор 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...
                                                             ^
 

Измените тип 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;