Good evening,
I want to create a TRANSACTION with the proper isolation level. On that transaction I want to do a double insert and if one fails the other one is aborted.
I have a stored procedure already created:
create or replace function insert_into_answercomments(userid INTEGER, answerid INTEGER, body text)
returns void language plpgsql as $$
DECLARE result INTEGER;
insert into publications(body, userid)
VALUES (body, userid)
returning publications.publicationid AS publicationid INTO result;
insert into comments(publicationid) VALUES (result);
insert into answercomments(commentid, answerid) VALUES (result, answerid);
end $$;
My doubt is if the transaction should be inside the function or if it is a different procedure. How do I create it with a correct isolation level.
Kind Regards
transaction cannot be started/ended inside postgres function. If you want some logic, make it inside function. In your case you don't need anything - if first insert generates exception, transaction aborts. But if you need some sophisticated check, make it right in code, eg
if result > 90 then
insert ...second insert
end if;
to run function in transaction start it outside, eg:
begin;
select * from insert_into_answercomments(1,2);
end;