Re: Passing a list of pairs to a PL/PGSQL function - Mailing list pgsql-sql
From | David Stanaway |
---|---|
Subject | Re: Passing a list of pairs to a PL/PGSQL function |
Date | |
Msg-id | 8DB64CA9-0F95-11D6-8E39-0003930FDAB2@netventures.com.au Whole thread Raw |
In response to | Re: Passing a list of pairs to a PL/PGSQL function (David Stanaway <david@netventures.com.au>) |
List | pgsql-sql |
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