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

Multi-row update w. plpgsql

From: Daniel Hertz <danielhertz(at)shaw(dot)ca>
To: "postgreSQL (Novice)" <pgsql-novice(at)postgresql(dot)org>
Subject: Multi-row update w. plpgsql
Date: 2005-12-13 22:57:28
Message-ID: 439F51D8.4020002@shaw.ca (view raw or flat)
Thread:
Lists: pgsql-novice
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="accepted"/>);

I'm using apache cocoon, which is why you see the variable placeholder: 
<xsp-request:get-parameter name="accepted"/>);

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

Responses

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-12-13 23:10:05
Subject: Table Structure Advice
Previous:From: manjusri schuitDate: 2005-12-13 21:39:51
Subject: Re: What is the Best Way to Learn PL/pgSQL?

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