(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
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:
Then I just made the trigger and it WORKED!
It WORKED!!
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:Related: