PostgreSQL Trigger to Update Inserted Row with Data from Another Table – How to Guide

postgresqltriggerupdate

I have 2 tables in PostgreSQL-12, SENSORS (receives timeseries data feed from sensors) and LABELS (contains sensor labels & meta-data).
I am trying to create a PostgreSQL trigger to update the sensor data as they are inserted into SENSORS. Each row of sensor data will be updated with the corresponding label name from LABELS. Unable to get the row update to work. Sample shown below:

Sample Dataset

CREATE TABLE sensor (
   datavalue integer, 
   created timestamp,
   label_id integer,
   label_name text);

CREATE TABLE labels (
   id integer,
   label_id integer,
   label_name text);

Insert into labels values (1, 215, 'Home'), (2, 216, 'Shop'), (3, 217, 'Flat'), (4, 218, 'Street');
Insert into sensor values (67, '2020-09-02 08:40:07.354', 215),(67, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

Trigger Function

CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS 
$func$
BEGIN
UPDATE sensor 
    SET label_name = b.label_name 
    from labels b
    where new.label_id = b.label_id;
     RETURN NEW; 
END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
AFTER INSERT OR UPDATE ON sensor
FOR EACH ROW EXECUTE PROCEDURE update_name();

Test row insertion

Insert into sensor values (78, '2020-09-02 08:40:07.354', 215),(77, '2020-09-02 08:41:07.354', 215),(67, '2020-09-02 08:41:07.354', 216);

I have a very large number of rows flowing in, and would like to update each row as it is inserted.

Any assistance greatly appreciated. Thanks!

Best Answer

Most likely you're going in wrong direction but I have only gut feelings for that.

But, you can play with triggers - maybe it will do a work. However it seems that you will have massive updates in each trigger call because you are updating all rows from sensor with new label, which seems just wrong.

As I'm not sure about your specific needs, I will just push you in most proper way (I hope) with these changes:


CREATE OR REPLACE FUNCTION update_name()
RETURNS trigger AS
$$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO sensor(datavalue, created, label_id, label_name)
        SELECT NEW.datavalue, NEW.created, NEW.label_id, b.label_name
        from labels b
        where new.label_id = b.label_id;

    ELSIF TG_OP = 'UPDATE' THEN
        UPDATE sensor
        SET label_name = b.label_name
        from labels b
        where new.label_id = b.label_id;

    END IF;
    RETURN NULL;

END
$$  LANGUAGE plpgsql;

CREATE TRIGGER name_update_trigger
BEFORE INSERT OR UPDATE ON sensor
FOR EACH ROW
WHEN (pg_trigger_depth() < 1)
EXECUTE PROCEDURE update_name();

Here, you do these changes instead (before) insert or update. Insert will be proper, update needs to be adjusted or removed as from your description it seems like you will be making only inserts here.

And please note that there is limiter as proposed here https://dba.stackexchange.com/a/103661/213360.

Related Solutions

Postgresql – Get inserted row count when master table isn’t modified

If this is still desirable for you (although not THAT vital), this is a solution to it:

CREATE TABLE rows_affected_in_session (
  session_pid int NOT NULL,
  session_time timestamptz NOT NULL,
  table_name text NOT NULL,
  rows_affected int NOT NULL,
  PRIMARY KEY (session_pid, session_time, table_name));

CREATE OR REPLACE FUNCTION f_update_rows_affected_in_session (p_table regclass, p_rows_affected int) RETURNS void AS $BODY$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM rows_affected_in_session WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text) THEN
    INSERT INTO rows_affected_in_session (session_pid, session_time, table_name, rows_affected)
    VALUES (pg_backend_pid(), current_timestamp, p_table::text, p_rows_affected);
  ELSE
    UPDATE rows_affected_in_session SET rows_affected = rows_affected + p_rows_affected
    WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp AND table_name = p_table::text; 
  END IF;
END;
$BODY$ LANGUAGE PLPGSQL SECURITY DEFINER;

CREATE OR REPLACE FUNCTION f_rows_affected_in_session() RETURNS TABLE (table_name text, rows_affected int) AS $BODY$
  SELECT table_name, rows_affected
  FROM rows_affected_in_session
  WHERE session_pid = pg_backend_pid() AND session_time = current_timestamp
  ORDER BY 1
$BODY$ LANGUAGE SQL SECURITY DEFINER;

You use it by calling the function f_update_rows_affected_in_session(<table>, <rows_affected>) after every successful insert within triggers (which you can use ROW_COUNT diagnostics), and then use the function f_rows_affected_in_session() to retrieve the rows affected within the session (you can determine in it exactly which child tables were affected, or sum the results for a grand total). Example:

SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT f_update_rows_affected_in_session('mytable'::regclass, 1);
SELECT * FROM f_rows_affected_in_session();

Sql-server – Creating SQL Server triggers to copy a record from one table and populate another

OK, Here's my update based on your newly added info. The merge statement in the trigger will insure that newly added records will be inserted into hydrants. Otherwise, records that exist between inspections and hydrants will be updated in hydrants--when inspections is updated.

You can see when you run the insert and update (after the table and trigger creation below) that the row insert in the inspections table is inserted into hydrants whereas the row update to inspections is also updated in hydrants.

create table Inspections
(
    PK int identity(1,1),
    MyDate date
)
go

create table Hydrants
(
    PK int,
    MyDate date
)
Go
CREATE TRIGGER MyTrigger
   ON  Inspections
   AFTER  INSERT,UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
    MERGE INTO Hydrants as Target
    USING inserted as Source 
    ON (TARGET.PK = SOURCE.PK) 
    WHEN MATCHED  THEN 
    UPDATE SET TARGET.MyDate = SOURCE.MyDate
    WHEN NOT MATCHED BY TARGET THEN 
    INSERT (PK, MyDate) 
    VALUES (SOURCE.PK,SOURCE.MyDate);

END
GO
insert into Inspections(Mydate) values('01/10/2016')
select * from Hydrants
update Inspections set Mydate = '02/10/2016' where PK =1
select * from Hydrants

Best Answer

Related Solutions

Postgresql – Get inserted row count when master table isn’t modified

Sql-server – Creating SQL Server triggers to copy a record from one table and populate another

Related Question