From: | Oliver Elphick <olly(at)lfix(dot)co(dot)uk> |
---|---|
To: | danielhertz(at)shaw(dot)ca |
Cc: | "postgreSQL (Novice)" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Multi-row update w. plpgsql |
Date: | 2005-12-14 11:36:12 |
Message-ID: | 1134560172.4974.9.camel@linda.lfix.co.uk |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tue, 2005-12-13 at 15:57 -0700, 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?
...
>
> 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;
I can't see what you are looping for; you don't use new_sample inside
the loop so the loop is completely redundant. I think this will just
update the same row in the same way as many times as there are
new_sample rows.
> 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="accepted"/>);
>
> I'm using apache cocoon, which is why you see the variable placeholder:
> <xsp-request:get-parameter name="accepted"/>);
Since this is updating something, I assume that is putting in a single
value of "approved". Perhaps you need to do the loop in the application
program.
>
> Unfortunately, the function only updates the first value submitted (mid
> 1), and doesn't loop through the other two values submitted.
I suggest you put in some RAISE NOTICE statements so you can see what is
actually happening.
--
Oliver Elphick olly(at)lfix(dot)co(dot)uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
========================================
Do you want to know God? http://www.lfix.co.uk/knowing_god.html
From | Date | Subject | |
---|---|---|---|
Next Message | Vishal Kashyap | 2005-12-14 11:37:33 | VB.Net with PostgreSQL |
Previous Message | Raghunandan Bapuram | 2005-12-14 10:21:01 | Using bytea |