How to generate a user-defined array of numbers on a given interval a certain number of times

Question

I need to generate user-defined integers as a required unordered set of numbers for a given interval, e.g. (10) a certain number of times, e.g. 3 in one column of a table.

The number and repetition of digits can be different and can be set by the user at his discretion.

For example, the desired result from top to bottom should look like this:

0, 1, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 9, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2. 1, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 9, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2. 1, 0, 0, 1, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 9, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2. 1, 0.

Perhaps, using the function

SELECT n
 FROM generate_series(ARRAY[0, 1, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 9, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2. 1, 0], 3) n

or something similar?

Answer 1

You almost have it. You need to use unnest to split up the array, then cross join it with 3 rows from generate_series.

SELECT a.value
FROM unnest(
  ARRAY[0, 1, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 9, 10, 9, 8, 7, 6, 5, 4, 3, 2, 2, 3, 4, 5, 6, 7, 8, 8, 7, 6, 5, 4, 3, 2, 1, 0]
) WITH ORDINALITY a(value, ordinal)
CROSS JOIN GENERATE_SERIES(1, 3) n(value)
ORDER BY
  n.value,
  a.ordinal;

db<>fiddle