Обсуждение: Passing a list of pairs to a PL/PGSQL function
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
On Tuesday, January 22, 2002, at 12:47 PM, chester c young wrote: > might be missing something here, but why don't you truncate the table, > insert from the list, and you're done. Thats what I do at the moment (But not in a function), however I am burning through the oid's and itemproperty_itempropertyid_seq. Its not really a big deal I know. I just thought that it would be better to update existing records where possible. Asuming I do make a function edititemproperty(int,text[][]) What would be the best structure to pass in pairs of values for the insert? With text[][], I am not sure how to expand that into a set of pairs for an INSERT INTO ... SELECT ...; ============================== David Stanaway Personal: david@stanaway.net Work: david@netventures.com.au
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
On Tuesday, January 22, 2002, at 03:05 PM, David Stanaway wrote:
>
> 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';
This functions works:
Is there a better way?
CREATE FUNCTION edititemproperty(int,text[][])
RETURNS int
AS 'DECLARE itemid ALIAS FOR $1; pairs ALIAS FOR $2; result int; rc int; i
int; BEGIN result := 0; i := 1; DELETE FROM itemproperty WHERE ipItemid = itemid; WHILE
pairs[i][1]!= '''' LOOP INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue) SELECT
itemid,propertyid, pairs[i][2] FROM property
WHERE prName = pairs[i][1]; GET DIAGNOSTICS rc = ROW_COUNT; result := result + rc; i
:=i + 1; END LOOP; 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@stanaway.net
> Work: david@netventures.com.au
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>
>
--
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