Obtain inserted value to update a column of another table inside a for loop

Question

I am basically trying to achieve what would be the equivalent of this c# in postgres pretending that tables are some lists

List<Comment> comments;
List<Post> posts;

//SELECT
foreach (var comment in comments.OrderBy(c => c.id))
{
    //INSERT
    var post = new Post(title: comment.title);
    posts.Add(post);

    //UPDATE
    comment.postId = post.id;
}

Basically, I am adding a new "parent" table and I need to run some code in the migration so every existing "child" has one parent. My postgres skills are limited so I don't know where to start.

Here is what I have so far

DO
$do$
BEGIN 
FOR comment IN 
    SELECT * FROM comments ORDER BY id
LOOP
   INSERT INTO posts (title)
   VALUES (comment.title);

   -- how do I update???
END LOOP;
END
$do$;

Sorry, misread it, but I still don't understand exactly what you are having trouble with. What Postgresql do you have? Is it showing you an error? How can you have a `comment` without a `postId`? — Dour High Arch, Nov 22 '15 at 22:18
@DourHighArch Update the question with what I have. The problem is that I can't run the script because its incomplete. — Cristian Garcia, Nov 22 '15 at 22:21
Are you looking for the [Postgresql function for last inserted id](http://stackoverflow.com/questions/2944297)? — Dour High Arch, Nov 22 '15 at 22:42

score 0 · Answer 1 · answered Dec 01 '15 at 17:44

Unless I've missed the boat, I think this is what you're after:

DO
$$
DECLARE
  komment comments;
  pid bigint;
BEGIN 
  FOR komment IN 
      SELECT * FROM comments ORDER BY id
  LOOP
     INSERT INTO posts (title)
     VALUES (komment.title)
     returning id into pid;

     update comments
     set post_id = pid
     where id = komment.id;

  END LOOP;
END
$$;

This presupposes that posts.id is a serial, defaulted to a sequence or has some other mechanism for auto-populating, which I assume is what you intended.

Obtain inserted value to update a column of another table inside a for loop

1 Answers1