From: Aarni =?iso-8859-1?q?Ruuhim=E4ki?= <aarni@kymi.com>
Reply-To: aarni@kymi.com
Organization: KYMI.com
To: danielhertz@shaw.ca
Subject: Re: Multi-row update w. plpgsql function
Date: Wed, 14 Dec 2005 10:48:54 +0200
User-Agent: KMail/1.6.2
References: <439F527F.4050204@shaw.ca>
In-Reply-To: <439F527F.4050204@shaw.ca>
Cc: <pgsql-sql@postgresql.org>
MIME-Version: 1.0
Content-Disposition: inline
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Message-Id: <200512141048.54034.aarni@kymi.com>

Hi,

If your checkboxes are like

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

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

Then you can just say

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

Not a function though.

BR,

Aarni

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

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

testing=3D#

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=3D1&approved=3D3&approved=3D4)
>
> 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 :=3D 'A';
>
>  FOR new_sample IN SELECT * FROM message_table WHERE status=3D'H' ORDER BY
> mid LOOP
>   UPDATE message_table SET status =3D new_status
>   WHERE mid =3D 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=3D"approved"/>);
>
> I'm using apache cocoon, which is why you see the variable placeholder:
> <xsp-request:get-parameter name=3D"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

=2D-=20
Aarni Ruuhim=E4ki
=2D-------------
This is a bugfree broadcast to you
from **Kmail**
on **Fedora Core** linux system
=2D-------------