How to import UNIQUE KEY from one table to another in a trigger function?-CodePudding

(obs: the ids are not pk they are unique keys, the pks are a government "id" called cpf);

I want to create a function that reads the id in table1 [person] of a person and then insert this [person].id in table 2 [went_to].id (it is null at the moment)* if the cpf in *[went_to] matches the cpf in [person]

But the following error happens (it never stops going because i don't know how to stoop the loop):

Here is the code:

CREATE OR REPLACE FUNCTION migrate_id() RETURNS INTEGER AS $$
BEGIN
   LOOP
        UPDATE schema.went_to
        SET id = person.id 
        FROM schema.person 
        WHERE went.cpf = person.cpf;
   END LOOP;
END
$$
 LANGUAGE plpgsql

But as it will be part of a trigger I don't think I need to create a Loop as long as I define the Trigger as to EACH ROW.

Then I tried without the loop:

CREATE OR REPLACE FUNCTION migrate_id() RETURNS INTEGER AS $$
BEGIN

        UPDATE schema.went_to
        SET id = person.id 
        FROM schema.person 
        WHERE went.cpf = person.cpf;

END
$$
 LANGUAGE plpgsql

But it doesn't work because there is no return in the end of the function. So I tried to make a return in many ways but I failed in all my tries. I end up running out of ideas and I can't find the "answer" to my problem online, could someone help me?

I manage to get an "result" if I type

CREATE OR REPLACE FUNCTION pa_migrarID_vp(OUT ID INTEGER) RETURNS INTEGER AS $$

But it gives null (because all the ids are already null)

Here is how I intend to use the trigger:

CREATE TRIGGER ADD_ID
    AFTER INSERT ON went_to
    FOR EACH ROW
    EXECUTE PROCEDURE migrate_id();

CodePudding user response：

I just manage to solve it!! At least I think so, so I will run some tests and then I will post the answer here in case someone else needs help with it too.

The code is the same but I just did not made the function into a trigger function:

CREATE OR REPLACE FUNCTION schema.migrated_id() RETURNS TRIGGER AS $$
BEGIN

    UPDATE schema.went_to
    SET id = person.id 
    FROM schema.person 
    WHERE went.cpf = person.cpf;

END
$$
 LANGUAGE plpgsql

Then I just made the trigger and it WORKED!

 CREATE TRIGGER ADD_ID
        BEFORE INSERT ON schema.went_to
        FOR EACH ROW
        EXECUTE PROCEDURE migrated_id();

It WORKED!!

CodePudding user response：

Your answer cannot work. You cannot update a row in a BEFORE INSERT trigger because it has not been inserted yet.

Instead, assign to NEW directly:

CREATE OR REPLACE FUNCTION schema.migrated_id()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   SELECT INTO NEW.id  p.id 
   FROM   schema.person p
   WHERE  p.cpf = NEW.cpf;
END
$func$

CREATE TRIGGER add_it
BEFORE INSERT ON schema.went_to
FOR EACH ROW
EXECUTE FUNCTION schema.migrated_id();