0

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.

nietoperz21
  • 303
  • 3
  • 12
  • Does this answer your question? [Are PostgreSQL functions transactional?](https://stackoverflow.com/questions/12778209/are-postgresql-functions-transactional) – rveerd Nov 19 '22 at 15:38

0 Answers0