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

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