PostgreSQL Trigger Error : control reached end of trigger procedure without RETURN

Question

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?

Answer 1

According to CREATE TRIGGER docs

function_name

A user-supplied function that is declared as taking no arguments and returning type trigger, which is executed when the trigger fires.

In the syntax of CREATE TRIGGER, the keywords FUNCTION and PROCEDURE are equivalent, but the referenced function must in any case be a function, not a procedure. The use of the keyword PROCEDURE here is historical and deprecated.

What is trigger function?

Function which returns a trigger. ex:

CREATE FUNCTION do_something() RETURNS trigger

Important bit is trigger function should be function not a procedure

Difference between Procedures and Functions according to docs

A procedure is a database object similar to a function. The key differences are:

• Procedures are defined with the CREATE PROCEDURE command, not CREATE FUNCTION.

• Procedures do not return a function value; hence CREATE PROCEDURE lacks a RETURNS clause. However, procedures can instead return data to their callers via output parameters.

• While a function is called as part of a query or DML command, a procedure is called in isolation using the CALL command.

• A procedure can commit or roll back transactions during its execution (then automatically beginning a new transaction), so long as the invoking CALL command is not part of an explicit transaction block. A function cannot do that.

• Certain function attributes, such as strictness, don't apply to procedures. Those attributes control how the function is used in a query, which isn't relevant to procedures.

Simply put functions should have a return statement

So each and every trigger function need to have a return statement. Some possible return statements for trigger functions.

RETURN NULL;

RETURN OLD;

RETURN NEW;

Answer 2

Try using this:

   END LOOP;
   RETURN NULL;