pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Passing a list of pairs to a PL/PGSQL function
4+ messages / 1 participants
[nested] [flat]

* Passing a list of pairs to a PL/PGSQL function
@ 2002-01-22 00:39  David Stanaway <david@netventures.com.au>
  0 siblings, 0 replies; 4+ messages in thread

From: David Stanaway @ 2002-01-22 00:39 UTC (permalink / raw)
  To: pgsql-sql

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 property WHERE prname = 'name';
INSERT INTO itemproperty (ipItemid,ipPropertyid,ipValue)
	SELECT currval('item_itemid_seq'),propertyid,'XP453-2421'
	FROM property WHERE 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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Passing a list of pairs to a PL/PGSQL function
@ 2002-01-22 02:56  David Stanaway <david@netventures.com.au>
  0 siblings, 0 replies; 4+ messages in thread

From: David Stanaway @ 2002-01-22 02:56 UTC (permalink / raw)
  To: chester c young <chestercyoung@yahoo.com>; pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Passing a list of pairs to a PL/PGSQL function
@ 2002-01-22 04:05  David Stanaway <david@netventures.com.au>
  0 siblings, 1 reply; 4+ messages in thread

From: David Stanaway @ 2002-01-22 04:05 UTC (permalink / raw)
  To: pgsql-sql


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@stanaway.net
Work: david@netventures.com.au




^ permalink  raw  reply  [nested|flat] 4+ messages in thread

* Re: Passing a list of pairs to a PL/PGSQL function
@ 2002-01-23 00:10  David Stanaway <david@netventures.com.au>
  parent: David Stanaway <david@netventures.com.au>
  0 siblings, 0 replies; 4+ messages in thread

From: David Stanaway @ 2002-01-23 00:10 UTC (permalink / raw)
  To: pgsql-sql


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




^ permalink  raw  reply  [nested|flat] 4+ messages in thread


end of thread, other threads:[~2002-01-23 00:10 UTC | newest]

Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-01-22 00:39 Passing a list of pairs to a PL/PGSQL function David Stanaway <david@netventures.com.au>
2002-01-22 02:56 Re: Passing a list of pairs to a PL/PGSQL function David Stanaway <david@netventures.com.au>
2002-01-22 04:05 Re: Passing a list of pairs to a PL/PGSQL function David Stanaway <david@netventures.com.au>
2002-01-23 00:10 ` David Stanaway <david@netventures.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox