Re: Multi-row update w. plpgsql function

Hi,

If your checkboxes are like

input type="checkbox" name="approved" value="1"
input type="checkbox" name="approved" value="2"
input type="checkbox" name="approved" value="3"
input type="checkbox" name="approved" value="4"

and 1, 3 and 4 are checked your form data will be approved=1,3,4

Then you can just say

UPDATE message_table SET status = 'A' WHERE mid IN($approved);

Not a function though.

BR,

Aarni

testing=# SELECT * FROM message_table;
mid | message | status
-----+---------+--------
1 | text1 | H
2 | text2 | H
3 | text3 | H
4 | text4 | H
(4 rows)

testing=# UPDATE message_table SET status = 'A' WHERE mid IN(1,3,4);
UPDATE 3
testing=# SELECT * FROM message_table;
mid | message | status
-----+---------+--------
2 | text2 | H
1 | text1 | A
3 | text3 | A
4 | text4 | A
(4 rows)

testing=#

On Wednesday 14 December 2005 01:00, 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?
>
> 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 2: Don't 'kill -9' the postmaster

--
Aarni Ruuhimäki
--------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core** linux system
--------------