CODEWITHSUNDEEP
postgresql
L.Nelson
L.Nelson

Reputation: 63

PgSQL functions with rollback and commit

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:

Upvotes: 0

Views: 3182

Answers (1)

Kamil Kukielka
Kamil Kukielka

Reputation: 51

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;

Upvotes: 1

Related Questions