Multi-row update w. plpgsql function
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
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
Show quoted text
On 12/13/05, Daniel Hertz <danielhertz@shaw.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 | HA 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 | AI 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
Aaron Koning wrote:
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@shaw.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?
You rock, Aaron! Thanks for helping this newbie out. Much more
economical than looping with my function.
The (<xsp-request:get-parameter name="approved"/>) isn't working, but
I'll try to see if I can use some xslt to parse the query string and
replace a variable ($approved_values) in:
UPDATE message_table SET status = 'A' WHERE mid IN ($approved_values);
If I new some javascript, I could probably do it client side, BEFORE it was sent to the server, but SIGH...I'm a graphic designer with little coding skill.
Thanks again.
Daniel