Re: insert serial numbers

Albert Vernon Smith presumably uttered the following on 01/03/06 13:36:
> I figured it out myself. Not TOO difficult. I was just having a hard
> time wading through the documentation before. Giving the answer out
> here, just in case any one else wants to see the solution (not using
> reserved words ;-)).
>
> 1. Made function:
>
> CREATE FUNCTION "return_one_id" () RETURNS "trigger" AS '
> DECLARE
> my_id bigint;
> BEGIN
> select into my_id one_id from one where one_text=NEW.one_text;
> NEW.one_id := my_id;
> return NEW;
> END;
> ' LANGUAGE "plpgsql"
>
> 2. Made trigger:
>
> CREATE TRIGGER return_one_id BEFORE INSERT OR UPDATE ON two FOR EACH
> ROW EXECUTE PROCEDURE return_one_id()
>
> Voila!
>
> -albert
>
>
> On 3.1.2006, at 14:36, Albert Vernon Smith wrote:
>
>> 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);
>>

The reason the rule failed, by the way is that the rule gets expanded
(like a macro). Whereas in a function "new.two_id" actually uses the
value of two_id that is about to be inserted into the table, in a rule
situation "new.two_id" gets expanded into its definition, namely
nextval(sequence name) so the comparison is between a two_id that really
doesn't exist in the table (its value is created by the rule after the
two_id that gets created on insert) which will always result in a non-match.

I had this same issue with a pair of table I had where I wanted to
update a customer information table with a live customer number that was
created in a different table. As in your case, a trigger solved my
situation.

Sven