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)