Thread: insert serial numbers
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
Don't use reserved words for column names. "Albert Vernon Smith" <contact1@absentia.com> wrote in message news:4AA7EACA-4F27-4F3E-B272-5E5470892405@absentia.com... >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 >
Realized. It was just a dummy-example, and I made a poor choice for
my example. Replaced reserved word, but the question still stands.
-a
Rewritten info without the reserved word:
CREATE TABLE "one" (
"one_id" BIGSERIAL,
"mytext" text NOT NULL,
CONSTRAINT "iu_mytext" UNIQUE (mytext)
)
CREATE TABLE "two" (
"two_id" BIGSERIAL,
"mytext" text NOT NULL,
"one_id" bigint,
CONSTRAINT "$1" FOREIGN KEY (mytext) REFERENCES one(mytext) ON
UPDATE SET
NULL
)
CREATE RULE two_insert AS ON INSERT TO two DO UPDATE two SET one_id =
(SELECT one.one_id FROM one WHERE (new.mytext = one.mytext)) WHERE
(new.two_id = two.two_id);
CREATE RULE two_insert AS ON INSERT TO two DO UPDATE two SET one_id =
(SELECT one.one_id FROM one WHERE (new.mytext = one.mytext)) WHERE
(new.mytext
= two.mytext);
On 3.1.2006, at 16:07, codeWarrior wrote:
> Don't use reserved words for column names.
>
>
> "Albert Vernon Smith" <contact1@absentia.com> wrote in message
> news:4AA7EACA-4F27-4F3E-B272-5E5470892405@absentia.com...
>> 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
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
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
Albert Vernon Smith wrote:
> CREATE FUNCTION "return_one_id" () RETURNS "trigger" AS '
Is there any reason you force your identifiers to be case sensitive?
> CREATE TRIGGER return_one_id BEFORE INSERT OR UPDATE ON two FOR EACH
>> 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,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
//Showing your Vision to the World//
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