From: | <btober(at)seaworthysys(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: List last value of all sequences |
Date: | 2003-07-24 15:12:07 |
Message-ID: | 64626.216.238.112.88.1059059527.squirrel@$HOSTNAME |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | scott.marlowe | 2003-07-24 15:12:20 | Re: Limited varchar, unlimited varchar, or text? |
Previous Message | Tom Lane | 2003-07-24 15:09:41 | Re: quoting of array elements + sexp_out? |