Re: insert serial numbers - Mailing list pgsql-general

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@absentia.com> wrote in message
> news:4AA7EACA-4F27-4F3E-B272-5E5470892405@absentia.com...
>> 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