Skip site navigation (1) Skip section navigation (2)

Re: Multi-row update w. plpgsql

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 (view raw or flat)
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


In response to

pgsql-novice by date

Next:From: Vishal KashyapDate: 2005-12-14 11:37:33
Subject: VB.Net with PostgreSQL
Previous:From: Raghunandan BapuramDate: 2005-12-14 10:21:01
Subject: Using bytea

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group