What should return trigger BEFORE DELETE

Question

I am trying to make simple trigger function (Postgresql) but I am getting same error 'function did not return any row' in all these cases (just simple examples):

New:

UPDATE somewhere SET something = something - 1;
RETURN NEW;

Old:

UPDATE somewhere SET something = something - 1;
RETURN OLD;

What should I return when I call this function "before delete"? ("after insert/update" works well)

Tyvm for tips!

Full code as requested: Function:

CREATE OR REPLACE FUNCTION pictogram_frequency_on_delete()
RETURNS trigger AS
$BODY$
DECLARE

new_frequency RECORD;
target_unit RECORD;
current_row RECORD;
units_with_same_type RECORD;
what RECORD; 

BEGIN
SET search_path TO 'myScheme';

CASE TG_OP
WHEN 'DELETED' THEN what := OLD;
ELSE what:= OLD;
END CASE;

SELECT unit_type_uid INTO STRICT target_unit 
FROM unit 
WHERE unit_uid = what.unit_uid;

SELECT count(*) AS exists INTO STRICT current_row 
FROM unit_type_pictogram utp 
WHERE utp.pictogram_uid = what.pictogram_uid 
AND utp.unit_type_uid = target_unit.unit_type_uid;

IF (current_row.exists = 0) THEN
    RETURN what; /* return new/old doesnt work too */
END IF;

UPDATE unit_type_pictogram utp 
SET frequency = frequency - 1
WHERE utp.pictogram_uid = what.pictogram_uid 
AND utp.unit_type_uid = target_unit.unit_type_uid;

RETURN what; /* return new/old doesnt work too */

END
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Trigger:

CREATE TRIGGER on_delete_frequency
BEFORE DELETE
ON unit_pictogram
FOR EACH ROW
  EXECUTE PROCEDURE pictogram_frequency_on_delete();

Usagi Miyamoto · Accepted Answer · 2017-07-11T13:00:48.457

5

From 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 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 careful 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).

EDIT

Try something like this:

CREATE OR REPLACE FUNCTION pictogram_frequency_on_delete()
RETURNS trigger AS
$BODY$
BEGIN
  UPDATE unit_type_pictogram AS utp
    SET frequency = frequency - 1
    FROM unit
    WHERE utp.pictogram_uid = OLD.pictogram_uid
      AND unit_uid = OLD.unit_uid
      AND utp.unit_type_uid = unit.unit_type_uid;
  RETURN OLD;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

edited Jul 11 '17 at 13:00

answered Jul 11 '17 at 12:26

Usagi Miyamoto

6,196
1
19
33

Yea, it says "OLD row for DELETE triggers" but it returns error for me – Úca Jul 11 '17 at 12:28
Show us the **whole** function. – Usagi Miyamoto Jul 11 '17 at 12:32
That is not a function, just a PL/PgSQL block... Also show us the `CREATE TRIGGER` statement. – Usagi Miyamoto Jul 11 '17 at 12:34
It is there too now, any idea what if wrong? All my triggers which are after delete or update works fine. – Úca Jul 11 '17 at 12:44
What debugger are you using? – Usagi Miyamoto Jul 11 '17 at 12:46
The debugger is a part of my web application based on Nette. – Úca Jul 11 '17 at 12:46
Whole error (translated) `Request did not return any row CONTEXT: PL/pgSQL fuunction pictogram_frequency_on_delete() row 17 on SQL query SQL statement "DELETE FROM ONLY "myScheme"."unit_pictogram" WHERE $1 OPERATOR(pg_catalog.=) "unit_uid""` – Úca Jul 11 '17 at 12:48
If my function is absolutly empty, and returns only OLD, it works - as soon as I add any "SELECT" to my function, it returns error – Úca Jul 11 '17 at 12:51
Found the error already, debugger just gave me wrong Exceptions :( tyvm for your time, UPed your answer – Úca Jul 11 '17 at 13:07
At first glance `exists` is a reserved word.... Also `CASE` is not a statement, but a "expression".... – Usagi Miyamoto Jul 11 '17 at 13:10
I don't know why, but my pgAdmin doesn't "marks" word exists as reserved word and it works. Case is experssion, but my dubugger told me to add "Else" statement :/ – Úca Jul 11 '17 at 13:13
Oh, yeah: the semicolon inside the `CASE` expression is terminating it... – Usagi Miyamoto Jul 11 '17 at 13:14

score 3 · Answer 2 · answered Jul 11 '17 at 12:26

3

You should RETURN OLD;.

Your function must be defined as RETURNS trigger.

answered Jul 11 '17 at 12:26

Laurenz Albe

209,280
17
206
263

I am using "RETURN OLD" but debugger returns error "'function did not return any row". How do you mean "Must be defined as returns trigger"? – Úca Jul 11 '17 at 12:28

What should return trigger BEFORE DELETE

2 Answers2

EDIT