Re: Sequences

Why not just do this:

select schemaname
, sequencename
, last_value
, increment_by
from pg_sequences
;

> On Dec 4, 2018, at 2:47 PM, Campbell, Lance <lance(at)illinois(dot)edu> wrote:
>
> Thanks so much. This was very helpful! Thanks.
>
> Lance
>
> On 12/4/18, 1:18 PM, "Alex Balashov" <abalashov(at)evaristesys(dot)com> wrote:
>
> Constructing dynamic SQL is always a bit tricky. Try define this
> function:
>
> ---
> CREATE OR REPLACE FUNCTION seqs_last_val()
> RETURNS SETOF record
> AS $$
> DECLARE
> _seqname varchar;
> _r record;
> BEGIN
> SELECT INTO _r null::varchar AS seqname, -1::integer AS lastval;
>
> FOR _seqname IN
> SELECT sequence_name
> FROM information_schema.sequences
> WHERE sequence_schema = 'public'
> LOOP
> _r.seqname = _seqname;
> EXECUTE format('SELECT last_value FROM %s', quote_ident(_seqname)) INTO _r.lastval;
> RETURN NEXT _r;
> END LOOP;
>
> RETURN;
> END
> $$ LANGUAGE 'plpgsql';
> ---
>
> Then run:
>
> SELECT * FROM seqs_last_val() AS (seqname varchar, last_value integer);
>
> -- Alex
>
> On Tue, Dec 04, 2018 at 07:03:11PM +0000, Campbell, Lance wrote:
>> PostgreSQL 10.x
>>
>> What query will give the name of all sequences in a database and the current or next value for each sequence?
>>
>> This will give me everything except for the next value in the sequence.
>>
>> SELECT * FROM information_schema.sequences;
>>
>> Thanks,
>>
>> Lance
>
> --
> Alex Balashov | Principal | Evariste Systems LLC
>
> Tel: +1-706-510-6800 / +1-800-250-5920 (toll-free)
> Web: http://www.evaristesys.com/, http://www.csrpswitch.com/
>
>
>