Re: insert serial numbers

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(at)absentia(dot)com> wrote in message
> news:4AA7EACA-4F27-4F3E-B272-5E5470892405(at)absentia(dot)com(dot)(dot)(dot)
>> 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