Skip site navigation (1) Skip section navigation (2)

Re: trigger that needs a PK

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 (view raw or flat)
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

In response to

Responses

pgsql-novice by date

Next:From: A. KretschmerDate: 2008-02-13 08:35:27
Subject: Re: trigger that needs a PK
Previous:From: A. KretschmerDate: 2008-02-13 07:32:24
Subject: Re: trigger that needs a PK

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group