pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMulti-row update w. plpgsql function
6+ messages / 5 participants
[nested] [flat]
* Multi-row update w. plpgsql function
@ 2005-12-13 23:00 Daniel Hertz <danielhertz@shaw.ca>
2005-12-14 08:48 ` Re: Multi-row update w. plpgsql function Aarni Ruuhimäki <aarni@kymi.com>
0 siblings, 1 reply; 6+ messages in thread
From: Daniel Hertz @ 2005-12-13 23:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi-row update w. plpgsql function
2005-12-13 23:00 Multi-row update w. plpgsql function Daniel Hertz <danielhertz@shaw.ca>
@ 2005-12-14 08:48 ` Aarni Ruuhimäki <aarni@kymi.com>
0 siblings, 0 replies; 6+ messages in thread
From: Aarni Ruuhimäki @ 2005-12-14 08:48 UTC (permalink / raw)
To: danielhertz@shaw.ca; +Cc: pgsql-sql
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
--------------
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi-row update w. plpgsql function
@ 2005-12-14 00:05 Owen Jacobson <ojacobson@osl.com>
2005-12-14 02:04 ` Re: Multi-row update w. plpgsql function Aaron Koning <aaronkoning@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Owen Jacobson @ 2005-12-14 00:05 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi-row update w. plpgsql function
2005-12-14 00:05 Re: Multi-row update w. plpgsql function Owen Jacobson <ojacobson@osl.com>
@ 2005-12-14 02:04 ` Aaron Koning <aaronkoning@gmail.com>
2005-12-15 00:46 ` Re: Multi-row update w. plpgsql function Daniel Hertz <danielhertz@shaw.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Aaron Koning @ 2005-12-14 02:04 UTC (permalink / raw)
To: ; +Cc: pgsql-sql
Owen makes a good point. Check that you are using the [] in the HTML input
variable for the checkboxes. Like:
<input type="checkbox" name="approved[]" value="1" /> 1 <br/>
<input type="checkbox" name="approved[]" value="2" /> 2 <br/>
<input type="checkbox" name="approved[]" value="3" /> 3 <br/>
<input type="checkbox" name="approved[]" value="4" /> 4 <br/>
Aaron
On 12/13/05, Owen Jacobson <ojacobson@osl.com> wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi-row update w. plpgsql function
2005-12-14 00:05 Re: Multi-row update w. plpgsql function Owen Jacobson <ojacobson@osl.com>
2005-12-14 02:04 ` Re: Multi-row update w. plpgsql function Aaron Koning <aaronkoning@gmail.com>
@ 2005-12-15 00:46 ` Daniel Hertz <danielhertz@shaw.ca>
0 siblings, 0 replies; 6+ messages in thread
From: Daniel Hertz @ 2005-12-15 00:46 UTC (permalink / raw)
To: pgsql-sql
Aaron Koning wrote:
> Owen makes a good point. Check that you are using the [] in the HTML input
> variable for the checkboxes. Like:
>
> <input type="checkbox" name="approved[]" value="1" /> 1 <br/>
> <input type="checkbox" name="approved[]" value="2" /> 2 <br/>
> <input type="checkbox" name="approved[]" value="3" /> 3 <br/>
> <input type="checkbox" name="approved[]" value="4" /> 4 <br/>
>
> Aaron
>
> On 12/13/05, Owen Jacobson <ojacobson@osl.com> wr
>> 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
Thank you all, so much, for taking the time to help me out. Especially
as a beginner, where coding IS rocket science.
On the general board, Aaron mentioned:
UPDATE message_table SET status = 'A' WHERE mid IN (1,2,3);
which seems very succinct and economical. I'm gonna have a go at parsing the query string using XSLT, substituting the variable for:
UPDATE message_table SET status = 'A' WHERE mid IN ($query_values_here);
Again, thanks for the help,
Daniel
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Multi-row update w. plpgsql function
@ 2005-12-14 09:15 Magnus Hagander <mha@sollentuna.net>
0 siblings, 0 replies; 6+ messages in thread
From: Magnus Hagander @ 2005-12-14 09:15 UTC (permalink / raw)
To: Owen Jacobson <ojacobson@osl.com>; pgsql-sql
> > 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.
It would probably be even more efficient to do:
UPDATE message_table SET status = 'A' WHERE mid IN (1,3,4)
and then use client code to generate the comma-separated list of ids.
(Don't forget to make sure they are actual integers so you don't get a
sql injection from it - I don't think parametrised queries can deal with
comma lists)
//Magnus
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2005-12-15 00:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-13 23:00 Multi-row update w. plpgsql function Daniel Hertz <danielhertz@shaw.ca>
2005-12-14 08:48 ` Aarni Ruuhimäki <aarni@kymi.com>
2005-12-14 00:05 Re: Multi-row update w. plpgsql function Owen Jacobson <ojacobson@osl.com>
2005-12-14 02:04 ` Aaron Koning <aaronkoning@gmail.com>
2005-12-15 00:46 ` Daniel Hertz <danielhertz@shaw.ca>
2005-12-14 09:15 Re: Multi-row update w. plpgsql function Magnus Hagander <mha@sollentuna.net>
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