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  (Richard Huxton <dev@archonet.com>)
Re: List last value of all sequences  (greg@turnstep.com)
Список 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 по дате отправления:

Предыдущее
От: "Rob Sell"
Дата:
Сообщение: Re: Join Problem
Следующее
От: "Ned Lilly"
Дата:
Сообщение: Re: SAP DB: The unsung Open Source DB