Re: Sequences

Thanks so much.  This was very helpful!  Thanks.

Lance

On 12/4/18, 1:18 PM, "Alex Balashov" <abalashov@evaristesys.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/