Serial - last value

hendra kusuma wrote:

Hi there
a little basic question here

I usually use something like this in stored function
to get the last value of a serial type column

select last_value into ret from id_sequence

return ret

What i'm asking is,
if many people run the same stored function at the same time
will they get the last_value they should really get
or is there another way to code that can ensure they really get the right
value

You should use the functional interface: SELECT currval('my_sequence').
That guarantees you the right value for your current session.

This only works if you've called nextval('my_sequence') at some point in
the current session (although that could be automatic for a column of
type SERIAL).

--
Richard Huxton
Archonet Ltd

hendra kusuma wrote:

Let me get this clear
it should looks like this?

create function something() returns integer as $$
declare
ret integer;
begin
-- just assume something table has a serial column as primary key
insert into something values ('a value');
select currval('something_sequence') into ret;
return ret;
end

$$ language 'plpgsql';

That should work fine, although for that particular case with recent
versions you could just use:

INSERT INTO some_table (id, mytext) VALUES (DEFAULT, 'a value')
RETURNING id;

--
Richard Huxton
Archonet Ltd