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 по дате отправления: