Re: trigger that needs a PK

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