Realized. It was just a dummy-example, and I made a poor choice for
my example. Replaced reserved word, but the question still stands.
-a
Rewritten info without the reserved word:
CREATE TABLE "one" (
"one_id" BIGSERIAL,
"mytext" text NOT NULL,
CONSTRAINT "iu_mytext" UNIQUE (mytext)
)
CREATE TABLE "two" (
"two_id" BIGSERIAL,
"mytext" text NOT NULL,
"one_id" bigint,
CONSTRAINT "$1" FOREIGN KEY (mytext) REFERENCES one(mytext) ON
UPDATE SET
NULL
)
CREATE RULE two_insert AS ON INSERT TO two DO UPDATE two SET one_id =
(SELECT one.one_id FROM one WHERE (new.mytext = one.mytext)) WHERE
(new.two_id = two.two_id);
CREATE RULE two_insert AS ON INSERT TO two DO UPDATE two SET one_id =
(SELECT one.one_id FROM one WHERE (new.mytext = one.mytext)) WHERE
(new.mytext
= two.mytext);
On 3.1.2006, at 16:07, codeWarrior wrote:
Don't use reserved words for column names.
"Albert Vernon Smith" <contact1@xxxxxxxxxxxx> wrote in message
news:4AA7EACA-4F27-4F3E-B272-5E5470892405@xxxxxxxxxxxxxxx
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
---------------------------(end of
broadcast)---------------------------
TIP 6: explain analyze is your friend
