Re: insert serial numbers - Mailing list pgsql-general

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