Thread: Sequences

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/

If you have no permission or inclination to actually define something in
the database, here is a shell-based alternative:

---
# psql -Aqt -U evaristesys_adm -d evaristesys \
  -c "SELECT sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public' ORDER BY sequence_name;"
\
| while read SEQ; 
do 
   echo -n "$SEQ: "; 
   psql -Aqt -U evaristesys_adm -d evaristesys -c "SELECT last_value FROM $SEQ;"; 
done
---

-- Alex

-- 
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/

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/
    
    

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@illinois.edu> wrote:
>
> 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/
>
>
>