CODEWITHSUNDEEP
sqlpostgresqlselectsql-insertdatabase-trigger
DrSnuggles
DrSnuggles

Reputation: 227

How to use a newly inserted value from another table in PostgreSQL?

Assuming I have two tables final table and table_1, I want to use the the newest values from table_1 and insert them with a trigger in the final_table with every INSERT ON table_1. When I create the triggerfunction inserttrigger() as shown in the example and create the trigger, I get the newest value times the number of rows in table_1. How to write the trigger proper that I get only the single newest record in table1?

Doing:

-- Create tables and inserting example values
CREATE TABLE final_table(id INTEGER, value_fin INTEGER);
CREATE TABLE table_1(id INTEGER, value INTEGER);
INSERT INTO table_1 VALUES(1, 200), (2,203), (3, 209);

-- Create Triggerfunction
CREATE OR REPLACE FUNCTION inserttrigger() 
    RETURNS TRIGGER AS
$func$

BEGIN

INSERT INTO final_table
SELECT latest.id, latest.value
FROM (SELECT NEW.id, NEW.value FROM table_1) AS latest;

RETURN NEW;                       
END;
$func$ language plpgsql;

-- Create Trigger
CREATE TRIGGER final_table_update BEFORE INSERT ON table_1
FOR EACH ROW EXECUTE PROCEDURE inserttrigger() ;

--Insert example values 
INSERT INTO table_1 VALUES(4, 215);

Results in:

SELECT * FROM final_table

id | value_fin
 4     215
 4     215
 4     215

But should look like:

id | value_fin
 4     215

While:

CREATE TRIGGER final_table_update BEFORE INSERT ON table_1
EXECUTE PROCEDURE inserttrigger() ;

Results in:

ERROR:  record "new" is not assigned yet
DETAIL:  The tuple structure of a not-yet-assigned record is indeterminate.

Upvotes: 0

Views: 51

Answers (1)

GMB
GMB

Reputation: 222482

I would recommend the VALUES() syntax:

CREATE OR REPLACE FUNCTION inserttrigger() 
RETURNS TRIGGER AS
$func$
BEGIN
    INSERT INTO final_table VALUES(NEW.id, NEW.value);
    RETURN NEW;                       
END;
$func$ language plpgsql;

CREATE TRIGGER final_table_update BEFORE INSERT ON table_1
FOR EACH ROW EXECUTE PROCEDURE inserttrigger();

Note that you could also get the same behavior with a common-table-expression and the returning syntax, which avoids the need for a trigger:

with t1 as (
    insert into table_1(id, value_fin) values(4, 215)
    returning id, value_fin
)
insert into final_table(id, value) select id, value_fin from t1

Upvotes: 1

Related Questions