Received: from dealer.netventures.com.au (dnet137107-1.gw.connect.com.au [210.8.15.221] (may be forged)) by postgresql.org (8.11.3/8.11.4) with ESMTP id g0M0dNl84676 for ; Mon, 21 Jan 2002 19:39:23 -0500 (EST) (envelope-from david@netventures.com.au) Received: from ciderbox.in.netventures.com.au (net137107-2.gw.connect.com.au [210.9.198.119]) by dealer.netventures.com.au (Postfix) with ESMTP id 68DC01A5DC5 for ; Tue, 22 Jan 2002 11:39:15 +1100 (EST) Date: Tue, 22 Jan 2002 11:39:16 +1100 Mime-Version: 1.0 (Apple Message framework v480) Content-Type: text/plain; charset=US-ASCII; format=flowed Subject: Passing a list of pairs to a PL/PGSQL function From: David Stanaway To: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit Message-Id: <76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au> X-Mailer: Apple Mail (2.480) X-Archive-Number: 200201/250 X-Sequence-Number: 6334 Hi, I am scratching my head at a neat way of doing an update function for my db. -- Here is a sketch schema CREATE TABLE item ( itemid serial, PRIMARY KEY (itemid) ); CREATE TABLE property ( propertyid serial, prName text, UNIQUE(prName), PRIMARY KEY(propertyid) ); CREATE TABLE itemproperty ( itempropertyid serial, ipItemid int REFERENCES item(itemid), ipPropertyid int REFERENCES property(propertyid), ipValue text, UNIQUE(ipItemid,ipPropertyid), PRIMARY KEY(itempropertyid) ); -- Sample data INSERT INTO property (prname) VALUES('name'); INSERT INTO property (prname) VALUES('rank'); INSERT INTO property (prname) VALUES('serial'); INSERT INTO item (itemid) VALUES(nextval('item_itemid_seq')); INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue) SELECT currval('item_itemid_seq'),propertyid,'John Wayne' FROM property WHERE prname = 'name'; INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue) SELECT currval('item_itemid_seq'),propertyid,'XP453-2421' FROM property WHERE prname = 'serial'; I want to write a function that will update itemproperty with a new set of property name/value pairs. Any new property name's that appear should be inserted, any old propery names that no longer appear should be deleted, and any existing values should be updated. I am not quite sure where to start. If I have an update function that takes (int,text,text) as args where $1 is itemid, $2 is a list of comer separated prnames and $3 is a list of comer separated ipvalues, then I can do the Delete okay, but the insert and update become difficult. If I have an update function that takes (int,text[][]) as args where $2 is an array of prname,ipvalue pairs then the update is easy, but the delete and insert become harder I think. Which route is more promising? -- Best Regards David Stanaway ================================ Technology Manager Australia's Premier Internet Broadcasters Phone: +612 9357 1699 Fax: +612 9357 1169 Web: http://www.netventures.com.au Support: support@netventures.com.au ================================ The Inspire Foundation is proudly supported by Net Ventures through the provision of streaming solutions for it's national centres. The Inspire Foundation is an Internet-based foundation that inspires young people to help themselves, get involved and get online. Please visit Inspire at http://www.inspire.org.au