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 g0N09sl45176 for ; Tue, 22 Jan 2002 19:09:55 -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 2E4431A5DC5 for ; Wed, 23 Jan 2002 11:09:56 +1100 (EST) Date: Wed, 23 Jan 2002 11:10:05 +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: <4F290460-0EED-11D6-A792-0003930FDAB2@netventures.com.au> Message-Id: <8DB64CA9-0F95-11D6-8E39-0003930FDAB2@netventures.com.au> X-Mailer: Apple Mail (2.480) X-Archive-Number: 200201/259 X-Sequence-Number: 6343 On Tuesday, January 22, 2002, at 03:05 PM, David Stanaway wrote: > > 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'; This functions works: Is there a better way? CREATE FUNCTION edititemproperty(int,text[][]) RETURNS int AS 'DECLARE itemid ALIAS FOR $1; pairs ALIAS FOR $2; result int; rc int; i int; BEGIN result := 0; i := 1; DELETE FROM itemproperty WHERE ipItemid = itemid; WHILE pairs[i][1] != '''' LOOP INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue) SELECT itemid, propertyid, pairs[i][2] FROM property WHERE prName = pairs[i][1]; GET DIAGNOSTICS rc = ROW_COUNT; result := result + rc; i := i + 1; END LOOP; 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 > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > > -- 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