insert serial numbers

Поиск
Список
Период
Сортировка
От Albert Vernon Smith
Тема insert serial numbers
Дата
Msg-id 4AA7EACA-4F27-4F3E-B272-5E5470892405@absentia.com
обсуждение исходный текст
Ответы Re: insert serial numbers  (Albert Vernon Smith <contact1@absentia.com>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Sean Davis
Дата:
Сообщение: Re: inserting many rows
Следующее
От: Manfred Koizar
Дата:
Сообщение: Re: POSTGRES DB 3 800 000 rows table, speed up?