Re: Multi-row update w. plpgsql function

Owen makes a good point. Check that you are using the [] in the HTML input
variable for the checkboxes. Like:

<input type="checkbox" name="approved[]" value="1" /> 1 <br/>
<input type="checkbox" name="approved[]" value="2" /> 2 <br/>
<input type="checkbox" name="approved[]" value="3" /> 3 <br/>
<input type="checkbox" name="approved[]" value="4" /> 4 <br/>

Aaron

On 12/13/05, Owen Jacobson <ojacobson(at)osl(dot)com> wrote:
>
> 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
>
> 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.
>
> > 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
>
> 1. No need for ORDER BY here, we're not doing anything user-visible or
> order-dependent with the data, so it's a waste of CPU time.
> 2. You're not using new_sample for anything, just retrieving it.
>
> > UPDATE message_table SET status = new_status
> > WHERE mid = approved;
>
> Consider that you can only pass a single value to an INTEGER parameter
> ("approved"); this will repeatedly update a single entry where mid =
> approved. You could simplify the function as written to
>
> CREATE OR REPLACE FUNCTION update_messages (INTEGER) RETURNS VOID AS $$
> UPDATE message_table SET status = 'A' WHERE mid = $1;
> $$ LANGUAGE SQL;
>
> > 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"/>);
>
> I'm not familiar with Cocoon, but I'd expect that to return only the first
> of the "approved" values from the HTTP request. If you add logging to the
> stored function (RAISE NOTICE 'approved: %', approved; near the start of the
> function, for instance) and tell PostgreSQL to store the logs, you can see
> what values your function is actually being called with.
>
> What you really want to do is begin a transaction, loop over all the
> values of approved present in the form data and call (the rewritten version
> of) update_messages for each one, then commit the transaction.
>
> -Owen
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>