Re: inputs for pg_get_id() function? - Mailing list pgsql-novice

 > My current understanding of postgres is that I need to know what the
id is *before* I do the insert into the parent table.

you can use curval('sequence_name') to get the id of the newly assigned
row.  This function actually isolates the current value for connection
to the database.  This way, your current value is not confused with
someone elses current value.  Also, if you are using PostgreSQL 8.2,
there is a newly added PostgreSQL specific extenstion that you can use:

INSERT INTO your_table ( pkey, col1, col2 ) values ( DEFAULT, 1,
'hello') RETURNING pkey;

This statement will perform the insert and return your primary key in
one statement.  check out the last example from:
http://www.postgresql.org/docs/8.2/interactive/sql-insert.html#AEN52425

> But my understanding of Postgres, from earlier conversations on this
> mailing list, is that using something like "SELECT last_value FROM
> sequence" doesn't mean that I will get the id from the insert I just
> did -- instead I will get the highest value from the sequence. In a
> high traffic situation, there may have been an insert that happened
> just after mine, and I would get that id instead of the one that
> resulted from my insert. That is, the sequence is subject to race
> conditions.
>
If you use nextval() you are protected from these kind of collisions. No
matter how many concurrent users you have hammering a way at your
database with calls to nextval(), you are always protected from id
collisions.

> So I want to make sure that the line items I insert get related to the
> invoice row I just inserted, not the necessarily the last value of the
> sequence.
> Of course, I could just make the function take the name of the
> sequence as the sole input. But the name of the sequence is arbitrary,
> no?
if you want you can create you own sequence or sequences with any name
that you want to give it.  Next as an option, you can manually alter
your table to use your newly created sequence as the default value.
Lastly as another option, you can alter your sequence so that it is
owned by your table.

> So while I could assume that it would be  table_field_seq, because
> that is automatically created when you specify a serial column, that
> is not necessarily the name of the sequence.
> When I'm programming, I don't want to have to double-check the name of
> the pkey sequence of the table I'm dealing with ( although I do have
> it created automatically). I just want to refer to the table, whose
> name I'm already certain of. I'd like to have it automated, so that
> the function look up the name of the sequence of the primary key,
> rather than me having to know it. I think I could get away with
>
There probably is a sql statement that will retrieve the sequence from a
given table,  however, I'm not not fortunite enough to know what it is. :-(

I am sure that other on IRC or the PG generals mailing list would have
this answer.  Also, besure to CC all replies to the mailing list so the
others can also participle.

Regards,
Richard Broersma Jr.