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
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