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
SELECT * FROM psy_revenues; id | owner_id | January | February | March | April | etc | etc ---+----------+---------+----------+-------+-------+-----+---- 2 | 1 |0 | 0 | 0 | 0 | | SELECT * FROM psy_consultations; id | first_name | last_name | date | payed | owner_id ---+------------+-----------+-----------------------------+-------+--------- 3 | GĂ©rard | Bouchard |2018-12-05 04:49:26.064397+01| 80 |1 4 | Pasti | Lami |2018-12-05 23:23:52.454849+01| 60 |2
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)
CREATE OR REPLACE FUNCTION update_revenues() RETURNS trigger AS $BODY$ BEGIN UPDATE psy_revenues SET to_char(NEW.date, 'Month') = ( SELECT SUM(payed) FROM psy_consultations WHERE ( owner_id = NEW.owner_id AND to_char(date, 'Month')=to_char(NEW.date, 'Month') ) ); RETURN NEW; END; $BODY$ LANGUAGE plpgsql; CREATE TRIGGER update_revenues AFTER INSERT ON psy_consultations FOR EACH ROW EXECUTE PROCEDURE update_revenues();
Advertisement
Answer
You should use dynamic SQL. Please avoid concatenation to avoid SQL injection, use format
function and positional arguments
CREATE OR REPLACE FUNCTION update_revenues() RETURNS trigger AS $BODY$ DECLARE BEGIN EXECUTE format ( 'UPDATE psy_revenues SET %I = ( SELECT SUM(payed) FROM psy_consultations WHERE owner_id = $1 AND to_char(date, ''MM-YYYY'') = $2 )' ,to_char(NEW.date, 'FMMonth') ) --column name to update(%I) USING NEW.owner_id , to_char(NEW.date, 'MM-YYYY');--values for owner_id & month RETURN NEW; END; $BODY$ LANGUAGE plpgsql;
EDIT: It seems TO_CHAR()
pads spaces for month string, I have added FM modifier ( as suggested by @horse ) to remove it.
See this:
knayak=# select '|'||to_char(current_date,'Month')||'|' as mon ; mon ------------- |December | (1 row)