From: Aarni Ruuhimäki <aarni@kymi.com>
To: danielhertz@shaw.ca
Cc: pgsql-sql@postgresql.org
Subject: Re: Multi-row update w. plpgsql function
Date: Wed, 14 Dec 2005 10:48:54 +0200
Message-ID: <200512141048.54034.aarni@kymi.com> (raw)
In-Reply-To: <439F527F.4050204@shaw.ca>
References: <439F527F.4050204@shaw.ca>
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
--------------
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aarni@kymi.com, danielhertz@shaw.ca
Subject: Re: Multi-row update w. plpgsql function
In-Reply-To: <200512141048.54034.aarni@kymi.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox