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