PostgreSQL - how to recognize if function was called by delete or update trigger?

Question

I have function, which should be called after update and delete on one table.

At the end of function I have to return "old" if function was called after delete or "new" if procedure was called after update.

I did it like this:

if new is null
then
 return old;
end if;
return new;

But I get error telling me that "new" has not been initialized if function is called after delete. My question is how to recognize if function was called after delete or update?

Answer 1

Assuming this is a PL/pgSQL function, then - as documented in the manual - a trigger function automatically has access to some special variables. One of them is TG_OP that identifies the trigger operation.

The manual even has an example on how to use it:

IF (TG_OP = 'DELETE') THEN
 -- do something with the OLD record
ELSIF (TG_OP = 'UPDATE') THEN
 -- do something with the NEW record
ELSIF (TG_OP = 'INSERT') THEN
 -- do something entirely different
END IF;

Answer 2

There will be a struct called 'EventTriggerData' passed to the trigger function as its argument.

It can be read as,

event_trigger_data = (EventTriggerData *)fcinfo->context;

Include this condition check in your code,

if((nodeTag(event_trigger_data->parseTree) == T_DeleteStmt)
 // trigger called from delete statement
if((nodeTag(event_trigger_data->parseTree) == T_UpdateStmt)
 // trigger called from update statement

Might be helpful if you are created udf's written in C

• Eleer: Thank you for your answer, but I used solution from accepted answer, since it seems more simple and straight forward solution