Copy value from other table on insert trigger, postgres

Question

I'm quite new to PostgreSQL and have spend too much time already to try to find an example to adapt for what i want to do. So I hope I can get some help here

I have 2 tables, call them person and address

I want to create a trigger that copy street and house_nr from table Address when I insert a new value for person_id in the first table where person_id = oid

Table person
person_id
street
house_nr
other_attributes

Table Address
oid
street
house_nr
other_attributes

Something like this

INSERT INTO person
set person.street = address.street,
    person.house_nr = address.house_nr
FROM address
WHERE person_id = oid

Hope someone will have time to help cheers

Solution

First you need to create a trigger function. (Detailed info on http://www.postgresql.org/docs/9.3/static/plpgsql-trigger.html)

CREATE OR REPLACE FUNCTION func_before_trigger_on_person()
  RETURNS trigger AS
$BODY$
BEGIN
    SELECT address.street, address.house_nr
        INTO NEW.street, NEW.house_nr
    FROM address
    WHERE address.oid = NEW.person_id;

    RETURN NEW;
END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Then you need to add this function to the table. (Detailed info on http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html)

CREATE TRIGGER before_trigger_on_person
  BEFORE INSERT OR UPDATE
  ON person
  FOR EACH ROW
  EXECUTE PROCEDURE func_before_trigger_on_person();

I did not try this specific solution. However it should work, I just modified my own triggers on Postgres 9.3

Comments

[Nuri Tasdemir]

What you ask is possible. However I do not get why you are asking for it. It is redundant to have the same data in two places, since you can access address data.

[geogrow]

Yes it does look redundant with the simple example of columns I have here. I will use it in an application where the user will type in the id and the other fields will be filled in automatically. The "master table" is generated with a view over several tables.

[Raman]

Another situation where this is useful is when the associated data can change over time, but the current table is historical record and should remain static.

[geogrow]

Thank you!! I will go ahead and try it out

[geogrow]

Thank you, it worked perfectly. It gave me more insight to how write triggers too. Very much appreciated!!