List last value of all sequences
| От | |
|---|---|
| Тема | List last value of all sequences |
| Дата | |
| Msg-id | 64784.216.238.112.88.1059050798.squirrel@$HOSTNAME обсуждение исходный текст |
| Ответы |
Re: List last value of all sequences
Re: List last value of all sequences |
| Список | 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
В списке pgsql-general по дате отправления: