From: | johnf <jfabiani(at)yolo(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: trigger that needs a PK |
Date: | 2008-02-13 07:46:41 |
Message-ID: | 200802122346.41975.jfabiani@yolo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
On Tuesday 12 February 2008 11:32:24 pm A. Kretschmer wrote:
> am Tue, dem 12.02.2008, um 23:20:46 -0800 mailte johnf folgendes:
> > Hi,
> > I'm sure this question has been ask before but I could not find anything
> > on google. I most likely did not enter the right text into the google
> > search.
> >
> > I have a parent table that requires that an insert into a child table
> > happen. The problem is I can not determine what the parent pk is for the
> > insert into the child because it hasn't happen yet - if I set the trigger
> > to before insert. So I guess I need something that works with after
> > insert into the parent so the pkid can be created.
>
> You don't need a TRIGGER, you need currval(). I will explain with an
> example:
>
> test=# create table master (id serial primary key);
> NOTICE: CREATE TABLE will create implicit sequence "master_id_seq" for
> serial column "master.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "master_pkey" for table "master"
> CREATE TABLE
> test=*# create table slave (fk int references master);
> CREATE TABLE
> test=*# insert into master (id) values(default);
> INSERT 0 1
> test=*# insert into master (id) values(default);
> INSERT 0 1
> test=*# insert into master (id) values(default);
> INSERT 0 1
> test=*# insert into slave (fk) values(currval('master_id_seq'));
> INSERT 0 1
> test=*# select * from master;
> id
> ----
> 1
> 2
> 3
> (3 rows)
>
> test=*# select * from slave;
> fk
> ----
> 3
> (1 row)
>
>
> http://www.postgresql.org/docs/current/static/functions-sequence.html
>
>
> HTH, Andreas
But how do I do automaticly???
You code implies that I just string two inserts together. I was hoping to use
rules or some other way to do it automaticly.
--
John Fabiani
From | Date | Subject | |
---|---|---|---|
Next Message | A. Kretschmer | 2008-02-13 08:35:27 | Re: trigger that needs a PK |
Previous Message | A. Kretschmer | 2008-02-13 07:32:24 | Re: trigger that needs a PK |