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
