Re: Multi-row update w. plpgsql

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