Re: List last value of all sequences
От | |
---|---|
Тема | Re: List last value of all sequences |
Дата | |
Msg-id | 64626.216.238.112.88.1059059527.squirrel@$HOSTNAME обсуждение исходный текст |
Ответ на | Re: List last value of all sequences (greg@turnstep.com) |
Ответы |
Re: List last value of all sequences
|
Список | pgsql-general |
> This will work in most cases: > > SELECT c.relname, > setval(c.relname, CASE WHEN nextval(c.relname) > 1 THEN > currval(c.relname)-1 ELSE 1 END,'true') > FROM pg_class c WHERE c.relkind='S'; > The main problem with this approach is that, while you get the "current value", the sequence is incremented by the call. I just want to (strictly) look at the value. I think I forgot to cc the list in a reply to another respondent in which I explained the further, previously unstated objective of creating an updateble view so that I can conveniently see AND CHANGE the sequence values: CREATE FUNCTION public.set_sequence(name, int4) RETURNS int4 AS ' DECLARE l_sequence_name ALIAS FOR $1; l_last_value ALIAS FOR $2; BEGIN IF l_last_value = 0 THEN PERFORM setval(l_sequence_name,1, False); ELSE PERFORM setval(l_sequence_name,l_last_value); END IF; RETURN 1; END;' LANGUAGE 'plpgsql' VOLATILE; CREATE VIEW public.sequence_values AS SELECT pg_get_userbyid(c.relowner) AS sequenceowner, c.relname AS sequencename, get_sequence_last_value(c.relname) AS last_value FROM pg_class c WHERE (c.relkind = 'S') ORDER BY pg_get_userbyid(c.relowner), c.relname; CREATE RULE sequence_values_rd AS ON DELETE TO sequence_values DO INSTEAD NOTHING; CREATE RULE sequence_values_ri AS ON INSERT TO sequence_values DO INSTEAD NOTHING; CREATE RULE sequence_values_ru AS ON UPDATE TO sequence_values DO INSTEAD SELECT set_sequence(new.sequencename, new.last_value) AS set_sequence; ~Berend Tober
В списке pgsql-general по дате отправления: