I figured it out myself. Not TOO difficult. I was just having a
hard time wading through the documentation before. Giving the answer
out here, just in case any one else wants to see the solution (not
using reserved words ;-)).
1. Made function:
CREATE FUNCTION "return_one_id" () RETURNS "trigger" AS '
DECLARE
my_id bigint;
BEGIN
select into my_id one_id from one where one_text=NEW.one_text;
NEW.one_id := my_id;
return NEW;
END;
' LANGUAGE "plpgsql"
2. Made trigger:
CREATE TRIGGER return_one_id BEFORE INSERT OR UPDATE ON two FOR EACH
ROW EXECUTE PROCEDURE return_one_id()
Voila!
-albert
On 3.1.2006, at 14:36, Albert Vernon Smith wrote:
I have two tables, listed as below. I'm inserting values for
"text" into table "two" (which must already exist as "text" values
in table "one"). When I do that, I'd like to also insert the
associated "one_id" value from table "one" into the field
"two.one_id". How is best to go about that? I imagine this would
be best be done with a trigger rather than a rule, but I don't know
enough on how to go about that. Can someone help point me in the
right direction. (I did try it with rules as listed below, but the
serial value increments, so the approach doesn't work on a single
row.)
--
My tables:
CREATE TABLE "one" (
"one_id" BIGSERIAL,
"text" text NOT NULL,
CONSTRAINT "iu_text" UNIQUE (text)
)
CREATE TABLE "two" (
"two_id" BIGSERIAL,
"text" text NOT NULL,
"one_id" bigint,
CONSTRAINT "$1" FOREIGN KEY (text) REFERENCES one(text) ON
UPDATE SET NULL
)
--
My failed rule approaches:
CREATE RULE two_insert AS ON INSERT TO two DO UPDATE two SET one_id
= (SELECT one.one_id FROM one WHERE (new.text = one.text)) WHERE
(new.two_id = two.two_id);
The following does work, but it updates all rows with the same
text. I'd rather be more efficient, and only work with the current
row.:
CREATE RULE two_insert AS ON INSERT TO two DO UPDATE two SET one_id
= (SELECT one.one_id FROM one WHERE (new.text = one.text)) WHERE
(new.text = two.text);
--
Thanks for any help,
-albert
---------------------------(end of
broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
