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