I am trying to convert my MySQL procedure to pgSQL function that uses rollback and commit. When I did I am getting an error like below,
ERROR: cannot begin/end transactions in PL/pgSQL HINT: Use a BEGIN block with an EXCEPTION clause instead. CONTEXT: PL/pgSQL function fun_profile_update(refcursor,character varying,character varying) line 155 at SQL statement
Please help me out. Here is my function:
Although begin/end transaction cannot be used in PL/pgSQL procedure, You can throw an exception and handle it.
Sample:
CREATE TABLE public.test
(
id serial,
description character(255)
)
WITH (
OIDS=FALSE
);
CREATE OR REPLACE FUNCTION insert_test(IN _description text, IN _rollback boolean DEFAULT false, OUT result integer) RETURNS integer AS $$
BEGIN
INSERT INTO public.test(description) SELECT _description;
IF _rollback THEN
RAISE EXCEPTION 'rollback' USING errcode = '40000'; --code for transaction_rollback
END IF;
result := 1;
EXCEPTION
WHEN transaction_rollback THEN
result := 0;
END;
$$
LANGUAGE PLPGSQL;
