I want to insert an user and get its id e.g. last insert id. through sql.

CREATE OR REPLACE FUNCTION create_user(registration_ip inet)
  RETURNS integer AS
insert into users(registration_ip)
   values($1);
select max(id) from users;
LANGUAGE sql VOLATILE

Is this function secure ? I meant is it concurrency safe ? e.g. n users are being created concurrently is it guaranteed that it will return the current user's id ?

• sql
• postgresql

Use RETURNING:

CREATE OR REPLACE FUNCTION create_user(registration_ip inet)
  RETURNS integer AS
INSERT INTO users(registration_ip)
   VALUES($1) RETURNING id;
LANGUAGE sql VOLATILE;

No it's not safe (and it's terribly slow!)

You should use sequences for this purpose.

If the id column is defined as serial, a sequence is automatically created for you and you can retrieve the last generated number using currval('users_id_seq').