I have a database class that I use which encapsulates some database functions for mysql. I'm trying to make a new class that uses postgres and I'm having trouble understanding what to do about this line:
$this->insert_id = @mysql_insert_id($this->connection_id);
what is the PG equivalent of mysql_insert_id()??
[man]pg_last_oid/man ?
I had read that page but am not sure what OID meant...it would appear that MySQL table creation syntax is different from PostGres table creation syntax. Is that what's going on here?
What version are you running?
Starting with 8.2 you got insert ... returning. From the docs:
Insert a single row into table distributors, returning the sequence number generated by the DEFAULT clause:
INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
RETURNING did;
If you're running 8.1 or before, then the standard way is to use the sequence and the nextval() / currval() functions.
insert into tablea (textfield) values ('this is a row being inserted');
select currval('tablea_id_seq'); -- store this value in a php variable, say $id
insert into tableb (a_id, moredata) values ($id,$moredata);
thx for your help sxooter (again and again).
I'm using postgresql V. 7.4.7.
for the v 8.2 syntax, what does DEFAULT do? where does the value get returned in PHP? If I upgrade to 8.1 this is what i expect to do.
As for the other sql u offer, think I understand that you are telling me to insert some data into some table:
INSERT INTO my_table SET field1=1, field2='val2', field3='val3';and then immediately follow that query with a subsequent one:
SELECT curval('foo');this is confusing...what is the argument for curval? i googled this and it would appear i need to create a 'sequence'....what does that mean? Also, is this safe for a webserver environment with many connections happening at once? Seems to me that it might not be threadsafe.
ok i think i'm figuring it out. looking at my table in phpPGAdmin I see that the default column for my id field contains this: nextval('public.test_zip_assoc_id_seq'::text)
It would seem that my table had a sequence created for the id field when I defined ID as they primary key. What is 'public' in there for?
Also, the reading I've done so far suggests that for this to be safe for a busy server environment, i would have to put the insert and the select currval() statements within a transaction block. grrrrrr. am i correct in understanding that?
and curval has TWO R's...it's CURRVAL().
1: public is the schema. You can ignore it if you want. Or use it. You need to read the pgsql docs a bit to bone up on the db. Schemas are just name spaces for your tables, and by default everything goes in the public schema of the db.
2: DEFAULT is just that. it inserts the DEFAULT for that column.
3: You have to do NOTHING to make currval safe with lots of inserts. Look it up in the docs. If the reading you've done so far suggests you need a transaction for it to be safe, you need to stop reading that source, as they know not of what they speak. 
1: ok checking into that. thanx for the tip.
2: ok i think i get it...the RETURNING part of the query is what specifies which field to return. but WHERE does this returned value show up in this php:
$sql = "INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets') RETURNING did;";
$result = pg_query($sql);
3: The last thing I want to do is irritate my savior, but are you sure? Suppose I had this script on my Linux/Apache/PHP server and was running PostGreSQL:
$sql1 = "INSERT INTO my_table SET field1=1, field2='val2', field3='val3'";
$result = pg_query($sql);
// CHECK POINT 1
// ...some other code runs here for some stupid reason
$sql2 = "SELECT CURRVAL('public.test_zip_assoc_id_seq')";
$result = pg_query($sql2);
While it is unlikely that the operating system might halt execution of the thread running this PHP script right at CHECK POINT 1 to schedule some other user's PHP thread, it is nevertheless a remote possibility is it not? In the event that the OS scheduled the same PHP script to run for some other user, the value returned by the second query would have been contaminated by the other user's script runnin the same query.
From the docs on sequence functions found here:
http://www.postgresql.org/docs/8.1/static/functions-sequence.html
comes this quote:
currval
Return the value most recently obtained by nextval for this sequence in the current session. (An error is reported if nextval has never been called for this sequence in this session.) Notice that because this is returning a session-local value, it gives a predictable answer whether or not other sessions have executed nextval since the current session did.
As for the returns it's just like a select. i,e, if you do $row = pg_fetch_assoc($res) then $row['did'] (or whatever you used as the arg for return) will have the insert value.
In the process of trying to test this out, I get an error for this:
database=> INSERT INTO test SET value='foobar';
ERROR: syntax error at or near "SET" at character 18
Is that not valid syntax in PostGreSQL? If so, I am utterly depressed. That means every insert query across my entire project is likely invalid in PostGres.
Also, I'm starting to realize that the way in which PostGres returns last_insert_id will require that all my queries using it must be modified as well either--to add RETURNING VALUE_X so i can get the last insert id in one atomic transaction OR to modify the calls to my DB class so that they provide a sequence argument to last_insert_id.
rats 
sneakyimp wrote:In the process of trying to test this out, I get an error for this:
database=> INSERT INTO test SET value='foobar'; ERROR: syntax error at or near "SET" at character 18Is that not valid syntax in PostGreSQL? If so, I am utterly depressed. That means every insert query across my entire project is likely invalid in PostGres.
Also, I'm starting to realize that the way in which PostGres returns last_insert_id will require that all my queries using it must be modified as well either--to add RETURNING VALUE_X so i can get the last insert id in one atomic transaction OR to modify the calls to my DB class so that they provide a sequence argument to last_insert_id.
rats
1: That's update syntax, not insert syntax. Oracle, db2, most other databases don't support that either. Sorry that you've used mysql specific syntax all over the place, you'll find that if you go from mysql to almost any database except mysql. Note that MySQL does support standard insert syntax of
insert into table (col1, col2, col3) values ('value1','value2','value3');
so there's really no reason to have coded to the non-standard way. I think the mysql manual even mentions that the other syntax is an extension to the SQL standard.
2: As for the returning / select nextval, that is database specific, and every database does it differently. Blame the SQL specification committee for not defining anything on how to handle sequence return values. There are abstraction layers that make it possible to not have to code it all up different for each database, but abstraction layers also remove you one more level from the db and often make it harder to utilize the full power of your particular db engine.
RE 1:
Yes, i goofed. The reason i use that syntax is because it's easier to tell which field is getting which values...when you have two separate lists or fields and values it's often a pain to see which lines up with which.
Sadness.
RE 2:
Where is that damn committee?! Why I oughta....
thx for the help sxooter. however grim this may be i will soldier throuh more effectively thanks to your help.