Re: Passing a list of pairs to a PL/PGSQL function
От | David Stanaway |
---|---|
Тема | Re: Passing a list of pairs to a PL/PGSQL function |
Дата | |
Msg-id | 4F290460-0EED-11D6-A792-0003930FDAB2@netventures.com.au обсуждение исходный текст |
Ответ на | Passing a list of pairs to a PL/PGSQL function (David Stanaway <david@netventures.com.au>) |
Ответы |
Re: Passing a list of pairs to a PL/PGSQL function
|
Список | pgsql-sql |
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 propertyWHERE prname = 'name'; INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue)SELECT currval('item_itemid_seq'),propertyid,'XP453-2421'FROM propertyWHERE prname = 'serial'; ============================== David Stanaway Personal: david@stanaway.net Work: david@netventures.com.au
В списке pgsql-sql по дате отправления: