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 >
