Обсуждение: Serial - last value

Поиск
Список
Период
Сортировка

Serial - last value

От
"hendra kusuma"
Дата:
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

Thank you
Regards
Hendra

Re: Serial - last value

От
Richard Huxton
Дата:
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

Re: Serial - last value

От
Richard Huxton
Дата:
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