last_insert_id or currval
When you are inserting data into a table that has a generated id you often need to know what the generated id was. With mysql it's real easy, just use the
last_insert_id() function.
When you enter data into a mysql table that has an auto_increment field you can then make use of this method call retrieve the identifier that was generated. If you happen to be using PHP the job is even easier because you can retrieve this number mysql_insert_id() function of PHP without needing an extra query.
At first glance it's counterpart in postgres seems to be the currval() function. But appearences can be deceptive. You don't need to pass in any parameters at all to last_insert_id() or the mysql_insert_id() ; the database always returns the id associated with the last executed insert query for that connection. currval on the other hand takes the name of the related sequence as a parameter.
So it seems the only way to determine the insert id in pg is by inspecting the database meta data - a cumbersome task at the best of times; it takes quite a bit of effort and querying to get the task done. On the other hand as a programmer you already know the relationship between the sequence and the column so it's a simple matter to create your own function that can return the id for you. In fact it's a mere 5 lines of code.
CREATE FUNCTION add_user(char,char,integer) RETURNS BIGINT AS '
INSERT INTO users(userName,userPassword,userStatus) VALUES($1,$2,$3);
SELECT CURRVAL(''users_userid_seq'');
' LANGUAGE SQL;
Instead of executing an Insert query as you might have done with mysql you do a SELECT when you want to add data to your table. Sounds counter intuitive? See this example:
SELECT add_user('raditha'.'123',1);
