Thread: get a pkey/id back from/after an insert
Hello! Ok, so no question too simple... I'm using the perl dbi interface to postgres, and I'm wondering how people do the following: insert a row into a table, and then immediately get back the pkey/id of the row, so that I can then use this id to insert into another related table. How would this be done in pure SQL, and/or is there a similar technique to do this using the perl DBI and DBD::Pg? Say I have a table named "Foo", which contains a not null constrained foreign key to table "Bar": "Foo".bar_id. Before I can insert a new row into table "Foo", I thus need to have the related row in table "Bar", so I need to insert into "Bar" first, get the pkey, and then insert "Foo". Hope this makes sense - I'm sure it could be explained more presicely, and it seems like it would be a very common scenerio/pattern. Many thanks! Corey
On Monday 19 June 2006 13:40, Corey wrote: <snip> > I'm using the perl dbi interface to postgres, and I'm wondering how > people do the following: > > insert a row into a table, and then immediately get back the pkey/id > of the row, so that I can then use this id to insert into another related > table. > Ok, so of course, a few more minutes of searching/reading/googling, and I found the answer to my own question: currval()/nextval() Sorry for the noise! Cheers, Corey
> Ok, so of course, a few more minutes of searching/reading/googling, > and I found the answer to my own question: > > currval()/nextval() I am not sure what the best practice is for adding records to both a "parent" and "child" table. However, examples in the postgresql documentation demonstrate the use a view that joins the parent and child tables. Rules are used to propagate insert/update/delete statements on the view to the actual parent/child tables. In addition to this, inserts to the view can also be redirected using triggers. Once this is setup, I would expect that it would simplify your procedural code. See: http://www.postgresql.org/docs/8.1/interactive/rules.html I would be interested in seeing if there is a way to preform operations like this (without views) with just one Sql statement. Regards, Richard Broersma Jr.
On Monday 19 June 2006 15:00, Richard Broersma Jr wrote: > I am not sure what the best practice is for adding records to both a "parent" and "child" table. > However, examples in the postgresql documentation demonstrate the use a view that joins the parent > and child tables. > > Rules are used to propagate insert/update/delete statements on the view to the actual parent/child > tables. In addition to this, inserts to the view can also be redirected using triggers. > > Once this is setup, I would expect that it would simplify your procedural code. > > See: > http://www.postgresql.org/docs/8.1/interactive/rules.html > > > I would be interested in seeing if there is a way to preform operations like this (without views) > with just one Sql statement. Excellent - yes, this is ultimately the same thing I was hoping to achieve: a single operation, rather than two or more disparate actions. Thankyou for the extra info and the link, I will definitely check that out very soon ( right now I'm in crunch mode and just to get something working, even if it's less elegant ). I'll post to the list if I successfully manage to get the single-statement, non-view solution working. Thanks! Corey
> Excellent - yes, this is ultimately the same thing I was hoping to achieve: > a single operation, rather than two or more disparate actions. > > Thankyou for the extra info and the link, I will definitely check that out very > soon ( right now I'm in crunch mode and just to get something working, > even if it's less elegant ). I'll post to the list if I successfully manage to > get the single-statement, non-view solution working. Well for testing purposes it should be fine to explicitly call out currentval. But be careful if you are going to be using multiply connections at onces. I am not sure that currentval will give you what you want if another connection calls nextval first before you insert a record to your child table. Regards, Richard Broersma Jr.
On Mon, Jun 19, 2006 at 19:10:01 -0700, Richard Broersma Jr <rabroersma@yahoo.com> wrote: > Well for testing purposes it should be fine to explicitly call out currentval. But be careful if > you are going to be using multiply connections at onces. I am not sure that currentval will give > you what you want if another connection calls nextval first before you insert a record to your > child table. It will do what is expected. currval returns the last value allocated in your session. It wouldn't be much use if other connections could change the value out from under you.
> It will do what is expected. currval returns the last value allocated in > your session. It wouldn't be much use if other connections could change > the value out from under you. Bruno, Thanks for the clarification. Regards, Richard Broersma Jr.
On 6/20/06 12:34 PM, "Bruno Wolff III" <bruno@wolff.to> wrote: > On Mon, Jun 19, 2006 at 19:10:01 -0700, > Richard Broersma Jr <rabroersma@yahoo.com> wrote: >> Well for testing purposes it should be fine to explicitly call out >> currentval. But be careful if >> you are going to be using multiply connections at onces. I am not sure that >> currentval will give >> you what you want if another connection calls nextval first before you insert >> a record to your >> child table. > > It will do what is expected. currval returns the last value allocated in > your session. It wouldn't be much use if other connections could change > the value out from under you. While this may be obvious, if you are coming from other database systems, it may come as a surprise that under the postgres system, you do not necessarily get a "gapless" id. There may be gaps (1,2,3,7,8,9). Once a value is allocated, it is not reused. This isn't at all important, but as I said, it may surprise some. Sean