skip to Main Content

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

2

Answers


  1. Chosen as BEST ANSWER

    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
            AFTER INSERT ON schema.went_to
            FOR EACH ROW
            EXECUTE PROCEDURE migrated_id();
    

    It WORKED!!


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

    Related:

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search