Re: Multi-row update w. plpgsql function

This might be easier to use this SQL:
UPDATE message_table SET status = 'A' WHERE mid IN (1,2,3);

The following might work for Cocoon (never used it):
UPDATE message_table SET status = 'A' WHERE mid IN
(<xsp-request:get-parameter name="approved"/>);

Aaron

On 12/13/05, Daniel Hertz <danielhertz(at)shaw(dot)ca> wrote:
>
> Given a set of checkbox values that are submitted through an html form,
> how do you loop through the submitted values to update more than one row
> in a table?
>
> 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
>
> I have never written a plpgsql function, but tried:
>
> CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS
> integer AS
> $body$
> DECLARE
> new_status varchar;
> new_sample record;
>
> BEGIN
> new_status := 'A';
>
> FOR new_sample IN SELECT * FROM message_table WHERE status='H' ORDER BY
> mid LOOP
> UPDATE message_table SET status = new_status
> WHERE mid = approved;
> END LOOP;
>
> RETURN 1;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
>
> I call the function with:
> SELECT update_messages(<xsp-request:get-parameter name="approved"/>);
>
> I'm using apache cocoon, which is why you see the variable placeholder:
> <xsp-request:get-parameter name="approved"/>);
>
> Unfortunately, the function only updates the first value submitted (mid
> 1), and doesn't loop through the other two values submitted.
>
> Can someone help this novice from getting ulcers?
>
> Thanks for your help!
>
> Daniel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
>