Re: insert serial numbers

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);
>
> --
>
> Thanks for any help,
> -albert
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster