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 ?
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')
.