Re: Passing a list of pairs to a PL/PGSQL function

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 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(at)stanaway(dot)net
Work: david(at)netventures(dot)com(dot)au