Are there functions in PostgreSQL like Oracles 'UPDATING, DELETING, INSERTING' functions?

Question

I hope that someone can help. Currently I'm working with a PostgreSQL database but I really don't know how I can solve my problem. In PLSQL there are operations inside a trigger called 'INSERTING, UPDATING, DELETING'. But when it comes to PostgreSQL I'm baffered...

In Oracle I can do this:

v_action CHAR(1) := CASE WHEN INSERTING THEN "I" WHEN UPDATING THEN "U" WHEN DELETING THEN "D" END;

you are looking for TG_OP?..

Vao Tsun
– Vao Tsun

2016-12-05 13:44:11 +00:00
Commented Dec 5, 2016 at 13:44 — Vao Tsun
– Vao Tsun, Commented Dec 5, 2016 at 13:44

Vao Tsun · Accepted Answer · 2016-12-05 14:19:58Z

2

https://www.postgresql.org/docs/current/static/plpgsql-trigger.html

TG_OP Data type text; a string of INSERT, UPDATE, DELETE, or TRUNCATE telling for which operation the trigger was fired.

This should do the trick:

v_action := CASE 
  WHEN TG_OP = 'INSERT' THEN 'I' 
  WHEN TG_OP = 'UPDATE' THEN 'U' 
  WHEN TG_OP = 'DELETE' THEN 'D' 
END;

edited Dec 5, 2016 at 14:19

answered Dec 5, 2016 at 13:46

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

mortendegn Over a year ago

Thank you, this is what I looked for. But when the trigger gets triggered I get following error: ERROR: column "INSERT" does not exist

mortendegn Over a year ago

What it looks like in my trigger:

v_action CHAR(1) := CASE WHEN TG_OP = "INSERT" THEN "I" WHEN TG_OP = "UPDATE" THEN "U" WHEN TG_OP = "DELETE" THEN "D" END;

Vao Tsun Over a year ago

single quotes, not double.

Collectives™ on Stack Overflow

Are there functions in PostgreSQL like Oracles 'UPDATING, DELETING, INSERTING' functions?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related