Re: using sequences

At 22:26 13.06.2003, Erik Price said:
--------------------[snip]--------------------
>I have read the manual about sequences and I thought I understood both
>their purpose and how to use them. When I perform inserts, the sequence
>is updated appropriately. However, I can't seem to directly access the
>sequence myself. I always seem to get this message:
>
>be_db=# select currval('news_news_id_seq');
>ERROR: news_news_id_seq.currval is not yet defined in this session
>
>Can someone explain what is going on?
--------------------[snip]--------------------

A sequence is a funny thing. If you SELECT nextval('sequence_name'), it
will return a value that is guaranteed unique (for this sequence), across
all parallel accesses and transactions that may do the same at almost the
same moment. SELECT currval('sequence_name') however is connection-bound,
which means it will _always_ return the last value that has been obtained
_by_this_connection_ (regardless of transactions).

If you consider this you will see the need that you _first_ execute
nextval() at least once, before currval() can be queried - it's simply not
defined before. And that's what the message says anyway.

If you have a serial field, you may safely
INSERT INTO TABLE (cols) VALUE (vals)
SELECT currval('table_id_sequence') as "row_id"
and you will retrieve the serial ID that has been obtained by the previous
insert.

HTH,

--
>O Ernest E. Vogelsinger
(\) ICQ #13394035
^ http://www.vogelsinger.at/