Update a column in a function using plpgsql

Question

I am writing a trigger function in PostgreSQL 9.5. When a column named action_flag is updated from FALSE to TRUE, a function is called.

If order_num is in some table, then I want the update from FALSE to TRUE to proceed.

Else, if order_num is in some other table, then I want to raise an exception and prevent the update from happening.

I need assistance writing the UPDATE within my IF statement. The ELSEIF part functions as expected.

CREATE OR REPLACE FUNCTION check_ingredient_availability()
RETURNS TRIGGER AS 
$Body$
BEGIN
    if (OLD.order_num IN ( SELECT  order_num
                   FROM    can_prep_m_orders )) THEN
        RAISE NOTICE 'This is a notice.';
        --UPDATE meal_order
        --SET actioned_flag=TRUE
        --WHERE order_num=OLD.order_NUM;

        --EXECUTE  'UPDATE  meal_order
            -- SET   actioned_flag=TRUE
             --WHERE     order_num=$1' 
             --USING OLD.order_num;
        RETURN NULL;
    ELSEIF (OLD.order_num IN ( SELECT  order_num
                        FROM    cannot_prep_m_orders )) THEN
        RAISE EXCEPTION 'Missing ingredients. Cannot prepare order number %.',
                OLD.order_num;
        RETURN NULL;
    ELSE
        RETURN NULL;
    END IF;
END;    
$Body$ LANGUAGE plpgsql;

score 1 · Accepted Answer · edited May 23 '17 at 12:09

Use EXISTS:

CREATE OR REPLACE FUNCTION check_ingredient_availability()
   RETURNS TRIGGER AS 
$func$
BEGIN
   IF EXISTS (
         SELECT 1 FROM can_prep_m_orders
         WHERE  order_num = NEW.order_num  -- NEW !?
         ) THEN
      RAISE NOTICE 'This is a notice.';

      -- do nothing, let original UPDATE proceed

      RETURN NEW;   -- let UPDATE proceed
   END IF;

   IF EXISTS (
         SELECT 1 FROM cannot_prep_m_orders
         WHERE  order_num = NEW.order_num  -- NEW!?
         ) THEN

      RAISE EXCEPTION 'Missing ingredients. Cannot prepare order number %.', NEW.order_num;
                                       -- I assume you want to mention the *new* order_num?
   END IF;

   RETURN NULL;  -- cancel original UPDATE in all other cases
END
$func$  LANGUAGE plpgsql;

PL/pgSQL checking if a row exists - SELECT INTO boolean

Also, add a WHEN clause to your trigger to run these checks when

action_flag is updated from FALSE to TRUE

CREATE TRIGGER your_trigger_name
BEFORE UPDATE ON your_table
FOR EACH ROW 
WHEN (NEW.action_flag AND NOT OLD.action_flag)
EXECUTE procedure check_ingredient_availability();

Trigger with multiple WHEN conditions

Thank you for your reply, Erwin. This was very helpful! I didn't know that RETURN NEW; is what allowed the update to proceed. — Pizzas, Mar 19 '17 at 19:26
@Pizzas: [Consider reading this chapter of the manual.](https://www.postgresql.org/docs/current/static/plpgsql-trigger.html) — Erwin Brandstetter, Mar 19 '17 at 20:03

Update a column in a function using plpgsql

1 Answers1