1

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.

1

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:

  • It works beautifully! Thanks! (For the recored, the language declaration was there, I just didn't copy the line after the END.) – Yanick Rochon Sep 8 '14 at 5:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.