Re: Support retrieving value from any sequence

On 14 July 2015 at 17:17, Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Tue, Jul 14, 2015 at 10:52 AM, Thom Brown <thom@linux.com> wrote:
> > When using currval() to find the current value of all sequences, it chokes
> > on those that aren't initialised.  This is expected and documented as
> > behaving in this manner.  However, I think it would be useful to also
> > support retrieving the current value of a sequence, regardless of whether
> > it's been used.  As this wouldn't be to get a sequence value for the current
> > session, but all sessions, this would ideally get the real current value.
> >
> > The use-case I have in mind is for finding out how close to the 32-bit
> > integer limit sequences have reached.  At the moment, this isn't possible
> > without creating a custom function to go fetch the last_value from the
> > specified sequence.
> >
> > So would it be desirable to have a function which accepts a sequence
> > regclass as a parameter, and returns the last_value from the sequence?
> >
> > Effectively, the same result as what this provides:
> >
> > CREATE FUNCTION lastval(tablename regclass) RETURNS bigint AS $$
> > DECLARE
> >   last_value bigint;
> > BEGIN
> >   EXECUTE format('SELECT last_value FROM %I ', tablename) INTO last_value
> > USING tablename;
> >   RETURN last_value;
> > END
> > $$ LANGUAGE plpgsql;
>
> Since it's trivial to define this function if you need it, I'm not
> sure there's a reason to include it in core.

It's not always possible to create functions on a system when access
is restricted.  It may even be the case that procedural languages are
prohibited, and plpgsql has been removed.

Thom