Postgresql update specific fields based on a value (trigger)

Question

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();

Kaushik Nayak · Accepted Answer · 2018-12-17 13:16:12Z

1

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

edited Dec 17, 2018 at 13:16

answered Dec 17, 2018 at 3:54

Kaushik Nayak

32k6 gold badges36 silver badges54 bronze badges

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

9 Comments

zar3bski Over a year ago

ERROR: the « December » does not exist for « psy_revenues » LINE 2: SET "December " = (

zar3bski Over a year ago

Looks like December still count as a str rather than a field name

Kaushik Nayak Over a year ago

@DavidZarebski : That's not the problem.I changed it to 'month' instead of 'Month', check now? I hope your month columns are not created with a different case (using quotes)?

zar3bski Over a year ago

same issue. My column names are January, February, etc etc. Not sure how my columns are created since they are generated by django models (which might be one of the reasons why I struggled that much). Apart from a SELECT * FROM psy_revenues; , how can I be sure of the way my column names were generated?

Kaushik Nayak Over a year ago

@DavidZarebski : Edit your question and show us your table definition of both the tables involved and some sample data for each. It's hard to tell without testing it myself.

|

GMB · Accepted Answer · 2018-12-17 01:11:17Z

You need to use dynamic sql from within your trigger function : generate a string containing the proper SQL code, and use the EXECUTE keyword to run it.

The following code should do the trick :

CREATE OR REPLACE FUNCTION update_revenues()
RETURNS trigger AS
$BODY$
    DECLARE 
        new_month text;
        new_owner_id integer;
    BEGIN
        new_month    := to_char(NEW.date, 'Month');
        new_owner_id := NEW.owner_id;
        EXECUTE 
            'UPDATE psy_revenues 
            SET ' || new_month || ' = (
                SELECT SUM(payed) FROM psy_consultations 
                WHERE (
                    owner_id = ' || new_owner_id || ' 
                    AND to_char(date, ''Month'') = ' || new_month || '
                )
            )';

    RETURN NEW;
    END;
$BODY$ LANGUAGE plpgsql;

Collectives™ on Stack Overflow

Postgresql update specific fields based on a value (trigger)

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related