Database – When do we need to return a value from a trigger program?

CREATE TRIGGER my_trigger
AFTER INSERT ON table_name
FOR EACH ROW EXECUTE PROCEDURE some_trigger_function();

create table my_table(id int);

-- do not insert rows with id> 10
create or replace function before_insert_on_my_table()
returns trigger language plpgsql as $$
begin
 return case
 when new.id> 10 then null
 else new
 end; 
end $$;

create trigger before_insert_on_my_table
before insert on my_table
for each row execute procedure before_insert_on_my_table();

insert into my_table
values ​​(15), (10), (5), (20)
returning id;

 id 
----
 10 
 5
(2 rows)

Documentation about trigger procedure (https://www.postgresql.org/docs/9.6/static /plpgsql-trigger.html) says: “The trigger function must return NULL or record/row”.

Example

CREATE TRIGGER my_trigger 
AFTER INSERT ON table_name
FOR EACH ROW EX ECUTE PROCEDURE some_trigger_function();

Assuming that some_trigger_function() returns a record/line, I understand that the body of the program is called and executed on this event, on this table, and so on… But where does it return? Worth this program?

The question is: When do we use which return value? Why do we need this value? Can you give some examples/explanations of using this return value?

Read the documentation:

Trigger functions invoked by per-statement triggers should always
return NULL. Trigger functions invoked by per-row triggers can return
a table row (a value of type HeapTuple) to the calling executor, if
they choose. A row-level trigger fired before an operation has the
following choices:

• It can return NULL to skip the operation for the current row. This instructs the executor to not perform the row-level operation that< br> invoked the trigger (the insertion, modification, or deletion of a
  particular table row).

• For row-level INSERT and UPDATE triggers only, the returned row becomes the row that will be inserted or will replace the row being
  updated. This allows the trigger function to modify the row being
  inserted or updated.

A row-level BEFORE trigger that does not intend to cause either of
these behaviors must be carefu l to return as its result the same row
that was passed in (that is, the NEW row for INSERT and UPDATE
triggers, the OLD row for DELETE triggers).

A row- level INSTEAD OF trigger should either return NULL to indicate
that it did not modify any data from the view’s underlying base
tables, or it should return the view row that was passed in (the NEW
row for INSERT and UPDATE operations, or the OLD row for DELETE
operations). A nonnull return value is used to signal that the trigger
performed the necessary data modifications in the view. This will
cause the count of the number of rows affected by the command to be
incremented. For INSERT and UPDATE operations, the trigger may modify
the NEW row before returning it. This will change the data returned by
INSERT RETURNING or UPDATE RETURNING, and is useful when the view will
not show exactly the same data that was provided.

The return value is ignored for row-level triggers fired after an
operation, and so they can return NULL.

The following example shows how to conditionally abort the insert in the trigger:

create table my_table(id int);

-- do not insert rows with id> 10
create or replace function before_insert_on_my_table()
returns trigger language plpgsql as $ $
begin
 return case
 when new.id> 10 then null
 else new
 end;
end $$;
create trigger before_insert_on_my_table
before insert on my_table
for each row execute procedure before_insert_on_my_table();
insert into my_table
values ​​(15), (10), (5), (20)
returning id;
 id 
----
 10
 5
(2 rows)< /pre>