Re: Does INSERT inserts always at the end ?

A long time ago, in a galaxy far, far away, florence.henry@obspm.fr (Florence HENRY) wrote:
> well, almost everything is in the subject !
>
> I have to fill 2 tables (more complicated than in the example !):
>
> CREATE TABLE A (
>    id serial primary key,
>    foo text);
>
> CREATE TABLE B (
>    id serial references A,
>    bar text);
>
> I fill A with :
>    INSERT into A VALUES (DEFAULT, "toto");
>
> Then I need to retreive the "A.id" that was given to A, in order to give it
> to B.id. If I was doing this by hand, it would have been quite easy, but I'm
> doing this with a script.
>
> So, if I make a SELECT id from A; and take the last row, will it *always*
> be the row that I've just inserted.

This won't happen "implicitly."

tutorial=# CREATE TABLE A (id serial primary key,foo text);
NOTICE:  CREATE TABLE will create implicit sequence "a_id_seq" for "serial" column "a.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a"
CREATE TABLE
tutorial=# drop table b;
DROP TABLE
tutorial=# CREATE TABLE B (id serial references A,bar text);
NOTICE:  CREATE TABLE will create implicit sequence "b_id_seq" for "serial" column "b.id"
CREATE TABLE

I can suggest a couple of ways you might do this:

 1.  So long as the inserts take place within the scope of the same
     transaction on the same connection, it would be safe to create
     the B entry via currval for the sequence.

tutorial=# begin;
BEGIN
tutorial=# insert into a (foo) values ('toto');
INSERT 2587831 1
tutorial=# insert into b (id, bar) values (currval('a_id_seq'), 'yellow brick road');
INSERT 2587832 1
tutorial=# commit;
COMMIT

Note that if you don't enclose it in BEGIN/COMMIT, the insert into b
could pick up on changes from other concurrent sessions.

 2.  You might create a stored procedure that creates both entries,
     using currval() behind your back.

 [assuming suitably-created funciton...

  select make_foo_bar ('toto', 'yellow brick road');

This won't work so well if there are to be multiple associations; if
that be the case, you'd want to have an explicit external primary key,
and do something like:

  select create_a ('toto', 'key-for-toto');
  select link_b_to_a ('key-for-toto', 'yellow brick road');
  select link_b_to_a ('key-for-toto', 'click, click');
  select link_b_to_a ('key-for-toto', 'ruby shoes');

There's no magic there; the stored procedure link_b_to_a() would look
up the ID number for 'key-for-toto' in table A.
--
(reverse (concatenate 'string "moc.enworbbc" "@" "enworbbc"))
http://cbbrowne.com/info/spiritual.html
Rules of the Evil Overlord #54. "I will not strike a bargain with a
demonic being then attempt to double-cross it simply because I feel
like being contrary." <http://www.eviloverlord.com/>