When an exception occurs while calling a procedure, are the previous operations in it rolled back in PostgreSQL? In MySQL, in order for ROLLBACK to occur I had to add a HANDLER FOR SQLEXCEPTION. For example:
DELIMITER //
CREATE PROCEDURE insert_address (IN region VARCHAR(25), city VARCHAR(50), street VARCHAR(50), OUT last_id INT)
BEGIN
DECLARE region_id INT;
DECLARE city_id INT;
DECLARE errno INT;
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SHOW ERRORS;
ROLLBACK;
END;
START TRANSACTION;
SET region_id = (SELECT r.id FROM region AS r WHERE r.name = region LIMIT 1);
IF NOT EXISTS (SELECT c.id FROM city AS c WHERE c.name = city LIMIT 1) THEN
CALL insert_city(city, city_id);
ELSE
SET city_id = (SELECT c.id FROM city AS c LIMIT 1);
END IF;
INSERT INTO address(region_id, city_id, street)
VALUES(region_id, city_id, street);
SET last_id = LAST_INSERT_ID();
COMMIT;
END
I'm wondering how this works in PostgreSQL, does it happen automatically or do I also have to add some commands.