# How to generate a 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, ... series in standard SQL or T-SQL?

Given two numbers `n` and `m`, I want to generate a series of the form

``````1, 2, ..., (n-1), n, n, (n-1), ... 2, 1
``````

and repeat it `m` times.

For instance, for `n = 3` and `m = 4`, I want a sequence of the following 24 numbers:

``````1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1, 1, 2, 3, 3, 2, 1
----------------  ----------------  ----------------  ----------------
``````

I know how to achieve this result in PostgreSQL by either of two methods:

Using the following query, which uses the `generate_series` function, and a few tricks to guarantee that the order is the right one:

``````WITH parameters (n, m) AS
(
VALUES (3, 5)
)
SELECT
xi
FROM
(
SELECT
i, i AS xi
FROM
parameters, generate_series(1, parameters.n) AS x(i)
UNION ALL
SELECT
i + parameters.n, parameters.n + 1 - i AS xi
FROM
parameters, generate_series(1, parameters.n) AS x(i)
) AS s0
CROSS JOIN
generate_series (1, (SELECT m FROM parameters)) AS x(j)
ORDER BY
j, i ;
``````

... or use a function for the same purpose, with adjoint and nested loops:

``````CREATE FUNCTION generate_up_down_series(
_elements    /* n */ integer,
_repetitions /* m */ integer)
RETURNS SETOF integer AS
\$BODY\$
declare
j INTEGER ;
i INTEGER ;
begin
for j in 1 .. _repetitions loop
for i in         1 .. _elements loop
return next i ;
end loop ;
for i in reverse _elements .. 1 loop
return next i ;
end loop ;
end loop ;
end ;
\$BODY\$
LANGUAGE plpgsql IMMUTABLE STRICT ;
``````

How could I possibly do the equivalent in either standard SQL or in Transact-SQL / SQL Server?

In Postgres, it's easy using the `generate_series()` function:

``````WITH
parameters (n, m) AS
( VALUES (3, 5) )
SELECT
CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
parameters AS p,
generate_series(1, p.n) AS gn (i),
generate_series(1, 2)   AS g2 (i),
generate_series(1, p.m) AS gm (i)
ORDER BY
gm.i, g2.i, gn.i ;
``````

In standard SQL - and assuming that there is a reasonable limit on the size of the parameters n, m, i.e. less than a million - you can use a `Numbers` table:

``````CREATE TABLE numbers
( n int not null primary key ) ;
``````

fill it with the preferred method of your DBMS:

``````INSERT INTO numbers (n)
VALUES (1), (2), .., (1000000) ;  -- some mildly complex SQL here
-- no need to type a million numbers
``````

and then use it, instead of `generate_series()`:

``````WITH
parameters (n, m) AS
( VALUES (3, 5) )
SELECT
CASE WHEN g2.i = 1 THEN gn.i ELSE p.n + 1 - gn.i END AS xi
FROM
parameters AS p
JOIN numbers AS gn (i) ON gn.i <= p.n
JOIN numbers AS g2 (i) ON g2.i <= 2
JOIN numbers AS gm (i) ON gm.i <= p.m
ORDER BY
gm.i, g2.i, gn.i ;
``````
• In practice, I don't expect those numbers to be bigger than 100; but in theory they could be anything. Jan 8 '17 at 23:55

## Postgres

You can make it work with a single `generate_series()` and basic math (see mathematical functions).

Wrapped into a simple SQL function:

``````CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
RETURNS SETOF int
LANGUAGE sql IMMUTABLE AS
\$func\$
SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END
FROM  (
SELECT n2m, n2m % (n*2) AS n2
FROM   generate_series(0, n*2*m - 1) n2m
) sub
ORDER  BY n2m
\$func\$;
``````

Call:

``````SELECT * FROM generate_up_down_series(3, 4);
``````

Generates the desired result. n and m can be any integer where n*2*m does not overflow `int4`.

### How?

In the subquery:

• Generate the desired total number of rows (n*2*m), with a simple ascending number. I name it `n2m`. 0 to N-1 (not 1 to N) to simplify the following modulo operation.

• Take it % n*2 (`%` is the modulo operator) to get a series of n ascending numbers, m times. I name it `n2`.

In the outer query:

• Add 1 to lower half (n2 < n).

• For the upper half (n2 >= n) mirror of the lower half with n*2 - n2.

• I added `ORDER BY` to guarantee the requested order. With current versions or Postgres it also works without `ORDER BY` for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

Unfortunately, `generate_series()` is Postgres specific and not standard SQL, as has been commented. But we can reuse the same logic:

## Standard SQL

You can generate the serial numbers with a recursive CTE instead of `generate_series()`, or, more efficiently for repeated use, create a table with serial integer numbers once. Anyone can read, noone can write to it!

``````CREATE TABLE int_seq (i integer);

WITH RECURSIVE cte(i) AS (
SELECT 0
UNION ALL
SELECT i+1 FROM cte
WHERE  i < 20000  -- or as many you might need!
)
INSERT INTO int_seq
SELECT i FROM cte;
``````

Then, the above `SELECT` becomes even simpler:

``````SELECT CASE WHEN n2 < n THEN n2 + 1 ELSE n*2 - n2 END AS x
FROM  (
SELECT i, i % (n*2) AS n2
FROM   int_seq
WHERE  i < n*2*m  -- remember: 0 to N-1
) sub
ORDER  BY i;
``````

If you need plain SQL. Theoretically it should to work on the most DBMSs (tested on PostgreSQL and SQLite):

``````with recursive
s(i,n,z) as (
select * from (values(1,1,1),(3*2,1,2)) as v  -- Here 3 is n
union all
select
case z when 1 then i+1 when 2 then i-1 end,
n+1,
z
from s
where n < 3), -- And here 3 is n
m(m) as (select 1 union all select m+1 from m where m < 2) -- Here 2 is m

select n from s, m order by m, i;
``````

# Explanation

1. Generate series 1..n

Assuming that `n=3`

``````with recursive s(n) as (
select 1
union all
select n+1 from s where n<3
)
select * from s;
``````

It is quite simple and could be found in the almost any docs about recursive CTEs. However wee need two instances of each values so

2. Generate series 1,1,..,n,n

``````with recursive s(n) as (
select * from (values(1),(1)) as v
union all
select n+1 from s where n<3
)
select * from s;
``````

Here we just doubling the initial value, which has two rows, but the second bunch we need in the reverse order, so we'll introduce the order in a bit.

3. Before we introduce the order observe that this is also a thing. We can have two rows in the starting condition with three columns each, our `n<3` is still a single column conditional. And, we're still just increasing the value of `n`.

``````with recursive s(i,n,z) as (
select * from (values(1,1,1),(1,1,1)) as v
union all
select
i,
n+1,
z
from s where n<3
)
select * from s;
``````
4. Likewise, we can mix them up a bit, watch our starting condition change here: here we have a `(6,2)`, `(1,1)`

``````with recursive s(i,n,z) as (
select * from (values(1,1,1),(6,1,2)) as v
union all
select
i,
n+1,
z
from s where n<3
)
select * from s;
``````
5. Generate series 1..n,n..1

The trick here is to generate the series, (1..n) twice, and then simply change the ordering on the second set.

``````with recursive s(i,n,z) as (
select * from (values(1,1,1),(3*2,1,2)) as v
union all
select
case z when 1 then i+1 when 2 then i-1 end,
n+1,
z
from s where n<3
)
select * from s order by i;
``````

Here `i` is order and `z` is number of the sequence (or half of sequence if you want). So for sequence 1 we are increasing order from 1 to 3 and for sequence 2 we are decreasing the order from 6 to 4. And finally

6. Multiply the series to `m`

(see the first query in the answer)

If you want a portable solution you need to realize that this is basically a mathematical problem.

Given @n as the highest number of the sequence and @x as the position of the number in that sequence (starting with zero), the following function would work in SQL Server:

``````CREATE FUNCTION UpDownSequence
(
@n int, -- Highest number of the sequence
@x int  -- Position of the number we need
)
RETURNS int
AS
BEGIN
RETURN  @n - 0.5 * (ABS((2*((@x % (@n+@n))-@n)) +1) -1)
END
GO
``````

You can check it with this CTE:

``````DECLARE @n int=3;--change the value as needed
DECLARE @m int=4;--change the value as needed

WITH numbers(num) AS (SELECT 0
UNION ALL
SELECT num+1 FROM numbers WHERE num+1<2*@n*@m)
SELECT num AS Position,
dbo.UpDownSequence(@n,num) AS number
FROM numbers
OPTION(MAXRECURSION 0)
``````

(Quick explanation: the function uses MODULO() to create a sequence of repeating numbers and ABS() to turn it into a zig-zag wave. The other operations transform that wave to match the desired result.)

In PostgreSQL, this is easy,

``````CREATE OR REPLACE FUNCTION generate_up_down_series(n int, m int)
RETURNS setof int AS \$\$
SELECT x FROM (
SELECT 1, ordinality AS o, x FROM generate_series(1,n) WITH ORDINALITY AS t(x)
UNION ALL
SELECT 2, ordinality AS o, x FROM generate_series(n,1,-1) WITH ORDINALITY AS t(x)
) AS t(o1,o2,x)
CROSS JOIN (
SELECT * FROM generate_series(1,m)
) AS g(y)
ORDER BY y,o1,o2
\$\$ LANGUAGE SQL;
``````

This works in MS-SQL and I think can be modified for any SQL flavor.

``````declare @max int, @repeat int, @rid int

select @max = 3, @repeat = 4

-- create a temporary table
create table #temp (row int)

--create seed rows
while (select count(*) from #temp) < @max * @repeat * 2
begin
insert into #temp
select 0
from (values ('a'),('a'),('a'),('a'),('a')) as a(col1)
cross join (values ('a'),('a'),('a'),('a'),('a')) as b(col2)
end

-- set row number can also use identity
set @rid = -1

update #temp
set     @rid = row = @rid + 1

-- if the (row/max) is odd, reverse the order
select  case when (row/@max) % 2 = 1 then @max - (row%@max) else (row%@max) + 1 end
from    #temp
where   row < @max * @repeat * 2
order by row
``````

A way to do it in SQL Server using a recursive cte.

1) Generate the required number of members in the series (for n=3 and m=4 it would be 24 which is 2*n*m)

2) After that using logic in a `case` expression, you can generate the required series.

`Sample Demo`

``````declare @n int=3;--change the value as needed
declare @m int=4;--change the value as needed

with numbers(num) as (select 1
union all
select num+1 from numbers where num<2*@n*@m)
select case when (num/@n)%2=0 and num%@n<>0 then num%@n
when (num/@n)%2=0 and num%@n=0 then 1
when (num/@n)%2=1 and num%@n<>0 then @n+1-(num%@n)
when (num/@n)%2=1 and num%@n=0 then @n
end as num
from numbers
OPTION(MAXRECURSION 0)
``````

As suggested by @AndriyM .. the `case` expression can be simplified to

``````with numbers(num) as (select 0
union all
select num+1 from numbers where num<2*@n*@m-1)
select case when (num/@n)%2=0 then num%@n + 1
when (num/@n)%2=1 then @n - num%@n
end as num
from numbers
OPTION(MAXRECURSION 0)
``````

`Demo`

A basic function using iterators.

T-SQL

``````create function generate_up_down_series(@max int, @rep int)
returns @serie table
(
num int
)
as
begin

DECLARE @X INT, @Y INT;
SET @Y = 0;

WHILE @Y < @REP
BEGIN

SET @X = 1;
WHILE (@X <= @MAX)
BEGIN
INSERT @SERIE
SELECT @X;
SET @X = @X + 1;
END

SET @X = @MAX;
WHILE (@X > 0)
BEGIN
INSERT @SERIE
SELECT @X;
SET @X = @X -1;
END

SET @Y = @Y + 1;
END

RETURN;
end
GO
``````

Postgres

``````create or replace function generate_up_down_series(maxNum int, rep int)
returns table (serie int) as
\$body\$
declare
x int;
y int;
z int;
BEGIN

x := 0;
while x < rep loop

y := 1;
while y <= maxNum loop
serie := y;
return next;
y := y + 1;
end loop;

z := maxNum;
while z > 0 loop
serie := z;
return next;
z := z - 1;
end loop;

x := x + 1;
end loop;

END;
\$body\$ LANGUAGE plpgsql;
``````

Using only basic Math `+ - * /` and Modulo:

``````SELECT x
, s = x % (2*@n) +
(1-2*(x % @n)) * ( ((x-1) / @n) % 2)
FROM (SELECT TOP(2*@n*@m) x FROM numbers) v(x)
ORDER BY x;
``````

This doesn't require a specific RDBMS.

With `numbers` being a number table:

``````...;
WITH numbers(x) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n0(x)
CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n1(x)
CROSS JOIN (VALUES(0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) AS n2(x)
)
...
``````

This generate a number table (1-1000) without using a recursive CTE. See Sample. 2nm must be smaller than the number of row in numbers.

Output with n=3 and m=4:

``````x   s
1   1
2   2
3   3
4   3
5   2
6   1
7   1
8   2
... ...
``````

This version requires a smaller number table (v >= n and v >= m):

``````WITH numbers(v) AS(
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM (VALUES(1), (2), (3), (4), (5), (6), ...) AS n(x)
)
SELECT ord = @n*(v+2*m) + n
, n*(1-v) + ABS(-@n-1+n)*v
FROM (SELECT TOP(@n) v FROM numbers ORDER BY v ASC) n(n)
CROSS JOIN (VALUES(0), (1)) AS s(v)
CROSS JOIN (SELECT TOP(@m) v-1 FROM numbers ORDER BY v ASC) m(m)
ORDER BY ord;
``````

See Sample.

``````declare @n int = 5;
declare @m int = 3;
declare @t table (i int, pk int identity);
WITH  cte1 (i)
AS ( SELECT 1
UNION ALL
SELECT i+1 FROM cte1
WHERE  i < 100  -- or as many you might need!
)
insert into @t(i) select i from cte1 where i <= @m  order by i
insert into @t(i) select i from @t order by i desc
select t.i --, t.pk, r.pk
from @t as t
cross join (select pk from @t where pk <= @n) as r
order by r.pk, t.pk
``````

## Introduction:

This question intrigued me (+1) and I pondered

• a) how to answer it and

• b) how could the answer be generalised?

All of the code below can be found on the fiddle here.

Interestingly, nobody who's answered so far (10 answers - and some very good quality SQL/programming to boot!) has made use of `ARRAY`s (tutorial), which I believe are very helpful in this case.

In a general sense, my approach has been to generate the first series as an ARRAY (`{1, 2,.. n, n,..2, 1}`) and then generate `m` of these for the complete task.

I took three approaches:

• the first is PostgreSQL specific, making use of `GENERATE_SERIES()` (tutorial) and `ARRAY`s. There's also a function call that can be replaced with a `RECURSIVE CTE` (`"RCTE"` - see tutorial).

• the second (also PostgreSQL specific) combines an `RCTE` with a call to `GENERATE_SERIES()` and `ARRAY`s. The `GENERATE_SERIES()` can be replaced with an `RCTE` - see solution 3.

• the third solution is `"Pure SQL"` and should work on any RDBMS that supports `RCTE`s (SQL Server for example).

## Preparation step:

I used a table called `param` to store the values (`3` & `5`) as per the question - these can obviously be changed. Also, for the benchmarking steps (see below), I used this param table for all queries tested so that there would be an even playing field.

``````--
-- Setup of parameters...
--

CREATE TABLE param(n INT, m INT);
INSERT INTO param (VALUES(3, 5));
SELECT * FROM param;
``````

The first method is as follows:

# Method 1 - GENERATE_SERIES (fiddle):

## Step 1:

``````--
-- Step 1
--
SELECT
GENERATE_SERIES(1, (SELECT n FROM param)) AS the_first_series
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1);
``````

Result:

``````the_first_series
1
2
3
3
2
1
``````

## Step 2:

``````--
--  Two possible Step 2s using a PL/pgSQL function or an RCTE
--
CREATE OR REPLACE FUNCTION fill_array_with_seq(the_array anyarray, seq_num INT)
RETURNS ANYARRAY LANGUAGE PLpgSQL AS \$\$
DECLARE
BEGIN
FOR i IN 1..seq_num LOOP
the_array[i] := i;
i = i + 1;
END LOOP;
RETURN the_array;
end \$\$;

SELECT fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param));

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param);
``````

Results (same):

``````fill_array_with_seq
{1,2,3,4,5,6}
f_arr
{1,2,3,4,5,6}
``````

## Step 3:

``````--
-- Step 3
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT
ROW_NUMBER() OVER () AS rn,
(
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
),

-- could use
--
-- fill_array_with_seq(ARRAY[]::INT[], (SELECT n * 2 FROM param))
--

ARRAY
(
SELECT
GENERATE_SERIES(1, (SELECT n FROM param))
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1)
) AS arr
FROM
GENERATE_SERIES(1, (SELECT m FROM param)) AS x;
``````

Result:

``````rn         f_arr             arr
1  {1,2,3,4,5,6}   {1,2,3,3,2,1}
2  {1,2,3,4,5,6}   {1,2,3,3,2,1}
3  {1,2,3,4,5,6}   {1,2,3,3,2,1}
4  {1,2,3,4,5,6}   {1,2,3,3,2,1}
5  {1,2,3,4,5,6}   {1,2,3,3,2,1}
``````

Finally:

``````--
--  Steps 4 & 5 - separate subquery not shown
--

WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT the_series FROM
(
SELECT
ROW_NUMBER() OVER () AS rn,
UNNEST
(
(
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
)
) AS seq,
UNNEST
(
ARRAY
(
SELECT
GENERATE_SERIES(1, (SELECT n FROM param))
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1)
)
) AS arr
FROM
GENERATE_SERIES(1, (SELECT m FROM param)) AS x
ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;
``````

Result:

``````the_series
1
2
3
3
2
1
1
2
...
... snipped for brevity
...
``````

# Method 2 - Recursive CTE (fiddle):

Here, I managed to "kill two birds with one stone" by constructing both the desired sequence and its numbering scheme in the same RCTE as follows:

``````--
-- Step 1
--
WITH RECURSIVE cte_fill_array AS -- (cnt, val_arr, cnt_arr) AS
(
SELECT 1 AS i, 1 AS cnt, ARRAY AS val_arr, ARRAY AS cnt_arr
UNION ALL
SELECT i + 1, cnt + 1,
ARRAY_APPEND
(
val_arr,
(
SELECT
CASE
WHEN cnt < (SELECT n FROM param) THEN (i + 1)
WHEN cnt = (SELECT n FROM param) THEN cnt
WHEN cnt > (SELECT n FROM param) THEN 6 - i
END
)
),
ARRAY_APPEND(cnt_arr, cnt + 1)
FROM cte_fill_array
WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT i, cnt, val_arr, cnt_arr FROM cte_fill_array;
``````

Result:

``````i   cnt val_arr cnt_arr
1   1   {1} {1}
2   2   {1,2}   {1,2}
3   3   {1,2,3} {1,2,3}
4   4   {1,2,3,3}   {1,2,3,4}
5   5   {1,2,3,3,2} {1,2,3,4,5}
6   6   {1,2,3,3,2,1}   {1,2,3,4,5,6}
``````

We only want the last record, so we select this by using the `CARDINALITY()` function - where that is equal to (`n * 2`) is the last record (step not shown individually).

Final step - see fiddle for more detail

``````--
--  Steps 2 - end
--
WITH RECURSIVE cte_fill_arr (n, f_arr) AS
(
SELECT 1 AS n, ARRAY::INT[] AS f_arr
UNION ALL
SELECT n + 1, array_append(f_arr, n + 1)
FROM cte_fill_arr
WHERE n < (SELECT n * 2 FROM param)
)
SELECT arr AS the_series FROM
(
SELECT
ROW_NUMBER() OVER () AS rn,
UNNEST
(
(
SELECT f_arr FROM cte_fill_arr
WHERE CARDINALITY(f_arr) = (SELECT n * 2 FROM param)
)
) AS seq,
UNNEST
(
ARRAY
(
SELECT
GENERATE_SERIES(1, (SELECT n FROM param))
UNION ALL
SELECT
GENERATE_SERIES((SELECT n FROM param), 1, -1)
)
) AS arr
FROM
GENERATE_SERIES(1, (SELECT m FROM param)) AS x
ORDER BY rn, seq
) AS fin_arr
ORDER BY rn, seq;
``````

Result (same as others):

``````the_series
1
2
3
3
...
... snipped for brevity
...
``````

A simpler, (and IMHO) more elegant solution exists - using the `GENERATE_SUBSCRIPTS()` function (tutorial) as follows:

``````WITH RECURSIVE cte (i) AS
(
SELECT 1 AS i, 1 AS cnt
UNION ALL
SELECT
CASE
WHEN cnt < (SELECT n FROM param) THEN (i + 1)
WHEN cnt = (SELECT n FROM param) THEN cnt
ELSE i - 1
END AS i,
cnt + 1
FROM cte
WHERE cnt < 2 * (SELECT n FROM param)
)
SELECT the_arr
FROM
(
SELECT
x,
UNNEST(ARRAY(SELECT i FROM cte))    AS the_arr,
GENERATE_SUBSCRIPTS(ARRAY(SELECT i FROM cte), 1) AS ss

FROM GENERATE_SERIES(1, (SELECT m FROM param)) AS t(x)
) AS s
ORDER BY x, ss;
``````

Result (same):

``````the_series
1
2
3
3
...
... snipped for brevity
...
``````

# Method 3 - Pure SQL (fiddle):

## Final step:

Since all of the code below has been seen above in one form or another, I'm just including the final step. No PostgreSQL specific functionality has been used and it also works under SQL Server 2019 (Linux).

``````WITH RECURSIVE cte (i, cnt) AS
(
SELECT 1 AS i, 1 AS cnt
UNION ALL
SELECT
CASE
WHEN cnt < (SELECT n FROM param) THEN (i + 1)
WHEN cnt = (SELECT n FROM param) THEN cnt
ELSE i - 1
END AS i,
cnt + 1
FROM cte
WHERE cnt < 2 * (SELECT n FROM param)
),
seq (n) AS
(
SELECT 1
UNION ALL
SELECT n + 1 FROM seq WHERE n < (SELECT m FROM param)
)
SELECT i
FROM
(
SELECT
s.n, c.cnt, c.i
FROM cte c
CROSS JOIN
(SELECT * FROM seq) AS s
) AS tab
ORDER BY n, cnt;
``````

Result (same):

``````i
1
2
3
3
``````

# Benchmarking:

I benchmarked all of the PostgreSQL solutions.

I have done my utmost to be fair in these benchmarks - I used a param table `(3, 5)` in my SQL, but for generating a `number` table, I used a `RECURSIVE CTE` - I have tried to level the playing field by slightly modifying some of the SQL so that I was comparning like with like. If you have any objections, please let me know and I'll rerun any test with which you are unhappy.

I used `db<>fiddle` for the tests and the usual caveats apply - I don't know what else is running on that server at any given moment in time - I took an average of several runs for each solution.

• Vérace GENERATE_SERIES SQL method (fiddle): 0.195 ms

• Vérace RECURSIVE CTE SQL method (fiddle): 0.200 ms

• Vérace GENERATE_SUBSCRIPTS() SQL method (fiddle): 0.110 ms

• Vérace "Pure SQL" method (

• a_horse_with_no_name's SQL method (fiddle) 0.150 ms

• OP's SQL method (fiddle): 12.50 ms

• OP's PL/pgSQL function method (fiddle): 0.60 ms

• Erwin Brandtstetter's SQL method (fiddle): 15.5 ms

• Erwin Brandtstetter's function method (fiddle): 0.150 ms

• Abelisto's SQL method (fiddle) 0.145 ms

• Evan Carroll's SQL method (fiddle) 0.125 ms

• McNet's PL/pgSQL method (fiddle) 0.075 ms

The source code of PostgreSQL is a bit above my pay grade, but I believe that operations with `GENERATE_SERIES` and `ARRAY`s preserve order - the `WITH ORDINALITY` implies this (again, I think) - the correct answers come up even without paying attention to ordering (although this not a guarantee)! @ErwinBrandstetter says that:

• I added ORDER BY to guarantee the requested order. With current versions or Postgres it also works without ORDER BY for the simple query - but not necessarily in more complex queries! That's an implementation detail (and it's not going to change) but not mandated by the SQL standard.

My understanding is that `ARRAY`s are fast in PostgreSQL because much of the backend code is implemented through them - but as I said, I'm not really a `C` expert.

It appears that "pure" PL/pgSQL (OP, McNets, then Erwin Brandstetter) is the fastest in this context, followed by SQL using `ARRAY`s and `GENERATE_SUBSCRIPS()` (me). Next is Evan Carroll's solution using `GENERATE_SERIES()` with `ORDINALITY`. Next is `a_horse_with_no_name`'s SQL also using `GENERATE_SERIES()` and Abelisto's SQL using an `RCTE`. The OP's and Erwin Brandstetter's SQL are in last place - strangely since the EB's PL/pgSQL functions calls the same SQL. The OP's SQL has no relation to the PL/pgSQL.

I found these posts on ARRAYs from Erwin Brandstetter (1) & a_horse_with_no_name (2) very helpful! Other's that I found helpful were as follows (1, 2).