2f005 error control reached end of trigger procedure without return

I am trying to create a trigger on a column of my table like this in Postgresql 9.5:

CREATE OR REPLACE FUNCTION app.combo_min_stock()
  RETURNS TRIGGER AS
$combo_sync$

DECLARE combo_product_ids INTEGER[] := array(SELECT combo_product_map.combo_productid FROM app.combo_product_map WHERE combo_product_map.productid=NEW.productid);
DECLARE comboid INTEGER;

BEGIN

  -- UPDATE MINIMUM STOCK FOR COMBO SKUS --

   FOREACH comboid IN ARRAY combo_product_ids
   LOOP

      UPDATE app.inventory SET
        good_stock = combo_data.min_good_stock,
        bad_stock = combo_data.min_bad_stock,
        to_be_updated = true
      FROM
        (SELECT
          product.productid,
          MIN(inventory.good_stock) as min_good_stock,
          MIN(inventory.bad_stock) as min_bad_stock
        FROM
          app.product,
          app.inventory,
          app.combo_product_map
        WHERE
          product.is_combo=true AND
          product.productid=comboid AND
          product.productid=combo_product_map.combo_productid AND
          combo_product_map.productid=inventory.productid
        GROUP BY
          product.productid) AS combo_data
      WHERE
        combo_data.productid=inventory.productid;


   END LOOP;

END; 
$combo_sync$
  LANGUAGE plpgsql;
ALTER FUNCTION app.combo_min_stock()
  OWNER TO postgres;



CREATE TRIGGER combo_sync
  AFTER UPDATE OF good_stock
  ON app.inventory
  FOR EACH ROW
  EXECUTE PROCEDURE app.combo_min_stock();

When I try to edit a value for good_stock column in my inventory table, it is throwing me this error:

An error has occurred:
ERROR: control reached end of trigger procedure without RETURN
CONTEXT: PL/pgSQL function app.combo_min_stock()

What is wrong with this query?

Edit: I have added the updated code to my post, I was missing a return null outside my IF block

I have a table working with the most recent data and would like to add a trigger for updates made to it so that I can record the changes made to a particular column in another table history. I run this code to create a trigger function and test it but get an error

SQL Error [2F005]: ERROR: control reached end of trigger procedure without RETURN
  Where: PL/pgSQL function update_history()

My code:

-- create function for updates to track history
CREATE function update_history ()
RETURNS TRIGGER
as $$
BEGIN   
    IF NEW.discipline <> OLD.discipline THEN
    INSERT INTO history
        (ShiftId, fieldName, OldValue, NewValue)
    VALUES(New.shift_id, 'discipline', OLD.discipline, NEW.discipline);
    END IF;
return null; -- notice return outside the IF block
END;
$$
language plpgsql;


-- create trigger for my table after updates are made to the working table
create trigger update_history_trigger
after update on working
for each row execute PROCEDURE update_history();

SQL Error [2F005]: ERROR: control reached end of trigger procedure without RETURN
  Where: PL/pgSQL function update_history()

-- create function for updates to track history
CREATE function update_history ()
RETURNS TRIGGER
as $$
BEGIN   
    IF NEW.discipline <> OLD.discipline THEN
    INSERT INTO history
        (ShiftId, fieldName, OldValue, NewValue)
    VALUES(New.shift_id, 'discipline', OLD.discipline, NEW.discipline);
    END IF;
return null; -- notice return outside the IF block
END;
$$
language plpgsql;


-- create trigger for my table after updates are made to the working table
create trigger update_history_trigger
after update on working
for each row execute PROCEDURE update_history();

1 ответ

Лучший ответ

CREATE FUNCTION update_history ()
RETURNS TRIGGER
AS $$
BEGIN
    IF NEW.discipline <> OLD.discipline THEN
        INSERT INTO history (ShiftId, fieldName, OldValue, NewValue)
        VALUES(NEW.shift_id, 'discipline', OLD.discipline, NEW.discipline);
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;

есть ли способ исправить эту ошибку или есть что-то, чего мне не хватает в моем коде, я учусь postgresql и я работаю над table inheritance and triggers у меня есть две таблицы temporary_object table и persons table таблица лиц наследует все свойства временного объекта, затем я создал триггерную функцию в таблице лиц, которая проверяет записи с тем же id before updating что собой представляет tables моя проблема возникает, когда я пытаюсь запустить insert query, я получаю это errors

ERROR: end of trigger procedure achieved without RETURN
CONTEXT: function PL / pgSQL muli_function ()

ERROR: end of trigger procedure achieved without RETURN
SQL-state: 2F005
Context: The PL / pgSQL muli_function ()

это по запросу вставки

INSERT INTO persons (id, time_create, time_dead, First_name, Last_name) values (1, '2011-10-07 15:25:00 EDT', '2011-10-07 3:25 PM EDT', 'sathiya', 'james');

и это моя функция триггера и сам триггер

CREATE FUNCTION muli_function() RETURNS trigger AS '
BEGIN
  IF tg_op = ''UPDATE'' THEN
     UPDATE persons
     SET time_dead = NEW.time_create
     Where
         id = NEW.id
         AND time_dead IS NULL
         ;
  RETURN new;
  END IF;
END
' LANGUAGE plpgsql;

CREATE TRIGGER sofgr BEFORE INSERT OR DELETE OR UPDATE
        ON persons FOR each ROW
        EXECUTE PROCEDURE muli_function();

и это мои два запроса таблиц

CREATE TABLE temporary_object
(
  id integer NOT NULL,
  time_create timestamp without time zone NOT NULL,
  time_dead timestamp without time zone,
  PRIMARY KEY (id, time_create)
);

CREATE TABLE persons
(

  First_Name text,
  Last_Name text
)
INHERITS (temporary_object);