Thread: getting currval from a sequence

Hello,

I would like to be able to get currval from a sequence but am running
ito the "current session" limitation.

I need to get the id of the previous record either during a session or
on a cold start (next morning for example).

How does one get the last record from a series of records? I just need
the id field which is a sequence field

Cheers

Tony Grant

--
RedHat Linux on Sony Vaio C1XD/S
http://www.animaproductions.com/linux2.html
Macromedia UltraDev with PostgreSQL
http://www.animaproductions.com/ultra.html

Hello

In a script I found a query

SELECT timespan_part( ... ) FROM table;

Is this a valid PostgreSQL-function? In the documentation I didn't
find any hint.
If yes, does PostgreSQL 7.1 support this function?

Thanks
Conni

On Mon, Feb 11, 2002 at 11:42:31AM +0100,
  tony <tony@animaproductions.com> wrote:
>
> How does one get the last record from a series of records? I just need
> the id field which is a sequence field

select max(id) from whatever;
(This assumes the sequence hasn't rolled over.)

On Mon, Feb 11, 2002 at 02:35:21PM +0100, Cornelia Boenigk wrote:
> Hello
>
> In a script I found a query
>
> SELECT timespan_part( ... ) FROM table;
>
> Is this a valid PostgreSQL-function? In the documentation I didn't
> find any hint.
> If yes, does PostgreSQL 7.1 support this function?

 Rather use date_part().

        Karel

--
 Karel Zak  <zakkr@zf.jcu.cz>
 http://home.zf.jcu.cz/~zakkr/

 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

Hi Karel

> Rather use date_part().

I know what to use instead and I know that I should use interval,
because timespan will be deprecated in future releases. Is PostgreSQL
such a future release?
My question is, weather a script with timespan_part() is working
properly in PostgreSQL 7.1.

Greetings
Conni

> My question is, weather a script with timespan_part() is working
> properly in PostgreSQL 7.1.

No. That function was deprecated for 7.0. And did not need to be called
explicitly in any version; date_part() was always the preferred choice.
afaicr...

                       - Thomas

Hi,
select currval('sequence_name');
with quotes should help you. But my pg 7.1.2 throws error
"sequence_name.currval is not yet defined in this session" (possibly bug
?). If I use sequence before calling this (for instance "insert into
mytable values(nextval('sequence_name'));" ), all is OK.

pajout


Bruno Wolff III wrote:

> On Mon, Feb 11, 2002 at 11:42:31AM +0100,
>   tony <tony@animaproductions.com> wrote:
>
>>How does one get the last record from a series of records? I just need
>>the id field which is a sequence field
>>
>
> select max(id) from whatever;
> (This assumes the sequence hasn't rolled over.)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>

Jan Poslusny <pajout@gingerall.cz> writes:

> Hi,
> select currval('sequence_name');
> with quotes should help you. But my pg 7.1.2 throws error
> "sequence_name.currval is not yet defined in this session" (possibly
> bug ?). If I use sequence before calling this (for instance "insert
> into mytable values(nextval('sequence_name'));" ), all is OK.

This is documented behavior.  See the FAQ and the docs.

-Doug
--
Let us cross over the river, and rest under the shade of the trees.
   --T. J. Jackson, 1863

You are right with the currval but it is not a bug since you have not
initiated a nextval in the current session yet.

Check the list 3 - 4weeks back there was a nice thread about this and it
should hoepfully provide the answer

Darren


On Mon, 11 Feb 2002, Jan Poslusny wrote:

> Hi,
> select currval('sequence_name');
> with quotes should help you. But my pg 7.1.2 throws error
> "sequence_name.currval is not yet defined in this session" (possibly bug
> ?). If I use sequence before calling this (for instance "insert into
> mytable values(nextval('sequence_name'));" ), all is OK.
>
> pajout
>
>
> Bruno Wolff III wrote:
>
> > On Mon, Feb 11, 2002 at 11:42:31AM +0100,
> >   tony <tony@animaproductions.com> wrote:
> >
> >>How does one get the last record from a series of records? I just need
> >>the id field which is a sequence field
> >>
> >
> > select max(id) from whatever;
> > (This assumes the sequence hasn't rolled over.)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> >
> >
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Bruno Wolff III wrote:
> On Mon, Feb 11, 2002 at 11:42:31AM +0100,
>   tony <tony@animaproductions.com> wrote:
> >
> > How does one get the last record from a series of records? I just need
> > the id field which is a sequence field
>
> select max(id) from whatever;
> (This assumes the sequence hasn't rolled over.)

You can do a select on the sequence table that lays behind the sequence.
usually ending in _seq.  You can't use currval() because that is a
per-backend value that saves your previous nextval call.  Make sure you
lock the sequence table during the retrieve if you want an accurate
number and don't unlock it until you are done with the value.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026