Re: List last value of all sequences

On Thursday 24 July 2003 13:46, btober@seaworthysys.com wrote:
> 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');
[snip]
> 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?

Why not just have two (nested) loops in the function?

FOR class_rec IN SELECT c.relname FROM pg_class WHERE c.relkind=''S'' LOOP
  FOR lr_record IN EXECTUTE ... || class_rec.relname... LOOP
  ...
  END LOOP
END LOOP

Or am I missing something?
--
  Richard Huxton
  Archonet Ltd