sqlpostgresqldatabase-sequence

Get the next 10 values of the SEQUENCE at once


I want to get the next 10 values of the SEQUENCE at once.

I can get those individually by calling

SELECT setval('myseq', ...);           -- Next nextval will return 43

10 times back to back.

On Microsoft SQL server, I can do something like below to get all the 10 at once.

DECLARE
    @range_first_value  SQL_VARIANT
  , @range_last_value   SQL_VARIANT
  , @range_cycle_count  INT
  , @sequence_increment SQL_VARIANT
  , @sequence_min_value SQL_VARIANT
  , @sequence_max_value SQL_VARIANT;
EXEC sys.sp_sequence_get_range
    @sequence_name = N''                                -- nvarchar(776)
  , @range_size = 0                                     -- bigint
  , @range_first_value = @range_first_value OUTPUT      -- sql_variant
  , @range_last_value = @range_last_value OUTPUT        -- sql_variant
  , @range_cycle_count = @range_cycle_count OUTPUT      -- int
  , @sequence_increment = @sequence_increment OUTPUT    -- sql_variant
  , @sequence_min_value = @sequence_min_value OUTPUT    -- sql_variant
  , @sequence_max_value = @sequence_max_value OUTPUT    -- sql_variant

Is there an equivalent on PostgreSQL?


Solution

  • Use generate_series().

    select nextval('myseq')
    from generate_series(1, 10);
    

    Db<>fiddle.