Postgresql: Update all Sequences to Latest

update_seqs_to_latest.sql

DO $$
DECLARE
i TEXT;
BEGIN
  FOR i IN (
    SELECT 'SELECT SETVAL('
        || quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname))
        || ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM '
        || quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
      FROM pg_class AS S,
           pg_depend AS D,
           pg_class AS T,
           pg_attribute AS C,
           pg_tables AS PGT
     WHERE S.relkind = 'S'
       AND S.oid = D.objid
       AND D.refobjid = T.oid
       AND D.refobjid = C.attrelid
       AND D.refobjsubid = C.attnum
       AND T.relname = PGT.tablename
  ) LOOP
      EXECUTE i;
  END LOOP;
END $$;

update_seqs_to_latest_fn.sql

CREATE OR REPLACE FUNCTION update_seqs_to_latest()
RETURNS void
LANGUAGE plpgsql
VOLATILE
AS $$
DECLARE
    i TEXT;
BEGIN
    FOR i IN (
        SELECT 'SELECT SETVAL('
            || quote_literal(quote_ident(PGT.schemaname) || '.' || quote_ident(S.relname))
            || ', COALESCE(MAX(' ||quote_ident(C.attname)|| '), 1) ) FROM '
            || quote_ident(PGT.schemaname)|| '.'||quote_ident(T.relname)|| ';'
        FROM pg_class AS S,
             pg_depend AS D,
             pg_class AS T,
             pg_attribute AS C,
             pg_tables AS PGT
        WHERE S.relkind = 'S'
            AND S.oid = D.objid
            AND D.refobjid = T.oid
            AND D.refobjid = C.attrelid
            AND D.refobjsubid = C.attnum
            AND T.relname = PGT.tablename
    ) LOOP
        EXECUTE i;
        RAISE NOTICE 'Executed: %', i;
    END LOOP;
    
    RAISE NOTICE 'All sequences updated successfully';
END;
$$;

-- To use the function, simply call:
-- SELECT update_seqs_to_latest();