From: | <btober(at)seaworthysys(dot)com> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | List last value of all sequences |
Date: | 2003-07-24 12:46:38 |
Message-ID: | 64784.216.238.112.88.1059050798.squirrel@$HOSTNAME |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Ned Lilly | 2003-07-24 13:05:40 | Re: SAP DB: The unsung Open Source DB |
Previous Message | Rob Sell | 2003-07-24 12:37:36 | Re: Join Problem |