CODEWITHSUNDEEP
postgresqlsequencepostgresql-12
GammaGames
GammaGames

Reputation: 1829

Get all sequences with current values

I have the following query that gets all sequences and their schemas:

SELECT sequence_schema as schema, sequence_name as sequence
FROM information_schema.sequences
WHERE sequence_schema NOT IN ('topology', 'tiger')
ORDER BY 1, 2

I would like to get the current value of each sequence name with something like select last_value from [sequence];. I have tried the following (and a couple variations), but it doesn't work because the syntax isn't correct:

DO $$
BEGIN
    EXECUTE 
        sequence_schema as schema,
        sequence_name as sequence,
        last_value
    FROM information_schema.sequences
    LEFT JOIN (
        EXECUTE 'SELECT last_value FROM ' || schema || '.' || sequence
    ) tmp
    ORDER BY 1, 2;
END
$$;

I've found some solutions that create functions to execute text or piece together a query inside a function and return the result, but I would prefer to have a single query that I can run and modify however I like.

Upvotes: 12

Views: 17678

Answers (3)

modulitos
modulitos

Reputation: 15844

Here's a solution that doesn't rely on pg_sequences or pg_sequence_last_value:

    CREATE OR REPLACE FUNCTION get_sequences()
      RETURNS TABLE (
        last_value bigint,
        sequence_schema text,
        sequence_name text
      )
      LANGUAGE plpgsql AS
    $func$
    DECLARE
        s RECORD;
    BEGIN
        FOR s IN SELECT t.sequence_schema, t.sequence_name
               FROM information_schema.sequences t
        LOOP
          RETURN QUERY EXECUTE format(
              'SELECT last_value, ''%1$s''::text, ''%2$s''::text FROM %1$I.%2$I',
              s.sequence_schema,
              s.sequence_name
          );
        END LOOP;
    END;
    $func$;

    SELECT * FROM get_sequences();

That'll output a table like this:

 last_value | sequence_schema |                     sequence_name
------------+-----------------+-------------------------------------------------------
          1 | public          | contact_infos_id_seq
          1 | media           | photos_id_seq
       2006 | company         | companies_id_seq
       2505 | public          | houses_id_seq
          1 | public          | purchase_numbers_id_seq
... etc

The other answers will only work if you are on a modern version of Postgres (I believe 10 or greater).

Upvotes: 3

user330315
user330315

Reputation:

In Postgres 12, you can use pg_sequences:

select schemaname as schema, 
       sequencename as sequence, 
       last_value
from pg_sequences

Upvotes: 27

JGH
JGH

Reputation: 17906

You can rely on the function pg_sequence_last_value

SELECT nspname as schema, 
       relname AS sequence_name,
       coalesce(pg_sequence_last_value(s.oid), 0) AS seq_last_value
FROM pg_class AS s
   JOIN pg_depend AS d ON d.objid = s.oid
   JOIN pg_attribute a ON d.refobjid = a.attrelid
                          AND d.refobjsubid = a.attnum
   JOIN pg_namespace nsp ON s.relnamespace = nsp.oid
WHERE s.relkind = 'S'
  AND d.refclassid = 'pg_class'::regclass
  AND d.classid = 'pg_class'::regclass
  AND nspname NOT IN ('topology', 'tiger')
ORDER BY 1,2 DESC;

Upvotes: 4

Related Questions