List last value of all sequences

From: <btober(at)seaworthysys(dot)com>
To: <pgsql-general(at)postgresql(dot)org>
Subject: List last value of all sequences
Date: 2003-07-24 12:46:38
Message-ID: 64784.216.238.112.88.1059050798.squirrel@$HOSTNAME
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm interested in producing a list of all sequence names and the
corresponding last value. Starting with a list of sequence names
generated by

SELECT c.relname FROM pg_class c WHERE (c.relkind = 'S');

my initial thought was to extend that with

SELECT c.relname, currval(c.relname)
FROM pg_class c WHERE (c.relkind = 'S');

but of course that doesn't work since "currval" is not defined until
"nextval" has been called at least once, and I don't want to increment
the sequences...just query the value.

I know that for a given sequence, E.G., "city_city_seq" I can get its
value using

SELECT last_value FROM city_city_seq;

So my next try used a function defined as

CREATE FUNCTION public.get_sequence_last_value(name) RETURNS int4 AS '
DECLARE
ls_sequence ALIAS FOR $1;
lr_record RECORD;
li_return INT4;
BEGIN
FOR lr_record IN EXECUTE ''SELECT last_value FROM '' || ls_sequence LOOP
li_return := lr_record.last_value;
END LOOP;
RETURN li_return;
END;' LANGUAGE 'plpgsql' VOLATILE;

Followed by

SELECT c.relname, get_sequence_last_value(c.relname)
FROM pg_class c WHERE (c.relkind = 'S');

Which works and produces the result I want, but that function seems
really messy. Is there a cleaner way to do this?

~Berend Tober

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ned Lilly 2003-07-24 13:05:40 Re: SAP DB: The unsung Open Source DB
Previous Message Rob Sell 2003-07-24 12:37:36 Re: Join Problem