On Thursday 24 July 2003 13:46, [EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
