Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Subject: Re: Multi-row update w. plpgsql function
Date: Tue, 13 Dec 2005 16:05:09 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC18FFE2@nelson.osl.com>
Thread-Topic: [SQL] Multi-row update w. plpgsql function
Thread-Index: AcYAOeWIobp339hWSDC5rLExk8oIiAABiw1Q
From: "Owen Jacobson" <ojacobson@osl.com>
To: "postgreSQL (SQL)" <pgsql-sql@postgresql.org>

Daniel Hertz wrote:

> Given a set of checkbox values that are submitted through an=20
> html form,=20
> how do you loop through the submitted values to update more=20
> than one row=20
> in a table?
>=20
> Imagine a table called 'message_table':
>=20
> mid | message | status
> ----+---------+-------
>   1  |  Text1   |  H
>   2  |  Text2   |  H
>   3  |  Text3   |  H
>   4  |  Text4   |  H
> =20
> A web page presents the user with all messages flagged with 'H'. User=20
> checks messages 1,3 and 4 and submits form.
> (i.e. approved=3D1&approved=3D3&approved=3D4)
> =20
> After performing postgreSQL update, rows 1, 3 and 4 would be=20
> updated to:
> =20
> mid | message | status
> ----+---------+-------
>   1  |  Text1   |  A
>   2  |  Text2   |  H
>   3  |  Text3   |  A
>   4  |  Text4   |  A

BEGIN;
UPDATE message_table SET status =3D 'A' WHERE mid =3D 1;
UPDATE message_table SET status =3D 'A' WHERE mid =3D 3;
UPDATE message_table SET status =3D 'A' WHERE mid =3D 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:
>=20
> CREATE OR REPLACE FUNCTION update_messages(approved integer) RETURNS=20
> integer AS
> $body$
> DECLARE
>  new_status varchar;
>  new_sample record;
>=20
> BEGIN
>  new_status :=3D 'A';
>=20
>  FOR new_sample IN SELECT * FROM message_table WHERE=20
> status=3D'H' ORDER BY=20
> 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 =3D new_status
>   WHERE mid =3D approved;

Consider that you can only pass a single value to an INTEGER parameter =
("approved"); this will repeatedly update a single entry where mid =3D =
approved.  You could simplify the function as written to

CREATE OR REPLACE FUNCTION update_messages (INTEGER) RETURNS VOID AS $$
  UPDATE message_table SET status =3D 'A' WHERE mid =3D $1;
$$ LANGUAGE SQL;

>  END LOOP;
>=20
>  RETURN 1;
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
> =20
> I call the function with:
> SELECT update_messages(<xsp-request:get-parameter =
name=3D"approved"/>);
>=20
> I'm using apache cocoon, which is why you see the variable=20
> placeholder:=20
> <xsp-request:get-parameter name=3D"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