Skip to content
Advertisement

Postgresql update specific fields based on a value (trigger)

I am writing a Trigger in Postgresql to update dynamically the content of specific columns of a revenues table based on inserts on a consultations table. [edit] Here is how my tables look like

I am looking for a straightforward solution to SET only the column (month) corresponding the timestamp of the NEW row added on consultations. This is fairly easy to get the month name based on timestamps in psql: to_char(2018-12-05 04:42:11.66867+01, ‘Month’) ==> December

[the problem] However, I do not know how to use this to indicate which column psql should update. Here is a general idea of what I am looking for. The main problem is that I am using a value to denote a field (see the SET line). Is there a workaround so I could directly select the relevant field? (I tried several things but I was not very successful)

Advertisement

Answer

You should use dynamic SQL. Please avoid concatenation to avoid SQL injection, use format function and positional arguments

EDIT: It seems TO_CHAR() pads spaces for month string, I have added FM modifier ( as suggested by @horse ) to remove it.

See this:

Demo

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement