write a sql block which will commit if both updates are successful else it will have to be rolled back

Поиск
Список
Период
Сортировка
От arun chirappurath
Тема write a sql block which will commit if both updates are successful else it will have to be rolled back
Дата
Msg-id CAA23SdvyDZNZxdUH2XBVyncdc245yo4WA7Sz3j3=jhvQbNA45g@mail.gmail.com
обсуждение исходный текст
Ответы Re: write a sql block which will commit if both updates are successful else it will have to be rolled back
Список pgsql-general
Дерево обсуждения
Hi All,

Can someone guide me to "write a sql block which will commit if both updates are successful else it will have to be rolled back"?
would like to explicitly specify both commit and rollback in code..

 I would like to turn off the autocommit then execute the query.

Below is a just a starter ...it doesnt has COMMIT clause.. 

DO $$ 
DECLARE
  emp_id1 INT := 1; -- Assuming employee ID for the first update
  new_salary1 NUMERIC := 1; -- New salary for the first update

  emp_id2 INT := 2; -- Assuming employee ID for the second update
  new_salary2 NUMERIC := 3; -- New salary for the second update
BEGIN
  -- Update Statement 1
  UPDATE employees
  SET salary = new_salary1
  WHERE employee_id = emp_id1;

  -- Update Statement 2
  UPDATE employees
  SET salary = new_salary2
  WHERE employee_id = emp_id2;

  EXCEPTION
    WHEN OTHERS THEN
      -- An error occurred during the update, log the error
      RAISE NOTICE 'Error during updates: %', SQLERRM;

      -- Roll back the transaction
      ROLLBACK;
END $$;

select * from public.employees

Thanks,
Arun

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: Postgresql : pg_temp & pg_toast_temp
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: write a sql block which will commit if both updates are successful else it will have to be rolled back