-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
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';
It works for simple sequences in which the number is incremented by 1 each
time it is called. We need the CASE to account for newly created sequences
in which the last_value is equal to min_value (1) and the "is_called"
flag is set to false. It will still fail on other cases, such as sequences
that start with something other than 1, increment other than +1, or are at
their max_value.
The function you provided should work fine as well, although it should
return BIGINT, not int4. If you are doing this check often, you might want
to also have a function that returns all the sequences for you when called.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200307241009
-----BEGIN PGP SIGNATURE-----
Comment: http://www.turnstep.com/pgp.html
iD8DBQE/H+mHvJuQZxSWSsgRAicMAJ4zqV/UmDlUKdQtI8e3qAorEJeKPACfVs97
vx8Oc9kFaGd8tpd1+yhR7jY=
=zJKz
-----END PGP SIGNATURE-----