From: David Stanaway <david@netventures.com.au>
To: pgsql-sql@postgresql.org
Subject: Re: Passing a list of pairs to a PL/PGSQL function
Date: Tue, 22 Jan 2002 15:05:45 +1100
Message-ID: <4F290460-0EED-11D6-A792-0003930FDAB2@netventures.com.au> (raw)
In-Reply-To: <20020122031709.45713.qmail@web12703.mail.yahoo.com>
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
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 using the --to and --cc options:
reply via email
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: <4F290460-0EED-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