> 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
