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 g0M45rl24794 for ; Mon, 21 Jan 2002 23:05:53 -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 E33661A64BB for ; Tue, 22 Jan 2002 15:05:56 +1100 (EST) Date: Tue, 22 Jan 2002 15:05:45 +1100 Subject: Re: Passing a list of pairs to a PL/PGSQL function Content-Type: text/plain; charset=US-ASCII; format=flowed Mime-Version: 1.0 (Apple Message framework v480) From: David Stanaway To: pgsql-sql@postgresql.org Content-Transfer-Encoding: 7bit In-Reply-To: <20020122031709.45713.qmail@web12703.mail.yahoo.com> Message-Id: <4F290460-0EED-11D6-A792-0003930FDAB2@netventures.com.au> X-Mailer: Apple Mail (2.480) X-Archive-Number: 200201/253 X-Sequence-Number: 6337 On Tuesday, January 22, 2002, at 02:17 PM, chester c young wrote: > What kind of conservationist are you - trying to save oids and > sequences? What about CPU cycles? To say nothing of brain cycles! Go > save some kangaroos! :) HeHe, okay okay, I give up on the conservation of oids and sequences. I still have the problem of passing the set of pairs to the function that will do something like this: CREATE FUNCTION edititemproperty(int,text[][]) RETURN int AS 'DECLARE itemid ALIAS FOR $1; pairs ALIAS FOR $2; result int; BEGIN DELETE FROM itemproperty WHERE ipItemid = itemid; INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue) SELECT itemid, propertyid, pairs[:][2] FROM property WHERE prName = pairs[:][1]; GET DIAGNOSTICS result = ROW_COUNT; RETURN result; END;' LANGUAGE 'plpgsql'; But my array syntax is wrong ... Here is my schema from earlier -- 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'; ============================== David Stanaway Personal: david@stanaway.net Work: david@netventures.com.au