Re: Multi-row update w. plpgsql function

> > Imagine a table called 'message_table':
> >
> > mid | message | status
> > ----+---------+-------
> > 1 | Text1 | H
> > 2 | Text2 | H
> > 3 | Text3 | H
> > 4 | Text4 | H
> >
> > A web page presents the user with all messages flagged with
> 'H'. User
> > checks messages 1,3 and 4 and submits form.
> > (i.e. approved=1&approved=3&approved=4)
> >
> > After performing postgreSQL update, rows 1, 3 and 4 would
> be updated
> > to:
> >
> > mid | message | status
> > ----+---------+-------
> > 1 | Text1 | A
> > 2 | Text2 | H
> > 3 | Text3 | A
> > 4 | Text4 | A
>
> BEGIN;
> UPDATE message_table SET status = 'A' WHERE mid = 1; UPDATE
> message_table SET status = 'A' WHERE mid = 3; UPDATE
> message_table SET status = 'A' WHERE mid = 4; COMMIT;
>
> would do that. Have your application generate an appropriate
> UPDATE line for each "approved" entry in the form data, wrap
> it in a transaction, and away you go.

It would probably be even more efficient to do:
UPDATE message_table SET status = 'A' WHERE mid IN (1,3,4)

and then use client code to generate the comma-separated list of ids.
(Don't forget to make sure they are actual integers so you don't get a
sql injection from it - I don't think parametrised queries can deal with
comma lists)

//Magnus