Thread: currval() without specifying the sequence name

Hi,

Is there a way to retrieve the current value (i.e. the last used value) of the
last used sequence without specifying the sequence name? Something like the
following:

create table ( id serial, value varchar(10));
insert into someOthertest (value) values ('100');
insert into test (value) values ('10');
insert into test (value) values ('20');

select currval() should returns 2 (the last value of the test table sequence)

Thanks




__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you�re looking for faster
http://search.yahoo.com

O kyrios ow egrapse stis Mar 9, 2004 :

> Hi,
> 
> Is there a way to retrieve the current value (i.e. the last used value) of the
> last used sequence without specifying the sequence name? Something like the
> following:
> 
> create table ( id serial, value varchar(10));
> insert into someOthertest (value) values ('100');
> insert into test (value) values ('10');
> insert into test (value) values ('20');
> 
> select currval() should returns 2 (the last value of the test table sequence)

You could use the same sequence in many tables.

> 
> Thanks
> 
> 
> 
> 
> __________________________________
> Do you Yahoo!?
> Yahoo! Search - Find what you’re looking for faster
> http://search.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faqs/FAQ.html
> 

-- 
-Achilleus

--- Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> 
> You could use the same sequence in many tables.
> 

I guess I could but then:

1) I'd still have to know the sequence name. When integrating pgSql with 3party
frameworks I'd either have to pass it there (could be a very difficult task) or
make some assumptions (also not very good).

2) There may be a need to have different sequences being used in an app.

3) If one sequence is used, in many cases it'll have to be of type int8 and
pgSql does not handle searches with int8 very nicely.

Thanks




__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you�re looking for faster
http://search.yahoo.com

O kyrios ow egrapse stis Mar 9, 2004 :

> 
> --- Achilleus Mantzios <achill@matrix.gatewaynet.com> wrote:
> > 
> > You could use the same sequence in many tables.
> > 
> 
> I guess I could but then:
> 
> 1) I'd still have to know the sequence name. When integrating pgSql with 3party
> frameworks I'd either have to pass it there (could be a very difficult task) or
> make some assumptions (also not very good).
> 
> 2) There may be a need to have different sequences being used in an app.
> 
> 3) If one sequence is used, in many cases it'll have to be of type int8 and
> pgSql does not handle searches with int8 very nicely.
> 
> Thanks
> 
>

I dont think you have given solid reasons as to why somebody whould
want to do that (in order to be more specific and help you better),
but in any case:

foodb=# SELECT relname from pg_class where relkind = 'S';   relname
---------------footbl_id_seqfootbl2_id_seq
(1 row)

gives you a list of sequences of your current schema.

Then you can loop thru those to:

select last_value from <cur_seq>;
and get the maximum, but again what exaclty are you trying to do?
> 
> 
> __________________________________
> Do you Yahoo!?
> Yahoo! Search - Find what you’re looking for faster
> http://search.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

-- 
-Achilleus

On Tuesday 09 March 2004 13:50, ow wrote:
> Hi,
>
> Is there a way to retrieve the current value (i.e. the last used value) of
> the last used sequence without specifying the sequence name? Something like
> the following:
>
> create table ( id serial, value varchar(10));
> insert into someOthertest (value) values ('100');
> insert into test (value) values ('10');
> insert into test (value) values ('20');
>
> select currval() should returns 2 (the last value of the test table
> sequence)

Not really - what if two sequences had been used?

--  Richard Huxton Archonet Ltd

ow <oneway_111@yahoo.com> writes:
> I guess I could but then:

> 1) I'd still have to know the sequence name. When integrating pgSql
> with 3party frameworks I'd either have to pass it there (could be a
> very difficult task) or make some assumptions (also not very good).

This scenario is really entirely ridiculous.  You want your code to be
integrated with other code and not make any assumptions, but you are
willing to assume that only one sequence will be touched in any command,
or that the last one that's touched is the one you want?

It seems to me that this idea will render your code more fragile, not
less so.

> 3) If one sequence is used, in many cases it'll have to be of type int8 and
> pgSql does not handle searches with int8 very nicely.

Quote or cast and you're fine.  But what does that have to do with
knowing a sequence name?
        regards, tom lane

--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> or that the last one that's touched is the one you want?

Correct.



__________________________________
Do you Yahoo!?
Yahoo! Search - Find what you�re looking for faster
http://search.yahoo.com