Обсуждение: Multi-row update w. plpgsql
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="accepted"/>); I'm using apache cocoon, which is why you see the variable placeholder: <xsp-request:get-parameter name="accepted"/>); 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
On Tue, 2005-12-13 at 15:57 -0700, Daniel Hertz 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? ... > > 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; I can't see what you are looping for; you don't use new_sample inside the loop so the loop is completely redundant. I think this will just update the same row in the same way as many times as there are new_sample rows. > 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="accepted"/>); > > I'm using apache cocoon, which is why you see the variable placeholder: > <xsp-request:get-parameter name="accepted"/>); Since this is updating something, I assume that is putting in a single value of "approved". Perhaps you need to do the loop in the application program. > > Unfortunately, the function only updates the first value submitted (mid > 1), and doesn't loop through the other two values submitted. I suggest you put in some RAISE NOTICE statements so you can see what is actually happening. -- Oliver Elphick olly@lfix.co.uk Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA ======================================== Do you want to know God? http://www.lfix.co.uk/knowing_god.html