From: | David Stanaway <david(at)netventures(dot)com(dot)au> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Passing a list of pairs to a PL/PGSQL function |
Date: | 2002-01-22 00:39:16 |
Message-ID: | 76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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 property WHERE prname = 'name';
INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue)
SELECT currval('item_itemid_seq'),propertyid,'XP453-2421'
FROM property WHERE 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(at)netventures(dot)com(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | David Stanaway | 2002-01-22 02:56:04 | Re: Passing a list of pairs to a PL/PGSQL function |
Previous Message | Ross J. Reedstrom | 2002-01-21 23:21:17 | Re: importing data from Filemaker: weird newline characters |