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
>