Get all sequences with current values

Question

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.

Answer 1

In Postgres 12, you can use pg_sequences:

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

Answer 2

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;