3

I've this table:

CREATE TABLE "mytable"
(  name text,  count integer );
INSERT INTO mytable VALUES ('john', 4),('mark',2),('albert',3);

and I would like "denormlize" the rows in this way:

SELECT name FROM mytable JOIN generate_series(1,4) tmp(a) ON (a<=count)

so I've a number of rows for each name equals to the count column: I've 4 rows with john, 2 with mark and 3 with albert. But i can't use the generate_series() function if I don't know the highest count (in this case 4). There is a way to do this without knowing the MAX(count) ?

Alberto
  • 2,881
  • 7
  • 35
  • 66

2 Answers2

4
select name, 
       generate_series(1,count)
from mytable;

Set returning functions can be used in the select list and will do a cross join with the row retrieved from the base table.

I think this is an undocumented behaviour that might go away in the future, but I'm not sure about that (I recall some discussion regarding this on the mailing list)

SQLFiddle example

  • extremely elegant solution! I like it! – Alberto Sep 25 '12 at 10:41
  • I was surprised by that behavior when I first saw it in another question/answer some days ago. I think it is worth its own question. – Clodoaldo Neto Sep 25 '12 at 11:33
  • There are indeed [two examples of generate_subscripts](http://www.postgresql.org/docs/current/static/functions-srf.html) in the select list, one of them doing a cross join. So I guess it is here to stay. – Clodoaldo Neto Sep 25 '12 at 11:47
0
DROP TABLE ztable ;
CREATE TABLE ztable (zname varchar, zvalue INTEGER NOT NULL);

INSERT INTO ztable(zname, zvalue) VALUES( 'one', 1), ( 'two', 2 ), ( 'three', 3) , ( 'four', 4 );

WITH expand AS (
        WITH RECURSIVE zzz AS (
        SELECT 1::integer AS rnk , t0.zname
        FROM ztable t0
        UNION
        SELECT 1+rr.rnk , t1.zname
        FROM ztable t1
        JOIN zzz rr ON rr.rnk < t1.zvalue
        )
        SELECT zzz.zname
        FROM zzz
        )
SELECT x.*
FROM expand x
        ;
wildplasser
  • 38
  • 2