Thread: inputs for pg_get_id() function?

Hello all --

I'm migrating from the MySQL world to Postgres. I'm used to having the
mysql_insert_id function to get the id of a row I just inserted. Several
people on the list turned me on to using nextval or currval to get an id
*before* I do an insert, to avoid race conditions .

Now I'm working on a PHP function that I'm calling pg_get_id(). When I
first wrote it, my inputs were $table_name and $column_name, but I
quickly found out that I have to reference the name of the columns'
sequence, not the column itself, to get the id. Now it's working.

But, I'm wondering it I can even get rid of the $sequence_name input. If
I assume that a user will only call the function to get an number for a
primary key column that is a sequence, is there a query I can do to find
out the primary key of a table, and the name of it's sequence? In
psuedo-code:

function pg_get_id ( $table ) {

   "SELECT primary_key_column_name FROM $table"

   "SELECT column_sequence_name FROM $table "

   "SELECT nextval('column_sequence_name') FROM $table"

   return $nextval;
}

--- Steve Lefevre <lefevre.10@osu.edu> wrote:
> Now I'm working on a PHP function that I'm calling pg_get_id(). When I
> first wrote it, my inputs were $table_name and $column_name, but I
> quickly found out that I have to reference the name of the columns'
> sequence, not the column itself, to get the id. Now it's working.
>
> But, I'm wondering it I can even get rid of the $sequence_name input. If
> I assume that a user will only call the function to get an number for a
> primary key column that is a sequence, is there a query I can do to find
> out the primary key of a table, and the name of it's sequence? In
> psuedo-code:
>
> function pg_get_id ( $table ) {
>
>    "SELECT primary_key_column_name FROM $table"
>
>    "SELECT column_sequence_name FROM $table "
>
>    "SELECT nextval('column_sequence_name') FROM $table"
>
>    return $nextval;
> }

While this is possible it really isn't necessary.  You really don't need to know the name of the
sequence, once it is implemented as a primary key default value.  Below are three ways to specify
the next value for a primary key.

for instance:

mydb=# CREATE TABLE Apple (
mydb(# apple_nbr Serial PRIMARY KEY,
mydb(# variety Varchar UNIQUE NOT NULL
mydb(# );
NOTICE:  CREATE TABLE will create implicit sequence "apple_apple_nbr_seq" for serial column
"apple.apple_nbr"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "apple_pkey" for table "apple"
NOTICE:  CREATE TABLE / UNIQUE will create implicit index "apple_variety_key" for table "apple"
CREATE TABLE
mydb=#
mydb=# --INSERTING specifying all the columns
mydb=#
mydb=# INSERT INTO APPLE( apple_nbr, variety ) VALUES( DEFAULT, 'Red Delicous');
INSERT 0 1
mydb=#
mydb=# --INSERTING by only specifying columns of interest
mydb=#
mydb=# INSERT INTO APPLE( variety) VALUES( 'Fugi' );  -- this uses the implied DEFAULT
INSERT 0 1
mydb=#
mydb=# SELECT * FROM Apple;
 apple_nbr |   variety
-----------+--------------
         1 | Red Delicous
         2 | Fugi
(2 rows)

mydb=#
mydb=# \d apple
                                   Table "public.apple"
  Column   |       Type        |                         Modifiers
-----------+-------------------+-----------------------------------------------------------
 apple_nbr | integer           | not null default nextval('apple_apple_nbr_seq'::regclass)
 variety   | character varying | not null
Indexes:
    "apple_pkey" PRIMARY KEY, btree (apple_nbr)
    "apple_variety_key" UNIQUE, btree (variety)

mydb=#
mydb=# --IF you want to specify the next value yourself and not use a default
mydb=#
mydb=# INSERT INTO APPLE( apple_nbr, variety ) VALUES ( nextval('apple_apple_nbr_seq'), 'Pink
Lady');
INSERT 0 1
mydb=#
mydb=# select * from apple;
 apple_nbr |   variety
-----------+--------------
         1 | Red Delicous
         2 | Fugi
         3 | Pink Lady
(3 rows)

mydb=#

Regards,
Richard Broersma Jr.

 > 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.