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

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)

Demo

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