Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 12.8 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


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



n
m


3
5


CREATE TABLE numbers (n INT);
WITH RECURSIVE dummy (n) AS
(
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM dummy
  WHERE n < (SELECT m FROM param)
)
INSERT INTO numbers
SELECT n from dummy;
5 rows affected

SELECT * FROM numbers;


n


1

2

3

4

5


WITH RECURSIVE cte (i, seq) AS -- PostgreSQL requires the RECURSIVE keyword, 
(                    -- SQL Server does not and throws an error!
  SELECT 1 AS i, 1 AS seq
  UNION ALL
  SELECT 
    CASE
      WHEN seq < (SELECT n FROM param) THEN (i + 1)
      WHEN seq = (SELECT n FROM param) THEN seq
      ELSE i - 1
    END AS i,
    seq + 1
  FROM cte
  WHERE seq < 2 * (SELECT n FROM param)
)
SELECT * FROM cte;


i
seq


1
1

2
2

3
3

3
4

2
5

1
6


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)
)
SELECT 
  n.n, c.i, c.cnt
FROM 
  cte c
CROSS JOIN numbers n
ORDER BY n.n, c.cnt;


n
i
cnt


1
1
1

1
2
2

1
3
3

1
3
4

1
2
5

1
1
6

2
1
1

2
2
2

2
3
3

2
3
4

2
2
5

2
1
6

3
1
1

3
2
2

3
3
3

3
3
4

3
2
5

3
1
6

4
1
1

4
2
2

4
3
3

4
3
4

4
2
5

4
1
6

5
1
1

5
2
2

5
3
3

5
3
4

5
2
5

5
1
6


EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
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)
)
SELECT 
  n.n, c.i, c.cnt
FROM 
  cte c
CROSS JOIN numbers n
ORDER BY n.n, c.cnt;


QUERY PLAN


Sort  (cost=8443.03..8640.66 rows=79050 width=12) (actual time=0.081..0.084 rows=30 loops=1)

  Output: n.n, c.i, c.cnt

  Sort Key: n.n, c.cnt

  Sort Method: quicksort  Memory: 26kB

  Buffers: shared hit=4

  CTE cte

    ->  Recursive Union  (cost=0.00..981.50 rows=31 width=8) (actual time=0.002..0.036 rows=6 loops=1)

          Buffers: shared hit=3

          ->  Result  (cost=0.00..0.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1)

                Output: 1, 1

          ->  WorkTable Scan on cte  (cost=97.80..98.09 rows=3 width=8) (actual time=0.005..0.005 rows=1 loops=6)

                Output: CASE WHEN (cte.cnt < $1) THEN (cte.i + 1) WHEN (cte.cnt = $2) THEN cte.cnt ELSE (cte.i - 1) END, (cte.cnt + 1)

                Filter: (cte.cnt < (2 * $3))

                Rows Removed by Filter: 0

                Buffers: shared hit=3

                InitPlan 1 (returns $1)

                  ->  Seq Scan on public.param  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.003..0.003 rows=1 loops=1)

                        Output: param.n

                        Buffers: shared hit=1

                InitPlan 2 (returns $2)

                  ->  Seq Scan on public.param param_1  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.003..0.004 rows=1 loops=1)

                        Output: param_1.n

                        Buffers: shared hit=1

                InitPlan 3 (returns $3)

                  ->  Seq Scan on public.param param_2  (cost=0.00..32.60 rows=2260 width=4) (actual time=0.004..0.004 rows=1 loops=1)

                        Output: param_2.n

                        Buffers: shared hit=1

  ->  Nested Loop  (cost=0.00..1030.62 rows=79050 width=12) (actual time=0.020..0.068 rows=30 loops=1)

        Output: n.n, c.i, c.cnt

        Buffers: shared hit=4

        ->  CTE Scan on cte c  (cost=0.00..0.62 rows=31 width=8) (actual time=0.003..0.042 rows=6 loops=1)

              Output: c.i, c.cnt

              Buffers: shared hit=3

        ->  Materialize  (cost=0.00..48.25 rows=2550 width=4) (actual time=0.003..0.003 rows=5 loops=6)

              Output: n.n

              Buffers: shared hit=1

              ->  Seq Scan on public.numbers n  (cost=0.00..35.50 rows=2550 width=4) (actual time=0.006..0.006 rows=5 loops=1)

                    Output: n.n

                    Buffers: shared hit=1

Planning Time: 0.153 ms

Execution Time: 0.134 ms
n	i	cnt
1	1	1
1	2	2
1	3	3
1	3	4
1	2	5
1	1	6
2	1	1
2	2	2
2	3	3
2	3	4
2	2	5
2	1	6
3	1	1
3	2	2
3	3	3
3	3	4
3	2	5
3	1	6
4	1	1
4	2	2
4	3	3
4	3	4
4	2	5
4	1	6
5	1	1
5	2	2
5	3	3
5	3	4
5	2	5
5	1	6
n	i	cnt
1	1	1
1	2	2
1	3	3
1	3	4
1	2	5
1	1	6
2	1	1
2	2	2
2	3	3
2	3	4
2	2	5
2	1	6
3	1	1
3	2	2
3	3	3
3	3	4
3	2	5
3	1	6
4	1	1
4	2	2
4	3	3
4	3	4
4	2	5
4	1	6
5	1	1
5	2	2
5	3	3
5	3	4
5	2	5
5	1	6