pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: David Stanaway <david@netventures.com.au>
To: pgsql-sql@postgresql.org
Subject: Passing a list of pairs to a PL/PGSQL function
Date: Tue, 22 Jan 2002 11:39:16 +1100
Message-ID: <76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au> (raw)

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




view thread (4+ messages)  latest in thread

Message-ID: <76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au>
Permalink:  ../76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au/
Also on:    postgresql.org/message-id/76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: david@netventures.com.au
  Subject: Re: Passing a list of pairs to a PL/PGSQL function
  In-Reply-To: <76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au>
  References: <76F6FE60-0ED0-11D6-A792-0003930FDAB2@netventures.com.au>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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