PostgreSQL: Select a single-row x amount of times

Question

A single row in a table has a column with an integer value >= 1 and must be selected however many times the column says. So if the column had '2', I'd like the select query to return the single-row 2 times.

How can this be accomplished?

Answer 1

You can do it with a recursive query, check out the examples in the postgresql docs.

something like

WITH RECURSIVE t(cnt, id, field2, field3) AS (
        SELECT 1, id, field2, field3
        FROM foo
      UNION ALL
        SELECT t.cnt+1, t.id, t.field2, t.field3
        FROM t, foo f
        WHERE t.id = f.id and t.cnt < f.repeat_cnt
)
SELECT id, field2, field3 FROM t;

Answer 2

You could make a table that is just full of numbers, like this:

CREATE TABLE numbers
(
  num INT NOT NULL
, CONSTRAINT numbers_pk PRIMARY KEY (num)
);

and populate it with as many numbers as you need, starting from one:

INSERT INTO numbers VALUES(1);
INSERT INTO numbers VALUES(2);
INSERT INTO numbers VALUES(3);
...

Then, if you had the table "mydata" that han to repeat based on the column "repeat_count" you would query it like so:

SELECT mydata.*
FROM mydata
JOIN numbers
ON numbers.num <= mydata.repeat_count
WHERE ...

If course you need to know the maximum repeat count up front, and have your numbers table go that high.

No idea why you would want to do this thought. Care to share?

Answer 3

Don't know why you would want to do such a thing, but...

CREATE TABLE testy (a int,b text);
INSERT INTO testy VALUES (3,'test');
SELECT testy.*,generate_series(1,a) from testy;  --returns 3 rows