From: Owen Jacobson <ojacobson@osl.com>
To: postgreSQL (SQL) <pgsql-sql@postgresql.org>
Subject: Re: Multi-row update w. plpgsql function
Date: Tue, 13 Dec 2005 16:05:09 -0800
Message-ID: <144D12D7DD4EC04F99241498BB4EEDCC18FFE2@nelson.osl.com> (raw)
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
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: ojacobson@osl.com
Subject: Re: Multi-row update w. plpgsql function
In-Reply-To: <144D12D7DD4EC04F99241498BB4EEDCC18FFE2@nelson.osl.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