PostgreSQL Exception Handling – Inside Function Triggers

functionsplpgsqlpostgresqltrigger

I need to add a trigger after insert or update on a table so that, where some row is updated and a certain condition is met, then some other table needs to be updated. However, that last update statement may throw an error (updating creates a duplicate index on the table), then I need to take a different approach, meaning I need to delete instead of update.

The function, so far, is this:

CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item()
    RETURNS trigger AS $BODY$ 
DECLARE found_group bigint; 
BEGIN 
    SELECT group_id INTO found_group 
      FROM inv_items_group_members 
     WHERE item_id = NEW.item_id; 
     IF found_group IS NOT NULL THEN 
        UPDATE public.prod_profiles_steps_items 
           SET item_id = found_group 
         WHERE item_id = NEW.item_id;

        -- TODO : on error for the last update, this should get executed:
        -- DELETE FROM public.prod_profiles_steps_items 
        -- WHERE item_id = NEW.item_id;

    END IF;

    RETURN NEW;
END; $BODY$

How can this be done? All I read everywhere are RAISE. Which is irrelevant here.

Best Answer

In a PL/pgSQL function (which you are using, but the language declaration is missing), use an EXCEPTION clause in your block.

I also consolidated your two SQL statement with variable declaration and assignment into a single, equivalent statement, which is typically much faster and less susceptible to race conditions in a multi-user environment. Details in the manual about UPDATE.

CREATE OR REPLACE FUNCTION prod_profiles_check_grouped_item()
    RETURNS trigger AS
$func$
BEGIN 

   UPDATE public.prod_profiles_steps_items i
   SET    item_id = m.group_id
   FROM   public.inv_items_group_members m
   WHERE  m.item_id = NEW.item_id
   AND    i.item_id = NEW.item_id;

   RETURN NEW;

EXCEPTION WHEN UNIQUE_VIOLATION THEN
-- This catches *any* unique violation in the function. Use a nested block, 
-- if you have more candidates in the same function and want to narrow it down.
   DELETE FROM public.prod_profiles_steps_items 
   WHERE  item_id = NEW.item_id;

   RETURN NEW;
END
$func$ LANGUAGE plpgsql;  -- language declaration required!

Related answers with more code examples:

Related Question